Index/match formula not working

jocote46

Board Regular
Joined
May 11, 2009
Messages
60
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.

Excel Formula:
[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 overRange 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
 

Attachments

  • Sample.jpg
    Sample.jpg
    76.3 KB · Views: 11
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:
Excel Formula:
=INDEX($V$2:$AA$7, MATCH(C2,$C$2:$C$7, 0), MATCH(Y29, $V$1:$AA$1, 1))
 
Upvote 0
You have text values in V1:AA1, so you can't do a "<="comparison with Y29.

Change your headers to:

VWXYZAA
10%100%103%105%110%115%
25.50%6.05%6.33%6.60%6.88%7.15%
35.50%6.05%6.33%6.60%6.88%7.15%
44.25%4.68%4.89%5.10%5.31%5.53%
51.00%1.00%1.00%1.00%1.00%1.00%
64.25%4.68%4.89%5.10%5.31%5.53%
70.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))
 
Upvote 0
Solution
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!
 
Upvote 0
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 description0% 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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,226,771
Messages
6,192,926
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top