Forum Discussion
Using results of search in a formula
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.
=E2*XLOOKUP(D2,Sheet2!$A$2:$A$4,Sheet2!$B$2:$B$4)
3 Replies
=E2*XLOOKUP(D2,Sheet2!$A$2:$A$4,Sheet2!$B$2:$B$4)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?
- Detlef_Lewin - I went in and deleted the data on Sheet2 and then reentered it. This corrected the problem. Not sure what happened but think you for your help.
