Range mapping in Excel

Question

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.

Solution 1

If you use Index/Match instead of Vlookup, you can keep the lookup table sorted descending like posted in your question. The formula is

=INDEX(B1:B10,MATCH(77,A1:A10,-1))

Match with -1 as the third parameter will return a value greater than or equal to the lookup value when the table is sorted descending.

Solution 2

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.