| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Match word in one column to another
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. -- David P. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match word in one column to another
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." wrote: 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match word in one column to another
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. -- David P. "Max" wrote: 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." wrote: 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match word in one column to another
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." wrote in message ... 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. -- David P. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match word in one column to another
Could it search for "Delicious Orange" as an entire phrase?
-- David P. "Max" wrote: 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." wrote in message ... 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. -- David P. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Match word in one column to another
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 --- |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need to match 2 columns, if a match found add info from 2nd column | Excel Worksheet Functions | |||
| Match using array of column and row references to match with | Excel Worksheet Functions | |||
| match word in one column form list in another | Excel Worksheet Functions | |||
| Any way for 2 column vlookups. i.e match last name then match firs | Excel Worksheet Functions | |||
| need formula to search column for a word and return another word | Excel Discussion (Misc queries) | |||
Hybrid Mode
