Finding the best match query from 2 databases.

Question

Hello!
I'm trying to find a best match fit of relevant data from reference data but facing an error due to names being mislabelled.
For instance, in Sheet14, in cell A13, the company name is IRFC and in the reference sheet (NSE), the company's entire name is used (B820) instead of the abbreviation. Although the code/ticker is used in a different cell (A820) . 
Please see the reference sheet attached - link
I have the formula running for company names on with data from 2 exchanges to prevent missing entries but not getting the search key right.
Any help will be appreciated!
Thanks!

Gill K · Accepted Answer

Hi rohang,
There is no perfect formula that will do what you want, but you can add extra tests to try to catch more of the specific cases you have.
For example, in tab GK.Help, in cell E2, I have expanded your formula to the following:
={"NSE Names";ARRAYFORMULA(
  IF(A3:A="","",
    IFERROR(VLOOKUP("*"&LEFT(A3:A,20)&"*",NSE!B$1:B,1,0),
    IFERROR(VLOOKUP("*"&LEFT(A3:A,15)&"*",NSE!B$1:B,1,0),
    IFERROR(VLOOKUP("*"&LEFT(A3:A,12)&"*",NSE!B$1:B,1,0),
    IFERROR(VLOOKUP("*"&LEFT(A3:A,10)&"*",NSE!B$1:B,1,0),
    IFERROR(VLOOKUP("*"&A3:A&"*",NSE!A$1:B,2,0),
    IFERROR(VLOOKUP(REGEXEXTRACT(A3:A,"(.*) "),NSE!A$1:B,2,0),))))))))}
This formula now matches 22 names, filling all down the column, most of which I think are correct.  There is always a chance of a mistake creeping in, if the logic is not perfect for finding matches.  And the data is too variable to be sure of getting the logic perfect.
For example, in E33, Reliance Capital probably shouldn't equal the Reliance Chemotex Industries Limited company.  By going down to matching just ten letters, it ended up taking the first company beginning with Reliance.
You can expand the formula to try to add other ways of searching for matches, but I don't think that you can guarantee it will catch everything.
Cheers,
Gill