Checking if values appear in a master list

[JTannas]

Are there any functions that check an array for values drawn from another array, then output a true/false array with the results?
eg.

Check For_____Master List_____ Output
Jack _________ Bill ____________ FALSE
Jill ___________ Marty __________ FALSE
______________ Jill _____________ TRUE


It would be nice if it could be done with built-in functions, but user defined is just as good. The length of the "check for" and "master list" arrays are arbitrary and usually different, but restricted to single columns.

I am using Excel 2003 on Windows Vista, don't know VBA but have minimal knowledge in C++.

Any help with this is greatly appreciated, and thanks in advance!
-Joel T.

 

[boim]

Put this on column C:
B2=cell in master list
A2:A3=range of check for

=AND(TRUE,IFERROR(LOOKUP(2^15,SEARCH(B2,$A$2:$A$3)),0))

Credit:
Above code is slight mod from the genius' answer to Mr Excel's June 2008 Challenge. Forgot his name though.

Added:
Oh, it's barry houdini

 

[boim]

Sorry, there's no IFERROR in Excel 2003. So a slight mod with regular IF should work.

 

[JTannas]

Thanks for the help, but it doesn't quite fit my needs (Sorry if I phrased the question unclearly).

I need to check if the entries in the "master list" match any entries shown in the "check for" list. Furthermore, the arguments and outputs need to be arrays .
The output of the formula should therefore be the same length as the master list.

Thanks anyways though!

 

[Akashwani]

Hi Joel,

Welcome to the best Excel site around.

<TABLE style="WIDTH: 173pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=230><COLGROUP><COL style="WIDTH: 15pt; mso-width-source: userset; mso-width-alt: 731" width=20><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2706" width=74><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1865" width=51><COL style="WIDTH: 15pt; mso-width-source: userset; mso-width-alt: 731" width=20><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; WIDTH: 15pt; HEIGHT: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl148 height=20 width=20></TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl142 width=65>A</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 56pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl142 width=74>Bill</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 38pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: #c0c0c0" class=xl142 width=51>C</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 15pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl143 width=20></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145>Check for</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145>Master List</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145>Output</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145>Jack</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145>Bill</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145>FALSE</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144></TD></TR>


<TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145>Jill</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145>Marty</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145>FALSE</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl149 height=20 align=right>4</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145>Jill</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl145>TRUE</TD><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl144></TD></TR><TR style="HEIGHT: 15.75pt" height=21><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl150 height=21></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl146></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl146></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl146></TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; BORDER-TOP: #c0c0c0; BORDER-RIGHT: windowtext 1pt solid" class=xl147></TD></TR>
</TBODY></TABLE>

In C2 put this and copy down...

=IF(COUNTIF($A$2:$A$3,B2)>0,"TRUE","FALSE")

OR THIS ONE....

=AND(COUNTIF($A$2:$A$3,B2))

Ak

 

[Aladin Akyurek]

Are there any functions that check an array for values drawn from another array, then output a true/false array with the results?
eg.

Check For_____Master List_____ Output
Jack _________ Bill ____________ FALSE
Jill ___________ Marty __________ FALSE
______________ Jill _____________ TRUE


It would be nice if it could be done with built-in functions, but user defined is just as good. The length of the "check for" and "master list" arrays are arbitrary and usually different, but restricted to single columns.

I am using Excel 2003 on Windows Vista, don't know VBA but have minimal knowledge in C++.

Any help with this is greatly appreciated, and thanks in advance!
-Joel T.

Let A2:A3 house the Check For values, E2:E4 the Master List, and G2:G4 the Output...

Try...

In G2 enter and copy down:

=ISNUMBER(MATCH(E2,$A$2:$A$3,0))

 

[JTannas]

Let A2:A3 house the Check For values, E2:E4 the Master List, and G2:G4 the Output...

Try...

In G2 enter and copy down:

=ISNUMBER(MATCH(E2,$A$2:$A$3,0))

As detailed in the original post, the arguments and outputs need to be arrays.
The reason copy down isn't possible is that this is an intermediate calculation in a MUCH larger formula and set of tables.

Thanks for trying though! Greatly appreciated.

 

[Aladin Akyurek]

As detailed in the original post, the arguments and outputs need to be arrays.
The reason copy down isn't possible is that this is an intermediate calculation in a MUCH larger formula and set of tables.

Thanks for trying though! Greatly appreciated.

Not knowing how you intend to use the output...

Example usage:

=SUMPRODUCT(ISNUMBER(MATCH(E2:E4,A2:A3,0))+0)

where

ISNUMBER(MATCH(E2:E4,A2:A3,0))

yields a vector/array of, given the sample:

{FALSE;FALSE;TRUE}

 

[JTannas]

It works perfectly! Thank you for the formula. I've been programming a workflow generating tool to model some scenarios, and this was the last piece of the puzzle.

Once again, thank for very much for your help.