V-lookup function on Primary key with multiple variations

[Varsit_N_Shah]

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

[#2]

Hi @Varsit_N_Shah ,

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

[#3]

=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

[#4]

@saybhatt 

Hi @saybhatt 

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

Varsit N. Shah

[#5]

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

[#6]

@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. 

[#7]

@JoeMcDaid Can you help?

[#8]

@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?