Forum Discussion
Formula Help
I am new to excel but am trying to accomplish what I think is rather difficult. I have many thousand items in Column A which is standard content from a product. I have requested items in Column B that I want to compare and look for in Column A. However, I am hoping to be able to look for approximate matches and not exact matches. I then want to return the value from Column A if there is an approximate match.
Example: B2 is 'Abdominal Hysterectomy' and I want to look for that in Column A and if it exists, return the value of Column A as the result. In Column A however, the item could be called Hysterectomy Abdominal and therefor not be an exact match. Is this possible?
Thank you!
3 Replies
=INDEX($A$1:$A$12,MATCH(1,N(ISNUMBER(SEARCH(LEFT(B1,SEARCH(" ",B1)-1),$A$1:$A$12)))*N(ISNUMBER(SEARCH(RIGHT(B1,LEN(B1)-SEARCH(" ",B1)),$A$1:$A$12))),0))Maybe with this formula if you are looking for an approximate match of 2 words in switched order.
Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
