Lookup multiple customers and apply their respective slabs

[amandeep08]

Hi All,

I have 100+ customers and each one is having different commission slabs. I want the formula which will match the customer name and apply the respective slabs on the sales done by them.

I have tried multiple formulas but not able to get success. Apart from this, i have tried putting the slabs in a cell without equal sign and tried to ccalculate the same basis the lookup value but it also not worked. Pl help me

 

[KRice]

For the formula version I posted, here is how it would be adapted to the revised table structure. Check the values to confirm that the results are as expected. I see that you have changed the tier limits so that one ends where the next begins. So the formula could be simplified a little bit by using the start slab values, but in this version, I've left the method unchanged...to shift the end slab index by 1 and insert a 0 for the first tier...and then use that array as the start slab (and it would look just like your current start slabs).

Edit: The simplified version is also shown, where we use the Start Slab values directly since there is no discontinuity between tiers. Also, I noted an error in the slab table (see green cell with red font).

MrExcel_20240301.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Customer Name	Customer Category	Sales	Commission				Customer Name	Customer Category	Start Slab	End Slab	% Margin
2	A	Reseller	825000	7525.00				A	Reseller	0	500000	0.01
3	B	Corporate	1225000	11887.50				A	Reseller	500000	750000	0.008
4	C	Employee	735000	9100.00				A	Reseller	750000	1000000	0.007
5								A	Reseller	1000000		0.006
6								B	Corporate	0	1000000	0.01
7	A	Reseller	825000	7525.00				B	Corporate	1000000	1200000	0.0085
8	B	Corporate	1225000	11887.50				B	Employee	1200000		0.0075
9	C	Employee	735000	9100.00				C	Employee	0	200000	0.015
10								C	Employee	200000	500000	0.0125
11								C	Employee	500000	1000000	0.01
12								C	Employee	1000000		0.0075
Sheet7

Cell Formulas
Range	Formula
D2:D4	D2	=LET(slab,$H$2:$L$12,cslab,FILTER(slab,CHOOSECOLS(slab,1)=A2),csslab,VSTACK(0,DROP(CHOOSECOLS(cslab,4),-1)),ccom,CHOOSECOLS(cslab,5),ccom_shft,VSTACK(0,DROP(ccom,-1)),res, SUMPRODUCT(--(C2>csslab),(C2-csslab),(ccom-ccom_shft)), res)
D7:D9	D7	=LET(slab,$H$2:$L$12,cslab,FILTER(slab,CHOOSECOLS(slab,1)=A7),csslab,CHOOSECOLS(cslab,3),ccom,CHOOSECOLS(cslab,5),ccom_shft,VSTACK(0,DROP(ccom,-1)),res, SUMPRODUCT(--(C7>csslab),(C7-csslab),(ccom-ccom_shft)), res)

 

[Dave Patton]

The suggestions posted previously still may be relevant.
To make the data entry easier, I included the Lambda version which requires Excel 365.
The first formula is on one row and the information is derived from the Rate Table but doesn't require the Table.
Provide the function with a user friendly name. The function indicates the required parameters.

Commissions 2024.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2	A	Reseller	825,000	7,525.00	7,525.00		0	500,000	750,000	1,000,000			0.01	-0.002	-0.001	-0		Customer Name	Customer Category	Start Slab	% Margin
3																		A	Reseller	0	1.00%
4																		A	Reseller	500,000	0.80%
5																		A	Reseller	750,000	0.70%
6																		A	Reseller	1,000,000	0.60%
7																		B	Corporate	0	1.00%
8																		B	Corporate	1,000,000	0.85%
9																		B	Employee	1,200,000	0.75%
10																		C	Employee	0	1.50%
11																		C	Employee	200,000	1.25%
12																		C	Employee	500,000	1.00%
13																		C	Employee	1,000,000	0.75%
14
1h

Cell Formulas
Range	Formula
D2	D2	=SUM((C2>G2:K2)*(C2-G2:K2)*(M2:Q2))
E2	E2	=Commission_(A2,$R$2:$U$13,C2)

Lambda Functions
Name	Formula
Commission_	=LAMBDA(CustomerName,rngRateInformation,Sales,LET(n,CustomerName,s,Sales,slab,rngRateInformation,cslab,FILTER(slab,CHOOSECOLS(slab,1)=n),csslab,CHOOSECOLS(cslab,3),ccom,CHOOSECOLS(cslab,4),ccom_shft,VSTACK(0,DROP(ccom,-1)),SUM((s>csslab)*(s-csslab)*(ccom-ccom_shft))))