Aug 18 2021 08:44 AM
I have three vendors that pay different commissions on sales. How do I look up the vendor in a table, find the corresponding percentage and use that percentage in a different table to calculate commission?
Date | Company | Instrument | Vendor | Price | Commission |
1/12/21 | A | 100 | A | 5000 | |
1/15/21 | B | 1001 | A | 7000 | |
1/31/21 | C | 10003 | B | 3000 |
Vendor | Percentage |
A | 1.50% |
B | 2.00% |
C | 2.50% |
Based on the above tables. Based on vendor A in the first table match it to the second table and retrieve the corresponding percentage and use it in the formula =sum(E2*"search result") and put it in cell F2. I have also attached the spreadsheet showing how I want to set it up. I have these two tables on separate worksheets. I hope this makes sense.
Aug 18 2021 12:27 PM
Solution=E2*XLOOKUP(D2,Sheet2!$A$2:$A$4,Sheet2!$B$2:$B$4)
Aug 19 2021 07:16 AM
I used your formula and it worked fantastic on the first two vendors. On the third vendor which is in cells A4 and B4 it is giving me a #NA error. I have checked my cell references and they are all correct. If I change the vendor name the formula works. I have also checked the spelling of the vendors. Do you have any suggestions?
Aug 19 2021 07:21 AM
Aug 18 2021 12:27 PM
Solution