Oct 25 2020 12:13 AM
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
Oct 25 2020 03:05 AM
Hi @Varsit_N_Shah ,
Probably not the best approach but I used a calculated column for wild cards and then xlookup
Oct 25 2020 03:09 AM
=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
Oct 25 2020 05:49 AM
Oct 25 2020 10:51 AM
@Varsit_N_Shah : Hi, in which case did the formula stand incorrect, will try to sort it out?
Oct 25 2020 11:02 AM
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.
Oct 25 2020 11:54 AM
@JoeMcDaid Can you help?
Oct 25 2020 12:23 PM
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?