Forum Discussion
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
Hi Varsit_N_Shah ,
Probably not the best approach but I used a calculated column for wild cards and then xlookup
Varsit_N_Shah : Hi, in which case did the formula stand incorrect, will try to sort it out?
=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
