lookup to see if any of the values (from a list) exist in a cell.

[artins]

How do I lookup to see if any of the values in a list exist in a cell?

Currently I use this formula but I am sure there is an easier way to do it

The list on a worksheet named "List".

=IF(OR(ISNUMBER(SEARCH(List!$A$1,A2)),ISNUMBER(SEARCH(List!$A$2,A2)),ISNUMBER(SEARCH(List!$A$3,A2)),ISNUMBER(SEARCH(List!$A$4,A2)),ISNUMBER(SEARCH(List!$A$5,A2)),ISNUMBER(SEARCH(List!$A$6,A2))),1,"")

I need to check the cell in column A of a worksheet to see if it contains any of the values in a list on worksheet "List", if so return "1".

Your help is greatly appreciated.

 

[MrVillareal]

Hi friend

Try this simple illustration.
Hope this add help

Excel Workbook
	A	B	C	D
1	LookupValue	ReturnValue		LookupTable
2	a	1		a
3	b	1		b
4	c	1		c
5	1	1		1
6	2	1		2
7	3	0
Sheet7

Excel 2010

Cell Formulas
Range	Formula
B2	=IF(ISNA(MATCH(A2,$D$2:$D$6,0)>0),0,1)
B3	=IF(ISNA(MATCH(A3,$D$2:$D$6,0)>0),0,1)
B4	=IF(ISNA(MATCH(A4,$D$2:$D$6,0)>0),0,1)
B5	=IF(ISNA(MATCH(A5,$D$2:$D$6,0)>0),0,1)
B6	=IF(ISNA(MATCH(A6,$D$2:$D$6,0)>0),0,1)
B7	=IF(ISNA(MATCH(A7,$D$2:$D$6,0)>0),0,1)

Hope for feedback

 

[scottylad2]

not too sure what you want returned if you find the item in your list, but adapt this construct. Tested and works, needs entered with Ctrl + Shift + Enter, just seen the end of your formula, replace True with 1 and false with ""

 

[scottylad2]

=IF(OR({"pete";"dave";"sam";"mary";"sean"}=B2),TRUE,FALSE)

and the formula this time

 

[scottylad2]

This
=IF(OR(ISNUMBER(SEARCH(List!$A$1,A2)),ISNUMBER(SEARCH(List!$A$2,A2)),ISNUMBER(SEARCH(List!$A$3,A2)),ISNUMBER(SEARCH(List !$A$4,A2)),ISNUMBER(SEARCH(List!$A$5,A2)),ISNUMBER(SEARCH(List!$A$6,A2))),1,"")

should shorten quite easily to this

=IF(OR(List!$A$1:$A$6=A2),1,"")

Not tried this on any data but it should work just the same

Ctrl + Shift + Enter not just enter

 

[mikerickson]

How about
=SIGN(SUMPRODUCT(--ISNUMBER(MATCH("*"&List!$A$1:$A$10&"*", A1, 0))))