Match word in one column to another

[David P.]

I need a formula to output in column C the closest match in column B from
column A:

Column A Coumn B Column C
"I ate apples" "Orange" Formula to output "Apple" as a
closest match
"I ate oranges" "Pear"
"I ate pears" "Apple"

I know this is probably elementary but thank you for the help.

 

[Max]

One way
In C1, normal ENTER:
=INDEX(B$1:B$3,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($B$1:$B$3,A1)),),0))
Copy down. Adapt the range: B$1:B$3 to suit
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik

 

[David P.]

Thank you Max. How would it change the formula if I had mulitple words in
column B like this:

Column A Coumn B Column C
"I ate apples" "Delicious Orange" Output "Delicious Apple" as
closest match
"I ate oranges" "Delicious Pear"
"I ate pears" "Delicious Apple"

I ask this because when I changed column B to more than one word it gave me
an #N/A. I will have multiple words in Column B in many cases. Many thanks.

 

[Max]

Yes, it doesn't work with multiple words. I mean, for example, how would you
possibly expect Excel to know which word in the phrase to focus on,
"Delicious" or "Orange"? Excel will just take the entire phrase to search,
unemotively.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik

 

[David P.]

Could it search for "Delicious Orange" as an entire phrase?

 

[Max]

Could it search for "Delicious Orange" as an entire phrase?

Yes of course. If it is as an entire phrase, then my original response
should have worked. You might want to fortify it a bit more by using TRIM
around A1, since the search string (the entire phrase) now contains a single
space:
=INDEX(B$1:B$3,MATCH(TRUE,INDEX(ISNUMBER(SEARCH($B$1:$B$3,TRIM(A1))),),0))

If this response helped in any way, do press the YES button below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---