Find partial text string within another text string return original text into cell.

Hi.

I need to match column C with column A and return the original text string in column C into column B. I highlighted what the matches should look like and put a couple of example results in column B.

Try this in B2 and filled down

=IFERROR(LOOKUP(2^15,SEARCH($C$2:$C$32,A2),$C$2:$C$32),"")

Note, there cannot be any blanks in C2:C32

Originally Posted by Jonmo1

Try this in B2 and filled down

=IFERROR(LOOKUP(2^15,SEARCH($C$2:$C$32,A2),$C$2:$C$32),"")

Note, there cannot be any blanks in C2:C32

This worked but when i upgraded the C column to include more variables some of the results came back as just the word Golf which i highlighted in green in this sheet.

The string in say A4 contains +golf
so therefore it also contains golf
Right?

The function is returning the last item from C2:C44 that it found within the string in A4 (golf)

You need to arrange your values in C2:C44 in order of priority when dealing with more than 1 matching value.
If you would prefer to see +golf instead of just golf, then put the +golf AFTER golf.


Hope that helps.

Ok, is there a way to make it match the exact string as a whole? So +golf is only +golf and not both +golf and golf? or another example will be A6 +golf +club would be the result im looking for as it matches [+golf +club] as a whole in column C. With over 100,000 rows in my main sheet it might not always be the last result.

Try

=IFERROR(LOOKUP(2^15,SEARCH(" "&$C$2:$C$44&" "," "&A2&" "),$C$2:$C$44),"")

OMG thank you sooooo sooooooo much ive been killing myself for 4 days trying to figure this out. Ill test it on the large sheet fingers crossed.

Yep seems to be working, i have no clue how but thank you none the less.

Great, glad to help. Thanks for the feedback.