Forum Discussion
V lookup #NA
You're using VLOOKUP seeking an absolute match; you want the "range match" and then your table can be a LOT smaller.
0 Less than 50% 0.51 51-60% 0.61 61-70% 0.71 71-80% 0.81 Above 80% 1 Above 80% And your formula in your column B changes ever so slightly:
=VLOOKUP(A2,$K$6:$L$11,2,1)
$K$6:$L$11 is where I put it to check it: you can move it anywhere and get rid of your original table. Here's a link where you can learn more of the nuances of VLOOKUP.
When you make the change suggested by HansVogelaar , you should also change the lookup array range (second parameter) to exclude the column titles. Namely:
=VLOOKUP(A2,$F$2:$G$102,2)
-----
And when you make that change, you can simplify the table as follows:
Thus, the VLOOKUP formula becomes:
=VLOOKUP(A2,$F$2:$G$6,2)
And note the correction for 0-50%: Less than 51%, not Less than 50%.
-----
Moreover, to ensure WYSIWYG results, you might want to explicitly round the first parameter for VLOOKUP. For example:
=VLOOKUP(ROUND(A2,4),$F$2:$G$6,2)
That rounds to 2 percentage decimal places because 12.34% is the decimal number 0.1234.
Explicit rounding is not necessary for your example because the percentages in column A are constants.
But if they are actually calculated in your Excel file, explicit rounding "corrects" the situation where what appears to be 51.00% returns "Less than 51%" because the actual value is between 50.995% and 50.9999999999999%.