INDEX MATCH when duplicate search value in one cell

**doc.ramses** · 10-11-2012, 12:42 PM

Hi,

looking for a solution for the following problem I couldn't find an answer in the web.
When I use the INDEX/MATCH function, it doesn't work, when I have the value I am searching for multiple times in one cell.
Please see attached file.

Thanks a lot in advance for your help.

**benishiryo** · 10-11-2012, 09:12 PM

hi doc.ramses. welcome to the forum. could you explain what you want to achieve? if the Value to Search is within column B, return Column A? you can try:
=IF(ISNUMBER(SEARCH(C3,B3)),A3,"")

**Teethless mama** · 10-11-2012, 10:08 PM

=if(countif(b3,"*"&c3&"*"),a3,"")

**doc.ramses** · 10-12-2012, 02:55 AM

Hey,

thank you for your help. I missed to mention that the value I am searching for, is somewhere in column B. So the returned value in A (which should be the same row as searched value in B) is mostly not from the same row as the search reference in column C.
I hope I could make it clear.

Looking forward to your suggestions!

**prasadavasare** · 10-12-2012, 03:04 AM

Hi,

Please try below formula in D2=LOOKUP(3^17,SEARCH(C3,B3),A3)

It works for me....not sure this is what you want.

Thanks & Warm Regards,
Prasad Avasare

**doc.ramses** · 10-12-2012, 07:48 AM

Thank you Prasad, this also works for me, although I don't really understand how it works.
Can you tell me what the 3^17 is doing ?

**prasadavasare** · 10-12-2012, 06:01 PM

3^17=129140163, The individual cell can have 32767 characters (length), obviously can not be greater than this number. You can take any number but should be greater than the length of sentence. Here we are looking up a word in C column within B which may have different length.

Thanks and Warm Regards,
Prasad