Index Match Lookup of Partial words in one cell.

[vstone]

I have a simple question.... I think

I need to search multiple words in a in one cell.

As an Example. Cells A1-A5
Apple Store Chatswood R1
BigW R3
McDonalds R1
KFC R3
Pie Face R2

The words I am looking for are R1, R2, R3, R4, R5. These are in Cells B1-B5

In Column C I would like to list the values

Thanks

 

[Canapone]

Hi, in B1:B5 the list of substrings (R1-R2-R3...) to be searched in A2 A3 A4 ....

In C1 to be copied down

=IFERROR(INDEX($B$1:$B$5,MATCH(1,INDEX(SEARCH("*"&$B$1:$B$5&"*",A1),),0)),"")

Regards

 

[uk747]

doesnt it need to be
=IFERROR(INDEX($B$1:$B$5,MATCH(1,INDEX(SEARCH("*"&$a$1:$a$5&"*",A1),),0)),"")

 

[uk747]

ignore above i didnt realise Column A contained the R1, R2 etc as well

Apple Store Chatswood R1</SPAN>

BigW R3</SPAN>

McDonalds R1</SPAN>

KFC R3</SPAN>

Pie Face R2</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

 

[vstone]

That was perfect. Thanks so much

Hi, in B1:B5 the list of substrings (R1-R2-R3...) to be searched in A2 A3 A4 ....

In C1 to be copied down

=IFERROR(INDEX($B$1:$B$5,MATCH(1,INDEX(SEARCH("*"&$B$1:$B$5&"*",A1),),0)),"")

Regards

 

[Canapone]

Thanks for providing feedback. Cheers

 

[Aladin Akyurek]

C1, just enter and copy down:

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$B$1:$B$5&" "," "&$A1&" "),$B$1:$B$5)

which should be faster and won't confound R1 and say R11 or JR1.