Using SEARCH Function with a List

Hi,

I was wondering if anyone knows how to use the ISNUMBER(SEARCH()) function using a list on a different sheet to search in.

To put it in context. I have a list of address in column T and am writing a formula to look at that address and search an abbreviated list of addresses in another sheet to identify it as being on the list so that I can review it.

For example: If in Column T I have "140 High St." and the list I am referencing has "140 High" as an identifier to review.. How do I make it search the full list and identify it as needing to be reviewed? I made the abbreviated list to eliminate any differences between "St." "ST" and "Street" etc; and just toned it back to the street number and beginning of the street name.

Thank you.

Try something like

=IFERROR(LOOKUP(2^15,SEARCH(list, T1),list),"")

T1 is cell to check
list cannot contain any blanks.

What is the 2^15 in reference to?

2 to the power of 15

2^15 = 32768

Right but I'm just not familiar with the use of it in a formula. I am not having any luck so far.. currently my formula looks like this:

=IF(AND((ISNUMBER(SEARCH('List Reference'!C2:C30,T2))),Z2="OK"),"Review","")

It returns my both values of "Review" and "", but the addresses do not seem to be consistent with what I am asking it to search.

Looks like your list is in 'List Reference'!C2:C30

Try
=IFERROR(LOOKUP(2^15,SEARCH('List Reference'!C2:C30, T2),'List Reference'!C2:C30),"")


If any of the values in C2:C30 exist in T2, the formula will return that value. Otherwise it returns ""
There cannot be any blanks in C2:C30

See if I can incorporate that to your AND test with Z2="OK"
Also proably need to lock down that C2:C30 list range.

=IF(AND(Z2="OK", ISNUMBER(LOOKUP(2^15,SEARCH('List Reference'!$C$2:$C$30, T2)))),"Review","")

That did it! Worked perfectly exactly how I had wanted. Thank you so much for your help!!

You're welcome.

The significance of 2^15, i.e. 32768
That number just happens to be 1 larger than the maximum number of characters allowed in a cell (32767)
So the lookup value 2^15 is guaranteed to be larger than any possible result of the Search funcion.

Definitely makes sense. Thank you for the learning point, I did not know that!