How to vlook up based on the content of an excel cell

Hello guys,

Can you please let me know in case I want to use vlook up and look for a cell in which the "key word" I am looking for is part of the cell content instead of 100% identical.

For example in the attached excel, I want in tab 'Tech.shortlist' cell I6, to return the following value:

Search the B6 content (in this case Koyo Instruments & Industries) in tab 'sheet 4' and return the value from 'sheet 4' tab

The thing is that the content of B6 cell is not 100% identical to the cells searching in 'sheet 4' tab but it does contain some of the words (in this case Koyo)

Can you please assist?

Try

=IFERROR(VLOOKUP("*" &LEFT(B6,FIND(" ",B6)-1)&"*",Sheet4!$A:$B,2,FALSE),"")

Formula

Formula:

Please Login or Register  to view this content.

many thanks for your assistance, problem solved!