Lookup based on 3 variables, one that has a range

[DPR]

- Our business utilizes multiple independent sales agents to sell our products.
- We pay agents a commission based on the selling price.
- The commission structure is unique for each agent.
- To remove the inefficiency of staff maually looking up commission rates based on the selling price, I would like the commission rate to be populated by looking up the rate based on the Agent, Product and Selling Price as defined in a commission table.
- I have unsuccessfully tried various combinations of VLOOKUP, CHOOSE, INDEX and MATCH functions to accomplish this.
- The challenge is that the commission is based on Selling Price ranges in the commission table and not an exact match on the three variables (Agent, Product, Selling Price).
- The uploaded file contains an example.

Any guidance would be greatly appreciated.

DPR

 

[Chihiro]

Something like this?
=SUMPRODUCT(($A$18:$A$41=A47)*($B$18:$B$41=B47)*($C$18:$C$41<=C47)*($D$18:$D$41>=C47),$E$18:$E$41)

 

[John Jairo V]

Hi to all!

Another option:
=LOOKUP(C47,$C$18:$C$41/(($A$18:$A$41=A47)*($B$18:$B$41=B47)),$E$18:$E$41)

Blessings!

 

[DPR]

- Our business utilizes multiple independent sales agents to sell our products.
- We pay agents a commission based on the selling price.
- The commission structure is unique for each agent.
- To remove the inefficiency of staff maually looking up commission rates based on the selling price, I would like the commission rate to be populated by looking up the rate based on the Agent, Product and Selling Price as defined in a commission table.
- I have unsuccessfully tried various combinations of VLOOKUP, CHOOSE, INDEX and MATCH functions to accomplish this.
- The challenge is that the commission is based on Selling Price ranges in the commission table and not an exact match on the three variables (Agent, Product, Selling Price).
- The uploaded file contains an example.

Any guidance would be greatly appreciated.

DPR

Something like this?
=SUMPRODUCT(($A$18:$A$41=A47)*($B$18:$B$41=B47)*($C$18:$C$41<=C47)*($D$18:$D$41>=C47),$E$18:$E$41)

Chihiro - Brilliant! Works perfectly. And using the formula highlighted that my example actually had a few errors in the commission amounts I hardcoded. Thank you

 

[DPR]

Hi to all!

Another option:
=LOOKUP(C47,$C$18:$C$41/(($A$18:$A$41=A47)*($B$18:$B$41=B47)),$E$18:$E$41)

Blessings!

John - Also brilliant and works perfectly! Much obliged.

 

[Khalid NGO]

Hi,

Or same as Chihiro's solution with SUMIFS:
=SUMIFS($E$18:$E$41,$A$18:$A$41,A47,$B$18:$B$41,B47,$C$18:$C$41,"<="&C47,$D$18:$D$41,">="&C47)

Regards,

 

[bosco_yip]

Another option using VLOOKUP(),

=VLOOKUP(C47,IF({1,0},INDEX(C$18:C$41/(A$18:A$41=A47)/(B$18:B$41=B47),),E$18:E$41),2)

Regards
Bosco

 

[DPR]

Hi,

Or same as Chihiro's solution with SUMIFS:
=SUMIFS($E$18:$E$41,$A$18:$A$41,A47,$B$18:$B$41,B47,$C$18:$C$41,"<="&C47,$D$18:$D$41,">="&C47)

Regards,

Thank you Khalid, definitely another option for me!

 

[DPR]

Another option using VLOOKUP(),

=VLOOKUP(C47,IF({1,0},INDEX(C$18:C$41/(A$18:A$41=A47)/(B$18:B$41=B47),),E$18:E$41),2)

Regards
Bosco

Thank you Bosco - I'll give this one a try also.