# V-lookup function on Primary key with multiple variations

Occasional Contributor

# V-lookup function on Primary key with multiple variations

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. Qty Amount Need name of Customer 772 1 100 702 2 500 GST/0621/20-21 3 600 B2B/0623/20-21 4 300 627 5 200 B2B/630 6 800 440 7 950 470/2019-20 8 750 GST/0650/20-21 9 300 245470381 10 50 MIS245470380 11 1000 GST/06545/2021 12 835

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 Customer Need Amount RKS/772/20-21 A MGPCL/702/20-21 B 621 X B2B/0623 E B2B/0627/20-21 R 630 F GST/440/2019-20 G 470 T B2B/0650/20-21 M MIS245470381 J MIS24547000380 K B2B/0645/20-21 Y

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

# Re: V-lookup function on Primary key with multiple variations

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

# Re: V-lookup function on Primary key with multiple variations

=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

# Re: V-lookup function on Primary key with multiple variations

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

Varsit N. Shah

# Re: V-lookup function on Primary key with multiple variations

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

# Re: V-lookup function on Primary key with multiple variations

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.

# Re: V-lookup function on Primary key with multiple variations

@Joe McDaid Can you help?

# Re: V-lookup function on Primary key with multiple variations

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?