unable to get around using index and match to find with range of numbers

[ozmozmozm]

Hi Experts, I am not sure if my title explains, basically, i am using the index and match to find rates for my chart, however unable to get around using index and match to find with range of numbers.

for example:


i have 4 groupings and a range of numbers <1000, <1500, <2000 etc

and under them are rates of how much it will be.

if using index and match i am able to do specific numbers but i am not about to put in it range...
i would like to be able to change the group and amount(of any range) and able to produce the results.


Thank you very much if you are about to help point me or guide me to solve the problems.

Best regards

[Pecoflyer]

I had to tweak the numbers in row 3 to make them real numbers formatted as currency ( without the < sign)
See if attached helps

[ozmozmozm]

THANK YOU VERY MUCH!!! PECOFLYER!!

Thanks... and i understood what you did there.... i tried playing around the "LEFT" and "RIGHT" and i understood...

THanks alot.

[Pecoflyer]

You're welcome

[ozmozmozm]

Hi, i would like to check why i am not able to change the "A,B,C,D" to any names?
is there are reason for that? i tried to change to Team 1, Team 2, Team 3, team 4. is it possible to do that?
what i meant is, after i changed the grouping names to etc "Team 1" and input "Team 1" to search, it shows error.

[Pecoflyer]

You have to adapt the lookup value in cells C11 15 and 19

[ozmozmozm]

Hi pecoflyer, I should be clearer in the issue i had encountered.
i did what you said, but doesn't seems to be able to do that.

when the grouping are "A,B,C,D" when i put any of them to lookup in C11, it works,
but when i change it to "Team 1, Team 2, Team 3, team 4" and i put the indentical input to C11. it doesn't work...

[Pecoflyer]

As the lookup values now are identical, the LEFT function can be dropped
=INDEX($B$4:$AF$7,MATCH($C11,$A$4:$A$7,0),MATCH($C 12,$B$3:$AF$3,1))

[ozmozmozm]

icici thanks pecoflyer!! that fix it... thank you very much