Index/match formula not working

[jocote46]

Hello Fellow Excel users,
So every month, i have a commission database that calculates revenue amount/percentage and we i have different commission tables for different groups. for the most part this formula works except when the percentages go above 100%,

I am in need of your expertise, i am using an index match formula that is not giving me the expected result.

[TABLE width="460pt"] [TR] [td width="613px"]=INDEX($V$2:$AA$7, MATCH(C2,$C$2:$C$7, 0), MATCH(TRUE,Y29 <= $V$1:$AA$1, 0))[/td] [/TR] [/TABLE]

the formula is supposed to look at a value in Y29 =124% (Revenue Percentage) and return a commission percentage of 7.150% based on the following table:

0% to 100%	100% to 103%	103% to 105%	105% to 110%	110% to 115%	115% and over	Range V1:AA1
5.50%	6.050%	6.325%	6.600%	6.875%	7.150%	Range V2:AA7
5.50%	6.050%	6.325%	6.600%	6.875%	7.150%	Range V2:AA7
4.25%	4.675%	4.888%	5.100%	5.313%	5.525%	Range V2:AA7
1.00%	1.00%	1.00%	1.00%	1.00%	1.00%	Range V2:AA7
4.25%	4.680%	4.890%	5.100%	5.310%	5.530%	Range V2:AA7
0.21%	0.210%	0.210%	0.210%	0.210%	0.210%	Range V2:AA7

However the formula returns a value of 5.50%.

I am attaching a screenshot(sample). thank you in advance for your time, expertise in finding a solution. i have tried to consult with AI to no avail. help! let me know if you have any questions in case more explanations are needed.
Damian

 

[Cubist]

You're trying to match a number with string headers. They need to be the same type so first change the values in $V$1:$AA$1 the lower limits only i.e. 0%, 110%, 103%, ect... so that they're numbers not a string, then try:

=INDEX($V$2:$AA$7, MATCH(C2,$C$2:$C$7, 0), MATCH(Y29, $V$1:$AA$1, 1))

 

[StephenCrump]

You have text values in V1:AA1, so you can't do a "<="comparison with Y29.

Change your headers to:

	V	W	X	Y	Z	AA
1	0%	100%	103%	105%	110%	115%
2	5.50%	6.05%	6.33%	6.60%	6.88%	7.15%
3	5.50%	6.05%	6.33%	6.60%	6.88%	7.15%
4	4.25%	4.68%	4.89%	5.10%	5.31%	5.53%
5	1.00%	1.00%	1.00%	1.00%	1.00%	1.00%
6	4.25%	4.68%	4.89%	5.10%	5.31%	5.53%
7	0.21%	0.21%	0.21%	0.21%	0.21%	0.21%
Sheet1

(I'm not sure what happens at the boundaries - I've assumed 100% --> 6.05%, rather than 5.50%, for example).

Then your formula can become: =INDEX(V$2:AA$7, MATCH(C2,C$2:C$7,0),MATCH(Y29,V$1:AA$1,1))

Or more succinctly: =INDEX(V2:AA2,MATCH(Y29,V$1:AA$1,1))

 

[jocote46]

Stephen,

it worked once a change the text headers to numbers and use the values suggested, i should change the boundaries values to 101% instead of 100% pm the second column (W) etc. i have 4 different tables so i will adjust accordingly. appreciate your time and expertise. thank you so much!

 

[StephenCrump]

i should change the boundaries values to 101%

I'm not sure that's going to help. The question is where the boundary value 100% falls. (And then what about 100.1% etc)

Your description	0% to 100%	100% to 103%
Do you mean?	<=100%	>100%, <=103%
or perhaps?	<100%	>=100%, <103%

@Cubist and I both assumed the latter in our formulae.

 

[jocote46]

Stephen, thanks for following up on this, i did change the boundaries and what i actually did was that i change the format of the brackets. on the first bracket, i changed it to 100%, then 101%,104% etc. the formula will look at the values on each field instead of a range which why it wasn't working in the first place.