match any string in a cell?

**gechu** · 07-07-2010, 04:15 AM

Hi,

I´m looking for a way to do a lookup on every word found in a cell against a value-pair map.

LIST1:

big truck
my dog is brown

LIST2: - value-pair list which maps a word with a category
dog | animal
truck | vehicle

I want to create a formula which tries to match all the cells with all their substrings located in LIST1, with LIST2.

The final output would be:
big truck | vehicle
my dog is brown | animal

I have found many hits when searching for a solution. But the partial matching have always been the "other way around". That is, the partial matching have been applied to LIST2, where I want to partial matching to occur in LIST1.

This the formula I´ve used: =INDEX(LIST2;MATCH("*"&A_CELL&*";LEFT_COLUMN_OF_LIST2;0);2)

Any clues on how I can reach a solution?

Thanks!

/Erik

**DonkeyOte** · 07-07-2010, 04:19 AM

You could perhaps using something along the lines of:

B2:
=INDEX($D$1:$D$2;MATCH(TRUE;INDEX(ISNUMBER(SEARCH($C$1:$C$2;$A2));0);0))
copied down

where

A2, A3 etc holds strings (LIST1)
C1:D2 is your pair listing (LIST2)

Modify ranges to suit of course though note the above will return only the first keyword (not multiple)... eg:

"My truck is a dog to drive"

would return "animal" given this is listed first in List2 (ie has preference)