help with index or lookup function

[zhl203]

Hello,
Can someone help me with index function for the below situation?

Criteria
Ratio Rating
15% 1
20% 2
25% 3
30% 4
35% 5
40% 6
45% 7
50% 8

Actual:
Ratio Rating
16% *

*What is the index function that can return rating of 2 for ratio of 16%?

Basically, I want a function that can do the following
if the ratio is <15%, return 1
if the ratio is >=15% but <20%, return 2
if the ratio is >=20% but <25%, return 3
and so on…

Any help would be greatly appreciated.

[etaf]

can you sort the list the other way round ?
then use index/match with -1

[Pete_UK]

Try it like this:

=IFERROR(VLOOKUP(A15,A$2:B$10,2,0),INDEX(B$2:B$10,MATCH(A15,A$2:A$10)+1))

Hope this helps.

Pete

[canapone]

Hi All,

in D2 the ratio.


=MAX(1,INT((D2-0.05)/0.05))


Hope it helps