Search/find multiple words in a cell and return a lookup value

[aequitas1903]

Hi everyone,

I have a data column and each cell value in this column has an explanation of product and some other datas . But cell value does not only contains product codes. and it is not possible to "text to column" or split the data.

I have a list of unique words/sentences to lookup and return the lookup value of this word/sentence.

Lets say C3 cell value is: 325 tons of hard steel
C4 cell value is: Wood chairs made for John
C5 cell value is:

And the words/sentences(40 different values) I am looking for is in column E and the values I need is at column F
E1: chair F1: MS2
E2: Steel F2: VCZ
E3: ..... F3: ....
.......................
E40: F40

I need a formula in column A to search for chair first and if it finds chair in cell C3 then returns MS2. If it doesnt find chair then it searches for steel and returns VCZ. It goes likes this for 40 different values. and if no listed value is found it returns blank.

I did try to use "If" formula and find/search formula inside of this "If" formula. I wrote 40 if formulas inside of each other. But somehow excel doesnt work after first search formula.

I hope you understood my problem. Do you think there is a way to find a value in a cell from a list of values in a table which contains 2 columns (first columnd: search value, second column: result value) ?

I am open to any ideas. Your help is always appreciated.

Thanks everyone for at least reading this.
Cheers

 

[anglais428]

Something like this works
=IF(ISNUMBER(SEARCH("Chairs",C3)),VLOOKUP("chairs",$E$1:$F$2,2,0),IF(ISNUMBER(SEARCH("Steel",C3)),VLOOKUP("steel",$E$1:$F$2,2,0),0))
You can adapt it as you see fit

 

[aequitas1903]

Something like this works
=IF(ISNUMBER(SEARCH("Chairs",C3)),VLOOKUP("chairs",$E$1:$F$2,2,0),IF(ISNUMBER(SEARCH("Steel",C3)),VLOOKUP("steel",$E$1:$F$2,2,0),0))
You can adapt it as you see fit

Thank you anglais428 it works.

I tried the formula like
=IF(search("Chairs",C3)>0;vlookup("chairs",.......
but it didnt work.

Anyways it works fine now. ty very much.

(I have to write 40 if formula now :'( )