Partial Match List

First time posting to the forum. I've read through several similar posts and have ran into a corner. I'm attempting to match two lists with partial match company names.

My goal is to match up Column E with Column B in order to identify duplicate companies.

Here's what I've tried

I've trimmed the first word in Column e's name into Column D. I'm now trying to find Column D in Column B.

=IF(ISERROR(MATCH(D1,$B$1:$B$5000,0)), A1,"")

The trouble seems to be in my result. It will always just give me the result from row a. In trying the match the two lists, I'd like to learn how to have it find D in B and give me either the result it finds in B or the result from column A next to the match in column B. I've attached the sheet.

Help from someone more experienced with this type of function would be tremendously helpful.

Thank you!

good am. Welcome to the forum..
can you try this one.

=INDEX(A:A,MATCH("*"&D1&"*",B:B,0))

you're on excel 2010

you can use.

=iferror(INDEX(A:A,MATCH("*"&D1&"*",B:B,0)),"")

welcome
btw if you like you can also do this

=IFERROR(INDEX($A:$A,MATCH("*"&D4&"*",$B:$B,0))&" "&INDEX($B:$B,MATCH("*"&D4&"*",$B:$B,0)),"")

it will return the nuber and the text of column B

e.g


167 First California Mortgage Co. (CA)

Partial matching is often problematic.

I've run your two lists through some experimental software which aims to identify best matches by looking for short substrings of characters in common.

Please see the two attachments.

Save the TSV file to a convenient location and open up the workbook.

Hit the go button and pick the TSV file at the prompt.

You will then get a form which will show the 20 closest matches for each of the terms in column E. Click the appropriate radio button and then move to the next one.