# Range mapping in Excel

I have a column of data in Excel with numbers [0..100]. I need a new column, which maps these to a new set of values such that:

• > 90 : 4
• 80-90 : 3
• 70-79 : 2
• 60-69 : 1
• < 60 : 0

I attempted to do this with VLOOKUP, specifically with a test input of `77` and the function call `VLOOKUP(77, A10:B19, 2, TRUE)`, where A10 is the top-left element in this range:

```99   4
90   4
89   3
80   3
79   2
70   2
69   2
60   1
59   0
0    0
```

However, I am getting a result of `#N/A`. According to the documentation, this is because

...the value in the lookup_value is smaller than the smallest value in the first column of the table_array...

However that's clearly not the case, as I'm passing 77. However, if I use a test input of `79`, a value that's explicitly in the lookup table, I get a correct result.

Is VLOOKUP the wrong approach here? What's the best way to do this sort of mapping?

I should mention that both my input value and lookup table are typed as Number.

``````=INDEX(B1:B10,MATCH(77,A1:A10,-1))
After digging in to this for a long time, I learned that the lookup table must be sorted ascending. The documentation notes that it must be sorted, but did not specify the sort order. Anything besides ascending order results in the `#N/A` error.