V-lookup function on Primary key with multiple variations

%3CLINGO-SUB%20id%3D%22lingo-sub-1815660%22%20slang%3D%22en-US%22%3EV-lookup%20function%20on%20Primary%20key%20with%20multiple%20variations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1815660%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Experts%20%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%202%20tables%20in%20my%20workbook.%20Table%20A%20has%20below%20columns.%20In%20Table%20A%20I%20need%20name%20of%20customers%20from%20Table%20B%20on%20the%20basis%20of%20Invoice%20Number.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22473%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22473%22%3ETABLE%20A%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EInvoice%20no.%3C%2FTD%3E%3CTD%3EQty%3C%2FTD%3E%3CTD%3EAmount%3C%2FTD%3E%3CTD%3ENeed%20name%20of%20Customer%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E772%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E100%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E702%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E500%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EGST%2F0621%2F20-21%3C%2FTD%3E%3CTD%3E3%3C%2FTD%3E%3CTD%3E600%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EB2B%2F0623%2F20-21%3C%2FTD%3E%3CTD%3E4%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E627%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E200%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EB2B%2F630%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E800%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E440%3C%2FTD%3E%3CTD%3E7%3C%2FTD%3E%3CTD%3E950%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E470%2F2019-20%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E750%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EGST%2F0650%2F20-21%3C%2FTD%3E%3CTD%3E9%3C%2FTD%3E%3CTD%3E300%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E245470381%3C%2FTD%3E%3CTD%3E10%3C%2FTD%3E%3CTD%3E50%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMIS245470380%3C%2FTD%3E%3CTD%3E11%3C%2FTD%3E%3CTD%3E1000%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EGST%2F06545%2F2021%3C%2FTD%3E%3CTD%3E12%3C%2FTD%3E%3CTD%3E835%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETable%20B%20has%20below%20format%20and%20I%20need%20sale%20amount%20here%20from%20Table%20A%20on%20the%20basis%20of%20Invoice%20No.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22337%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%22337%22%3ETABLE%20B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EInvoice%20no.%3C%2FTD%3E%3CTD%3EName%20of%20Customer%3C%2FTD%3E%3CTD%3ENeed%20Amount%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERKS%2F772%2F20-21%3C%2FTD%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMGPCL%2F702%2F20-21%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E621%3C%2FTD%3E%3CTD%3EX%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EB2B%2F0623%3C%2FTD%3E%3CTD%3EE%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EB2B%2F0627%2F20-21%3C%2FTD%3E%3CTD%3ER%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E630%3C%2FTD%3E%3CTD%3EF%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EGST%2F440%2F2019-20%3C%2FTD%3E%3CTD%3EG%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E470%3C%2FTD%3E%3CTD%3ET%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EB2B%2F0650%2F20-21%3C%2FTD%3E%3CTD%3EM%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMIS245470381%3C%2FTD%3E%3CTD%3EJ%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EMIS24547000380%3C%2FTD%3E%3CTD%3EK%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EB2B%2F0645%2F20-21%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBut%20as%20there%20are%20multiple%20variation%20in%20Invoice%20Number%20in%20both%20the%20table%20%2C%20vlookup%20function%20is%20not%20working%20properly.%20Pls%20guide%20how%20to%20resolve%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20a%20ton%20in%20advance%20for%20sparing%20time%20for%20me%20in%20replying.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVarsit%20N%20Shah%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1815660%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1815778%22%20slang%3D%22en-US%22%3ERe%3A%20V-lookup%20function%20on%20Primary%20key%20with%20multiple%20variations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1815778%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F844595%22%20target%3D%22_blank%22%3E%40Varsit_N_Shah%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3EProbably%20not%20the%20best%20approach%20but%20I%20used%20a%20calculated%20column%20for%20wild%20cards%20and%20then%20xlookup%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1815780%22%20slang%3D%22en-US%22%3ERe%3A%20V-lookup%20function%20on%20Primary%20key%20with%20multiple%20variations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1815780%22%20slang%3D%22en-US%22%3E%3CP%3E%3DXLOOKUP(%22*%22%26amp%3BB3%26amp%3B%22*%22%2C%24H%243%3A%24H%2414%2C%24I%243%3A%24I%2414%2C%22%22%2C2%2C1)%26nbsp%3B%3CBR%20%2F%3Eyou%20can%20also%20use%20this%20directly%20instead%20of%20the%20calculated%20column%2C%20works%20the%20same%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1815937%22%20slang%3D%22en-US%22%3ERe%3A%20V-lookup%20function%20on%20Primary%20key%20with%20multiple%20variations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1815937%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F686497%22%20target%3D%22_blank%22%3E%40saybhatt%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F686497%22%20target%3D%22_blank%22%3E%40saybhatt%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much%20for%20the%20new%20insight.%20It%20worked%20but%20not%20100%25.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVarsit%20N.%20Shah%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1816422%22%20slang%3D%22en-US%22%3ERe%3A%20V-lookup%20function%20on%20Primary%20key%20with%20multiple%20variations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1816422%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F844595%22%20target%3D%22_blank%22%3E%40Varsit_N_Shah%3C%2FA%3E%26nbsp%3B%3A%20Hi%2C%20in%20which%20case%20did%20the%20formula%20stand%20incorrect%2C%20will%20try%20to%20sort%20it%20out%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1816431%22%20slang%3D%22en-US%22%3ERe%3A%20V-lookup%20function%20on%20Primary%20key%20with%20multiple%20variations%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1816431%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F686497%22%20target%3D%22_blank%22%3E%40saybhatt%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20%2C%20I%20have%20attached%20file%20for%20your%20reference.%20Mainly%20it%20didn't%20worked%20at%20places%20where%20wild%20card%20characters%20are%20not%20there%20in%20the%20lookup%20value%20associated%20in%20lookup%20array.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hello Experts ,

 

