VLOOKUP the word contained in text

[Nick70]

Hi,

I need a VLOOKUP which will give me a result if the text in a column is found in another column even if this text is contained inside a word.

So if I have:

A...... B...... C
apple apple apple
pear pear pear
strawberry strawberry strawberry
cherries cherries cherries
bananas bananas bananas

and I want to check if items in column B are found in column A then I would do a VLOOKUP formula in column C equal to =VLOOKUP(B1,$A$1:$A$5,1,FALSE), =VLOOKUP(B2,$A$1:$A$5,1,FALSE) etc.

But if I have:

A.....B.......C
apple apple apple
whitepear pear #N/A
strawberry strawberry strawberry
redcherries cherries #N/A
bananas bananas bananas

I will get #N/As where the text is different (although contained within word so pear is contained in whitepear but I still get #N/A).

How can I amend my VLOOKUP formula in column C so that if text is contained in word then I get a result (ie. not #N/A) see example below?

A...... B......C
apple apple apple
whitepear pear pear
strawberry strawberry strawberry
redcherries cherries cherries
bananas bananas bananas

Thanks,
Nic

 

[Swapnil Shah]

Just try to use wild character i.e. use something like =VLOOKUP("*"&B1&"*",$A$1:$A$5,1,FALSE) This can help you.

 

[Nick70]

Just try to use wild character i.e. use something like =VLOOKUP("*"&B1&"*",$A$1:$A$5,1,FALSE) This can help you.

Great thanks!
N.