Find a cell with String value - complex

**hqureshi** · 06-01-2012, 01:03 AM

Col B has all the sub-strings that needs to be searched.
Col A has the full length strings that needs to be returned.

VLOOKUP is not the answer; neither is INDEX/MATCH combo - in my opinion.

Take a substring and match it against data in Col A and then return the Col A cell value. So sub-string in B6 exists in A3. So formula should return same value as A3 in C6. If sub-string is not found returning a "n/a" would be nice. (smile!)

Please help..

Thanks.
H-

**zbor** · 06-01-2012, 02:48 AM

How about this (2 solutions, check on other examples does they have faults... I think 2nd is better).

(you can also return blank instead of NA().)

**zbor** · 06-01-2012, 02:54 AM

Yes, 2nd is better... Ignore first one...

If you enter WORLD in B20 both will work. But if you have blank cells (like enter WORLD in B30 and leave blank B20 to B29) then first one won't work.
It assume all words are in B column without blanks.