Forum Discussion
V lookup #NA
PFA data in which if the output is in decimal i.e A24 ; then B24 should automatically round off and calculate as per vlook up and should not result in #NA.
OR
please let me know the simplified way to place vlookup correctly making changes to the array as the G column mostly contains same value.
#v_lookup functions
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.
8 Replies
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%.
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.
- it was helpful but round up is not fit for my results because if i consider 50.22% as a value in A24 than my bucket is getting changed to 51-60% instead i want less than 50%
To be fair to OliverScheurich your original post asked for the value to be rounded off. Here's the quote:
PFA data in which if the output is in decimal i.e A24 ; then B24 should automatically round off and calculate as per vlook up and should not result in #NA.
But for your own learning, become aware of the fact that VLOOKUP--as a computer function--is going to be very precise, so when you're dealing with boundaries--as you are doing here--you need to be sure that the boundaries you draw are at the level of precision you need, and that they take into account all possible results in the way that you want them. And accuracy also matters. In that context, look at your last statement
if i consider 50.22% as a value in A24 than my bucket is getting changed to 51-60% instead i want less than 50%
But 50.22% is NOT less than 50%. Granted, it's not between 51 and 60% either.
So it's up to you, first, to know how VLOOKUP works (in this case), and then, second, to design your reference table so that it gives you the result you want, especially with numbers like 50.22%, numbers that hover on the boundary.
