New Contributor

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.

3 Replies

Re: Using results of search in a formula

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

Re: Using results of search in a formula

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?

Re: Using results of search in a formula

@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.