I have 2 tables in my workbook. Table A has below columns. In Table A I need name of customers from Table B on the basis of Invoice Number.

 

TABLE A
Invoice no.QtyAmountNeed name of Customer
7721100 
7022500 
GST/0621/20-213600 
B2B/0623/20-214300 
6275200 
B2B/6306800 
4407950 
470/2019-208750 
GST/0650/20-219300 
2454703811050 
MIS245470380111000 
GST/06545/202112835 

 

Table B has below format and I need sale amount here from Table A on the basis of Invoice No. 

 

TABLE B
Invoice no.Name of CustomerNeed Amount
RKS/772/20-21A 
MGPCL/702/20-21B 
621X 
B2B/0623E 
B2B/0627/20-21R 
630F 
GST/440/2019-20G 
470T 
B2B/0650/20-21M 
MIS245470381J 
MIS24547000380K 
B2B/0645/20-21Y 

 

But as there are multiple variation in Invoice Number in both the table , vlookup function is not working properly. Pls guide how to resolve this.

 

Thanks a ton in advance for sparing time for me in replying.

 

Varsit N Shah

7 Replies

Hi @Varsit_N_Shah ,

Probably not the best approach but I used a calculated column for wild cards and then xlookup

=XLOOKUP("*"&B3&"*",$H$3:$H$14,$I$3:$I$14,"",2,1) 
you can also use this directly instead of the calculated column, works the same

@saybhatt 

 

Hi @saybhatt 

 

Thank you so much for the new insight. It worked but not 100%. 

 

Varsit N. Shah

@Varsit_N_Shah : Hi, in which case did the formula stand incorrect, will try to sort it out?

@saybhatt 

 

Hi , I have attached file for your reference. Mainly it didn't worked at places where wild card characters are not there in the lookup value associated in lookup array. 

@Varsit_N_Shah 

If I understood correctly we lookup when any part of lookup value text shall met any part of the texts in lookup array. Afraid that doesn't work, bit more logic is required. For example, shall we consider GST/0650/20-21 and B2B/0650/20-21 as matched and why?