Using results of search in a formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2661285%22%20slang%3D%22en-US%22%3EUsing%20results%20of%20search%20in%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2661285%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20three%20vendors%20that%20pay%20different%20commissions%20on%20sales.%20%26nbsp%3BHow%20do%20I%20look%20up%20the%20vendor%20in%20a%20table%2C%20find%20the%20corresponding%20percentage%20and%20use%20that%20percentage%20in%20a%20different%20table%20to%20calculate%20commission%3F%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22522%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2287%22%20height%3D%2221%22%3EDate%3C%2FTD%3E%3CTD%20width%3D%2287%22%3ECompany%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EInstrument%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EVendor%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EPrice%3C%2FTD%3E%3CTD%20width%3D%2287%22%3ECommission%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E1%2F12%2F21%3C%2FTD%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3E100%3C%2FTD%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3E5000%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E1%2F15%2F21%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3E1001%3C%2FTD%3E%3CTD%3EA%3C%2FTD%3E%3CTD%3E7000%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3E1%2F31%2F21%3C%2FTD%3E%3CTD%3EC%3C%2FTD%3E%3CTD%3E10003%3C%2FTD%3E%3CTD%3EB%3C%2FTD%3E%3CTD%3E3000%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22174%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2287%22%20height%3D%2221%22%3EVendor%3C%2FTD%3E%3CTD%20width%3D%2287%22%3EPercentage%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3EA%3C%2FTD%3E%3CTD%3E1.50%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3EB%3C%2FTD%3E%3CTD%3E2.00%25%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2221%22%3EC%3C%2FTD%3E%3CTD%3E2.50%25%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3EBased%20on%20the%20above%20tables.%20%26nbsp%3BBased%20on%20vendor%20A%20in%20the%20first%20table%20match%20it%20to%20the%20second%20table%20and%20retrieve%20the%20corresponding%20percentage%20and%20use%20it%20in%20the%20formula%20%3Dsum(E2*%22search%20result%22)%20and%20put%20it%20in%20cell%20F2.%20%26nbsp%3BI%20have%20also%20attached%20the%20spreadsheet%20showing%20how%20I%20want%20to%20set%20it%20up.%20%26nbsp%3BI%20have%20these%20two%20tables%20on%20separate%20worksheets.%20%26nbsp%3BI%20hope%20this%20makes%20sense.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2661285%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2662350%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20results%20of%20search%20in%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2662350%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1131487%22%20target%3D%22_blank%22%3E%40smorgan94%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DE2*XLOOKUP(D2%2CSheet2!%24A%242%3A%24A%244%2CSheet2!%24B%242%3A%24B%244)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2665415%22%20slang%3D%22en-US%22%3ERe%3A%20Using%20results%20of%20search%20in%20a%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2665415%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1639%22%20target%3D%22_blank%22%3E%40Detlef%20Lewin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20used%20your%20formula%20and%20it%20worked%20fantastic%20on%20the%20first%20two%20vendors.%20%26nbsp%3BOn%20the%20third%20vendor%20which%20is%20in%20cells%20A4%20and%20B4%20it%20is%20giving%20me%20a%20%23NA%20error.%20%26nbsp%3BI%20have%20checked%20my%20cell%20references%20and%20they%20are%20all%20correct.%20%26nbsp%3BIf%20I%20change%20the%20vendor%20name%20the%20formula%20works.%20%26nbsp%3BI%20have%20also%20checked%20the%20spelling%20of%20the%20vendors.%20%26nbsp%3BDo%20you%20have%20any%20suggestions%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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

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