New Contributor

# 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

# Re: Formula Help

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

# Re: Formula Help

Thank you I will give it a try!

# Re: Formula Help

Unfortunately that did not work. Thank you anyways!