Forum Discussion
VLOOKUP partial match from one sheet to another
Trying to use VLOOKUP to transfer ID data from https://ontariogov-my.sharepoint.com/:x:/r/personal/celise_kirkpatrick_ontario_ca/_layouts/15/Doc.aspx?sourcedoc=%7B580F1227-494F-4B39-9789-FF2B46BA1EAD%7D&file=Book%202.xlsx&action=editnew&mobileredirect=true&login_hint=Celise.Kirkpatrick%40ontario.ca&wdNewAndOpenCt=1668784537338&ct=1668784537338&wdPreviousSession=98119629-d00a-49c8-b377-a691adb18012&wdOrigin=OFFICECOM-WEB.START.NEW to https://ontariogov-my.sharepoint.com/:x:/r/personal/celise_kirkpatrick_ontario_ca/_layouts/15/Doc.aspx?sourcedoc=%7B4668628A-0590-4366-AEC6-22AA85CBC6B0%7D&file=Book%202.xlsx&action=editnew&mobileredirect=true&login_hint=Celise.Kirkpatrick%40ontario.ca&wdNewAndOpenCt=1668784439862&ct=1668784439862&wdPreviousSession=98119629-d00a-49c8-b377-a691adb18012&wdOrigin=OFFICECOM-WEB.START.NEW when it isn't an exact match.
Thanks in advance!
1 Reply
CK1231565 I couldn't open your links but I attached a workbook with some LAMBDA functions I wrote for NearMatches and NearMatches2.
In the simple case if the short name is a truncation of the long name you can simple do a search for the short name starting the names in the long name list something like this:
=IFERROR( XMATCH( [short_name], LEFT( [long_name_list], LEN( [short_name] ) ) , "none" )
But this doesn't work if the short name list isn't just a truncation of the long name (e.g. "Google Analytics Corporation" vs "Google Corp"). So the LAMBDA function in the attached file finds the best match (i.e. the most characters match) and you can set a minimum. So you can say at least 5 characters must match so you don't get "GooGoo Girls" to match "Google ..." and even if you did set a min of only 3 then it would still return "Google ..." instead of "GooGoo" because it matches more characters.
