Find text within cell from a list of names

[KongoBongo]

Hello.
I have one column with text inputs (list A) and i need to check if these inputs contains
names from a list in another list B (not exact match). If so i need the formula to produce which of the names that were a "hit"

Example list A:

abc hero daf
fre gold
yellow bird
blue giraff

Check list B:
hero
gold
bird

if anything hits in list A (from B) i would like the name to be produced in adjacent column (example:hero)

any one that could help me?
thanks in advance!

 

[T. Valko]

Hello.
I have one column with text inputs (list A) and i need to check if these inputs contains
names from a list in another list B (not exact match). If so i need the formula to produce which of the names that were a "hit"

Example list A:

abc hero daf
fre gold
yellow bird
blue giraff

Check list B:
hero
gold
bird

if anything hits in list A (from B) i would like the name to be produced in adjacent column (example:hero)

any one that could help me?
thanks in advance!

Try this...

List B in the range G2:G4.

List A starts in cell A2.

Enter this formula in B2 and copy down as needed:

=LOOKUP(1E100,SEARCH(G$2:G$4,A2),G$2:G$4)

If there is no match with list B then you'll get an error. One way to prevent that is:

=LOOKUP("zzzzz",CHOOSE({1,2},"",LOOKUP(1E100,SEARCH(G$2:G$4,A2),G$2:G$4)))

Also note, if the string contains more than one match from list B then the formula will match the word that is furthest down in list B.

 

[KongoBongo]

Thanks for the swift answer, that seems to cover my needs

 

[T. Valko]

Thanks for the swift answer, that seems to cover my needs

You're welcome. Thanks for the feedback!

 

[nehshere]

Hi,

This formula was very helpful for me too. Thanks!

I have a follow-up question, although after 7 years! This formula returns the name that matches and as you said, if the string contains more than one match from list B then the formula will match the word that is furthest down in list B. This is happening a lot in my output. But the problem here is that in the string in cell A2 it is looking at the words also in part. Is there a way to say that it should match the whole worlds in the string of text?

For example, in my data is a list of city names. I am looking for these names in the strings. when my string contains "warwick restaurants", it returns the value "wick" instead of "warwick" from the list of cities. Although 'wick' is in the list and in the string, i want it to return 'warwick' as that is the full word in the string.

Hope you can help!

Thanks,
Neha

 

[531phil]

Hi, I too was having this problem recently and found the following technique to improve the accuracy of the output:

- Remove all punctuation from the column containing the cells you are searching with, eg using Find and Replace
- Add spaces around your list of towns, eg using =CONCATENATE(" ",A2," ")
- Now use the LOOKUP formula as below with your new list, and it will now only match Warwick in the text with Warwick in your list, thanks to the spaces.

It's not perfect but an improvement. Another thing I found to look out for is a lot of UK towns have names of common words, eg Street, Stone, etc so you may need to edit your original list to remove these or do some manual checking after applying the formula

Hi,

This formula was very helpful for me too. Thanks!

I have a follow-up question, although after 7 years! This formula returns the name that matches and as you said, if the string contains more than one match from list B then the formula will match the word that is furthest down in list B. This is happening a lot in my output. But the problem here is that in the string in cell A2 it is looking at the words also in part. Is there a way to say that it should match the whole worlds in the string of text?

For example, in my data is a list of city names. I am looking for these names in the strings. when my string contains "warwick restaurants", it returns the value "wick" instead of "warwick" from the list of cities. Although 'wick' is in the list and in the string, i want it to return 'warwick' as that is the full word in the string.

Hope you can help!

Thanks,
Neha

 

[warrenmcaleny]

Brilliant solution although in the lookup what does the 1E100 drive? Can some please clarify?