Formula Help

[kscheer]

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!

[#2]

@kscheer 

=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. 

[#3]

Thank you I will give it a try!

[#4]

Unfortunately that did not work. Thank you anyways!