SOLVED

Using results of search in a formula

Copper Contributor

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?

DateCompanyInstrumentVendorPriceCommission
1/12/21A100A5000 
1/15/21B1001A7000 
1/31/21C10003B3000 

 

 

VendorPercentage
A1.50%
B2.00%
C2.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. 

 

 

 

3 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@smorgan94 

=E2*XLOOKUP(D2,Sheet2!$A$2:$A$4,Sheet2!$B$2:$B$4)

@Detlef Lewin 

 

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.
1 best response

Accepted Solutions
best response confirmed by allyreckerman (Microsoft)
Solution

@smorgan94 

=E2*XLOOKUP(D2,Sheet2!$A$2:$A$4,Sheet2!$B$2:$B$4)

View solution in original post