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

Attached Files

help 1.xlsx (11.6 KB, 10 views)

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

Attached Files

Copy of help 1.xlsx (11.6 KB, 20 views)

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

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.

You're welcome

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

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.

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

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

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...

Attached Files

Copy of Copy of help 1.xlsx (11.7 KB, 10 views)

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))

__________________
Using O365 v2503 - Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

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