Excel: Commission rates using multiple criteria in separate cells - PLEASE HELP

[snyderjames724]

Hey everyone,

I am trying to pull commission rates using a commission reference sheet which contains multiple criteria on the x axis and 1 criteria on the y axis. I have tried indexes, match functions, arrays, etc and cannot get it to pull a commission rate when both criteria from the x axis are matched with a salesperson on the y axis. For example, when James sells an SUV in Utah, I want a formula that will pull 0.0325 from the Commission Rate tab (example below).

See below:

Sales Data example:

[TABLE="width: 417"]
<tbody>[TR]
[TD]Fname
[/TD]
[TD]Type
[/TD]
[TD]State
[/TD]
[TD] Amount
[/TD]
[TD] Commission Rate 1
[/TD]
[/TR]
[TR]
[TD]James
[/TD]
[TD]SUV
[/TD]
[TD]UT
[/TD]
[TD] 38,968.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chris
[/TD]
[TD]Standard
[/TD]
[TD]PA
[/TD]
[TD] 24,305.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Matt
[/TD]
[TD]Standard
[/TD]
[TD]PA
[/TD]
[TD] 22,060.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter
[/TD]
[TD]Compact
[/TD]
[TD]UT
[/TD]
[TD] 13,311.00
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fred
[/TD]
[TD]Compact
[/TD]
[TD]PA
[/TD]
[TD] 17,097.00
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Commission Rates:

