Home

Vlookup funcion with best possible match

%3CLINGO-SUB%20id%3D%22lingo-sub-872567%22%20slang%3D%22en-US%22%3EVlookup%20funcion%20with%20best%20possible%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-872567%22%20slang%3D%22en-US%22%3E%3CP%3E%3CSPAN%3EHello%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CSPAN%3EI%20am%20trying%20to%20compare%20two%20list%20where%20I%20need%20to%20check%20from%20list1%20if%20the%20names%20do%20exist%20at%20list%202%20but%20the%20issue%20is%20that%20on%20list%202%20not%20all%20the%20names%20are%20are%20not%20full%20match%20so%20there%20is%20slight%20differences%20in%20the%20names%20as%20you%20can%20see%20from%20below%20tables.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%3EI%20did%20try%20with%20VLOOKUP%20function%20%3DVLOOKUP(%22*%22%26amp%3B%5B%40Company%5D%26amp%3B%22*%22%2CSalesForce%2C1%2CFALSE)%20but%20do%20not%20get%20best%20match%20from%20this.%3C%2FSPAN%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FP%3E%3CDIV%20class%3D%22cms_table%22%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E%3CU%3ECompany%3C%2FU%3E%3C%2FTD%3E%3CTD%3E%3CU%3ESalesForce%3C%2FU%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CSTRONG%3EGoogle%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EGoogle%20foods%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CSTRONG%3EYahoo%3C%2FSTRONG%3E%3C%2FTD%3E%3CTD%3EGoogle%20corporation%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EGoogle%20LLC%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%3CSTRONG%3EThe%20yahoo%3C%2FSTRONG%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EYahoo%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FDIV%3E%3CP%3E%3CSPAN%3EThe%20match%20I%20get%20is%20highlited%20in%20bold%20so%20my%20idea%20is%20to%20do%20best%20match%20not%20the%20first%20one%20in%20a%20row%20from%20the%20second%20list%20SalesForce.%20Any%20idea%20will%20be%20much%20appreciated%20%3F%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3EKushtrim%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-872567%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-873434%22%20slang%3D%22en-US%22%3ERe%3A%20Vlookup%20funcion%20with%20best%20possible%20match%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-873434%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F414368%22%20target%3D%22_blank%22%3E%40Kushtrim_A%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHow%20do%20you%20define%20%22best%22%20match%3F%20That%20is%20a%20subjective%20evaluation%20a%20human%20being%20makes.%20Excel%20can%20return%20the%20first%20or%20the%20last%20match%20or%20count%20all%20the%20cells%20with%20text%20that%20contain%20the%20search%20term%2C%20but%20it%20cannot%20tell%20if%20%22Google%20Foods%22%20is%20a%20%3CSTRONG%3Ebetter%3C%2FSTRONG%3E%20match%20than%20%22Google%20corporation%22.%26nbsp%3B%20It%20needs%20a%20logical%20approach%20for%20%22best%22.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESo%2C%20what%20is%20the%20logic%20that%20identifies%20the%20%22best%22%20match%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Kushtrim_A
Occasional Visitor

Hello


I am trying to compare two list where I need to check from list1 if the names do exist at list 2 but the issue is that on list 2 not all the names are are not full match so there is slight differences in the names as you can see from below tables.
I did try with VLOOKUP function =VLOOKUP("*"&[@Company]&"*",SalesForce,1,FALSE) but do not get best match from this.


CompanySalesForce
GoogleGoogle foods
YahooGoogle corporation
 Google LLC
 The yahoo
 Yahoo

The match I get is highlited in bold so my idea is to do best match not the first one in a row from the second list SalesForce. Any idea will be much appreciated ?

 

Regards,

Kushtrim

1 Reply

Hello @Kushtrim_A 

 

How do you define "best" match? That is a subjective evaluation a human being makes. Excel can return the first or the last match or count all the cells with text that contain the search term, but it cannot tell if "Google Foods" is a better match than "Google corporation".  It needs a logical approach for "best".

 

So, what is the logic that identifies the "best" match?

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies