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:

Fname	Type	State	Amount	Commission Rate 1
James	SUV	UT	38,968.00
Chris	Standard	PA	24,305.00
Matt	Standard	PA	22,060.00
Peter	Compact	UT	13,311.00
Fred	Compact	PA	17,097.00

<tbody>
</tbody>

Commission Rates:

	NH	NH	NH	NH	UT	UT	UT	UT	PA	PA	PA	PA
	Compact	Standard	SUV	Luxury	Compact	Standard	SUV	Luxury	Compact	Standard	SUV	Luxury
James	0.0150	0.0250	0.0350	0.0525	0.0125	0.0225	0.0325	0.0500	0.0160	0.0260	0.0360	0.0535
Chris	0.0150	0.0250	0.0350	0.0525	0.0125	0.0225	0.0325	0.0500	0.0160	0.0260	0.0360	0.0535
Matt	0.0150	0.0250	0.0350	0.0525	0.0125	0.0225	0.0325	0.0500	0.0160	0.0260	0.0360	0.0535
Pete	0.0150	0.0250	0.0350	0.0525	0.0125	0.0225	0.0325	0.0500	0.0160	0.0260	0.0360	0.0535
Fred	0.0075	0.0125	0.0175	0.0263	0.0063	0.0113	0.0163	0.0250	0.0080	0.0130	0.0180	0.0268
Jillian	0.0075	0.0125	0.0175	0.0263	0.0063	0.0113	0.0163	0.0250	0.0080	0.0130	0.0180	0.0268
Katie	0.0075	0.0125	0.0175	0.0263	0.0063	0.0113	0.0163	0.0250	0.0080	0.0130	0.0180	0.0268
Teresa	0.0075	0.0125	0.0175	0.0263	0.0063	0.0113	0.0163	0.0250	0.0080	0.0130	0.0180	0.0268

<tbody>
</tbody>

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!