Formula? To match account names in seperate sheets - Containing nearest match

I am looking for a formula that will help me match a long list of account names.

Have 2 sheets from data from separate databases, containing Account names. I know there are matches in Accounts from our database, that do not appear in the 2nd database. Spot checking a few i noticed many are matched account but the spelling and/or wording is slightly different.
- Using a VLookup "True" verdict is not sufficient to use
- I tried using the "Exact" verdict, but this was not helpful either

Any ideas for better find/match formulas to help minimize the manual checking for all 1200 rows of data.

Thanks again
RohanF

Please post a sample workbook with expected results.

Use "Go advanced" and click on "Paper Clip" icon to upload file.

This has identified some matches so hopefully it goes some way to helping you.

Thanks John very helpful, as it returned some extra details.

I would like to understand more about the INDEX/MATCH formula, as it is very helpful.

Can the formula be extend to either exclude or search for a wildcard of the below, which are several ways to write the same thing; and is cause to a lot of unsuccessful matching.?
- PTY LTD
- PROPRIETARY LIMITED
- PTY LIMITED
- LIMITED

Thanks FD, very useful insight.

Looping back to Johns formula, he did use MATCH within the syntax

=IFERROR(INDEX(DATABASE1!D:D,MATCH("*"&LEFT($A2,10)&"*",DATABASE1!$C:$C,0)),"")

In regards to this formula
=IFERROR(INDEX(DATABASE1!D:D,MATCH("*"&LEFT($A2,10)&"*",DATABASE1!$C:$C,0)),"")

in the MATCH function what is true meaning for "*"&LEFT($A2,10). ??

Does this infer wildcard find for the first 10 characters in Cell A2..

@RohanF


I see the forum experts have been very helpful (as always) in addressing your query to me, Have you got the answers you require?

John

Hi John, yes helpful as always.

In regards to the formula
=IFERROR(INDEX(DATABASE1!D:D,MATCH("*"&LEFT($A2,10)&"*",DATABASE1!$C:$C,0)),"")

in the MATCH function what is true meaning for "*"&LEFT($A2,10). ??

Does this infer wildcard find for the first 10 characters in Cell A2..

I am trying to edit this slightly to define more possibilities

LEFT finds the characters starting at the LEFT. The RIGHT function is its equivalent and the MID function allows the selection of characters anywhere in a string.

In my formula it is looking for any 10 character match going effectively left to right in the text.

I should have added that I also tried a 20 character match which was much less successful: it therefore might be worth trying a 5 character match to unearth a few more "rogues".