check to see if a list of cells contains a string

  • Thread starter Thread starter wing328hk
  • Start date Start date
W

wing328hk

Hi,

Is there a function to check if a list of cells (say form a1 to a10)
contains a string in a cell (say b2)??

I try lookup and the problem of lookup is that even though b2 is not
in a1 to 10, it returns the previous result instead of N/A.

Thanks,
wing
 
Use VLOOKUP() instead of LOOKUP().
To find an exact match, the fourth argument of VLOOKUP() should be FALSE

--
Kind regards,

Niek Otten
Microsoft MVP - Excel



| Hi,
|
| Is there a function to check if a list of cells (say form a1 to a10)
| contains a string in a cell (say b2)??
|
| I try lookup and the problem of lookup is that even though b2 is not
| in a1 to 10, it returns the previous result instead of N/A.
|
| Thanks,
| wing
|
 
If you only want to know if it does contain that string (or maybe where the
match occurs), use MATCH rather than a lookup.

eg =MATCH(B2,A1:A10,0) will return "5" if the item in B2 is in A5 (the fifth
position in the searched list).

So =NOT(ISERROR(MATCH(B2,A1:A10,0)) will produce "True" if it is in the list
and "false" if not.
 
Instead of:
=NOT(ISERROR(MATCH(B2,A1:A10,0))

you could also use:
=isnumber(MATCH(B2,A1:A10,0)
 
Back
Top