Return a value from a range where the lookup value is between 2 values

[elronino]

Alright, first time poster.... long time reader. With that said, I have mooched long enough and figured I'd join and try and contribute.

So my problem, I'm a cyclist and am trying to create a workbook to document my Fatigue Profile throughout the season. At a high level, a Fatigue Profile will give you a rating based on percentage of wattage decrease between 2 defined time lengths.

so my workbook has 2 worksheets, the 'Fatigue Profile' and the 'Fatigue Profile Rating Ranges'. I am all the way up to the point of pulling in the rating based on the percentage of decrease, but cannot seem to get the formula to work. I've tried:

1. VLOOKUP with a TRUE indicator
2. SUMPRODUCT
3. INDEX & MATCH

As of now, I haven't been able to successfully grab that rating value. Essentially my 'Fatigue Profile Rating Ranges' table looks like this:

A B C

1 10s Low	10s High	Rating_1
2 0.41	0.55	Well Below Average(Very High Explosiveness)
3 0.31	0.40	Below Average(High Explosiveness)
4 0.22	0.30	Average
5 0.15	0.21	Above Average(Strong Endurance)
6 0.05	0.14	Well Above Average(Tremendous Endurance)

<colgroup><col><col><col></colgroup><tbody>
</tbody>

I have a value on the Fatigue Profile worksheet of .11. So, naturally I could just *Look* a the Rating Ranges and fill it in manually, but what is the fun in that. Everything I have tried either returns a #N/A or #VALUE... all I want it to return is the "Well Above Average( Tremendous Endurance)".

Any help, thoughts, solutions, sanity checks or "Dang... what weirdo" comments are appreciated.

 

[BobUmlas]

=INDEX(C2:C6,MATCH(yourvalue,B2:B6,-1))

since the values are descending, you need the -1 in the MATCH

 

[NBVC]

Assuming data in A2:C6, try:

=INDEX(C2:C6,MATCH(TRUE,INDEX(A2:A6<=E3,0),0))

where E3 contains the lookup criteria (0.11)


Or if you sort data by first column ascending order, you can use:

=LOOKUP(E3,A2:C6)

 

[elronino]

Assuming data in A2:C6, try:

=INDEX(C2:C6,MATCH(TRUE,INDEX(A2:A6<=E3,0),0))

where E3 contains the lookup criteria (0.11)


Or if you sort data by first column ascending order, you can use:

=LOOKUP(E3,A2:C6)

Awesome! the =INDEX(C2:C6,MATCH(TRUE,INDEX(A2:A6<=E3,0),0)) worked beautifully. I think I was trying to do too much when using the INDEX, MATCH by qualifying both the upper and lower bounds.

Thanks so much for your help