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.
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.
- correct ! Thanks..!