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