Forum Discussion
Solved
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...
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.
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.