Search a cell for a list of values contained in another column and return that value if found

[khan123]

So I want search a cell for a list of values and return that value if value is found.

E.g
A
----
I love mangoes
I like mangoes
I hate bananas
I love oranges

B
-----
mangoes
oranges
bananas

I want to search the value in 'A' column where I looked for column 'B' values for each cell. If that value is found, I populate the next column with that value. In the given example, the desired result would be

I love mangoes -> mangoes
I like mangoes -> mangoes
I hate bananas -> bananas
I love oranges -> oranges

 

[jtakw]

Hi,

Like this:

Book3.xlsx
	A	B
1	I love mangoes	mangoes
2	I like mangoes	mangoes
3	I hate bananas	bananas
4	I love oranges	oranges
5
6
7
8	mangoes
9	oranges
10	bananas
Sheet974

Cell Formulas
Range	Formula
B1:B4	B1	=LOOKUP(2,1/SEARCH(A$8:A$10,A1),A$8:A$10)

 

[khan123]

Perfect, thank you! I am still exploring this syntax, how this is working.

 

[jtakw]

You're welcome, thanks for the feedback.

If in case you may have text strings that has no match in the list (like A5), just wrap my formula with IFERROR to show Blank rather than an error:

Book3.xlsx
	A	B
1	I love mangoes	mangoes
2	I like mangoes	mangoes
3	I hate bananas	bananas
4	I love oranges	oranges
5	I like hamburgers
6
7
8	mangoes
9	oranges
10	bananas
Sheet974

Cell Formulas
Range	Formula
B1:B5	B1	=IFERROR(LOOKUP(2,1/SEARCH(A$8:A$10,A1),A$8:A$10),"")

 

[khan123]

This is awesome, I needed that. If the match is not found, I want to show the original cell content, which I think I should be able to do. I would have never been able to solve this!

 

[jtakw]

This is awesome, I needed that. If the match is not found, I want to show the original cell content, which I think I should be able to do. I would have never been able to solve this!

Yes, just replace the "" at the end of the formula with the subject cell:

Book3.xlsx
	A	B
1	I love mangoes	mangoes
2	I like mangoes	mangoes
3	I hate bananas	bananas
4	I love oranges	oranges
5	I like hamburgers	I like hamburgers
6
7
8	mangoes
9	oranges
10	bananas
Sheet974

Cell Formulas
Range	Formula
B1:B5	B1	=IFERROR(LOOKUP(2,1/SEARCH(A$8:A$10,A1),A$8:A$10),A1)

 

[khan123]

Perfect, solved my really complicated problem!