[TABLE="width: 868"]
<tbody>[TR]
[TD][/TD]
[TD]NH
[/TD]
[TD]NH
[/TD]
[TD]NH
[/TD]
[TD]NH
[/TD]
[TD]UT
[/TD]
[TD]UT
[/TD]
[TD]UT
[/TD]
[TD]UT
[/TD]
[TD]PA
[/TD]
[TD]PA
[/TD]
[TD]PA
[/TD]
[TD]PA
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Compact
[/TD]
[TD]Standard
[/TD]
[TD]SUV
[/TD]
[TD]Luxury
[/TD]
[TD]Compact
[/TD]
[TD]Standard
[/TD]
[TD]SUV
[/TD]
[TD]Luxury
[/TD]
[TD]Compact
[/TD]
[TD]Standard
[/TD]
[TD]SUV
[/TD]
[TD]Luxury
[/TD]
[/TR]
[TR]
[TD]James
[/TD]
[TD] 0.0150
[/TD]
[TD] 0.0250
[/TD]
[TD] 0.0350
[/TD]
[TD] 0.0525
[/TD]
[TD] 0.0125
[/TD]
[TD] 0.0225
[/TD]
[TD] 0.0325
[/TD]
[TD] 0.0500
[/TD]
[TD] 0.0160
[/TD]
[TD] 0.0260
[/TD]
[TD] 0.0360
[/TD]
[TD] 0.0535
[/TD]
[/TR]
[TR]
[TD]Chris
[/TD]
[TD] 0.0150
[/TD]
[TD] 0.0250
[/TD]
[TD] 0.0350
[/TD]
[TD] 0.0525
[/TD]
[TD] 0.0125
[/TD]
[TD] 0.0225
[/TD]
[TD] 0.0325
[/TD]
[TD] 0.0500
[/TD]
[TD] 0.0160
[/TD]
[TD] 0.0260
[/TD]
[TD] 0.0360
[/TD]
[TD] 0.0535
[/TD]
[/TR]
[TR]
[TD]Matt
[/TD]
[TD] 0.0150
[/TD]
[TD] 0.0250
[/TD]
[TD] 0.0350
[/TD]
[TD] 0.0525
[/TD]
[TD] 0.0125
[/TD]
[TD] 0.0225
[/TD]
[TD] 0.0325
[/TD]
[TD] 0.0500
[/TD]
[TD] 0.0160
[/TD]
[TD] 0.0260
[/TD]
[TD] 0.0360
[/TD]
[TD] 0.0535
[/TD]
[/TR]
[TR]
[TD]Pete
[/TD]
[TD] 0.0150
[/TD]
[TD] 0.0250
[/TD]
[TD] 0.0350
[/TD]
[TD] 0.0525
[/TD]
[TD] 0.0125
[/TD]
[TD] 0.0225
[/TD]
[TD] 0.0325
[/TD]
[TD] 0.0500
[/TD]
[TD] 0.0160
[/TD]
[TD] 0.0260
[/TD]
[TD] 0.0360
[/TD]
[TD] 0.0535
[/TD]
[/TR]
[TR]
[TD]Fred
[/TD]
[TD] 0.0075
[/TD]
[TD] 0.0125
[/TD]
[TD] 0.0175
[/TD]
[TD] 0.0263
[/TD]
[TD] 0.0063
[/TD]
[TD] 0.0113
[/TD]
[TD] 0.0163
[/TD]
[TD] 0.0250
[/TD]
[TD] 0.0080
[/TD]
[TD] 0.0130
[/TD]
[TD] 0.0180
[/TD]
[TD] 0.0268
[/TD]
[/TR]
[TR]
[TD]Jillian
[/TD]
[TD] 0.0075
[/TD]
[TD] 0.0125
[/TD]
[TD] 0.0175
[/TD]
[TD] 0.0263
[/TD]
[TD] 0.0063
[/TD]
[TD] 0.0113
[/TD]
[TD] 0.0163
[/TD]
[TD] 0.0250
[/TD]
[TD] 0.0080
[/TD]
[TD] 0.0130
[/TD]
[TD] 0.0180
[/TD]
[TD] 0.0268
[/TD]
[/TR]
[TR]
[TD]Katie
[/TD]
[TD] 0.0075
[/TD]
[TD] 0.0125
[/TD]
[TD] 0.0175
[/TD]
[TD] 0.0263
[/TD]
[TD] 0.0063
[/TD]
[TD] 0.0113
[/TD]
[TD] 0.0163
[/TD]
[TD] 0.0250
[/TD]
[TD] 0.0080
[/TD]
[TD] 0.0130
[/TD]
[TD] 0.0180
[/TD]
[TD] 0.0268
[/TD]
[/TR]
[TR]
[TD]Teresa
[/TD]
[TD] 0.0075
[/TD]
[TD] 0.0125
[/TD]
[TD] 0.0175
[/TD]
[TD] 0.0263
[/TD]
[TD] 0.0063
[/TD]
[TD] 0.0113
[/TD]
[TD] 0.0163
[/TD]
[TD] 0.0250
[/TD]
[TD] 0.0080
[/TD]
[TD] 0.0130
[/TD]
[TD] 0.0180
[/TD]
[TD] 0.0268
[/TD]
[/TR]
</tbody>[/TABLE]

Please let me know if any of you have an idea for this! I would greatly appreciate it. Thank you.

 

[pvr928]

Hi snyderjames

With thanks to Chandoo try this approach.

Firstly, convert the two rows of State and Car type data into one, by use of the CONCATENATE or & function, so you have something like:

'NH_Compact', 'NH_Standard', etc. It doesn't matter what format you take, as long as you can replicate it.

In other words, you are taking the two rows and turning them into one row. Once you've done this, you now have a more typical table. My table now is:

Range B1:M1 - Header (containing 'NH_Compact', 'NH_Standard' etc)
Range A2:A9 - Names
Range B2:M9 - Commission rates

Next:

Name the ranges, being:

Header: 'State_Car'
Names: 'Names'
Commission rates: 'CommissionRates'

Then, using your Sales Data table, use the following formula to pull in the commission rate:

=SUMPRODUCT((Names=$O12)*(State_Car=$Q12&"_"&$P12)*CommissionRates)

Where $O12 is 'James', $Q12 is 'UT', and $P12 is 'SUV'

Result: 0.0325

Enjoy !

Cheers

pvr928

PS make sure your names are the same: you have 'Peter' as 'Pete' in your commission table. If the text does not match, nothing will be returned.

 

[snyderjames724]

Hello pvr928,

This works better than what I had in mind, thank you very much for your help!