Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Vlookup Help
I cant figure out what I am doing wrong.
Why wont my lookup bring me back 2.7 ? This is what Im getting: If I do =VLOOKUP(a6*100,a1:b4,2) = 2.5 or if I do =VLOOKUP(a6*100,a1:b4,2,0) = #N/A a b 1 6.875 -2.375 2 7.000 -2.500 3 7.125 -2.750 4 7.250 -3.375 5 6 7.125% |
#2
|
|||
|
|||
You're probably getting very small rounding errors (See
http://cpearson.com/excel/rounding.htm for more). Try =VLOOKUP(ROUND(A6*100,3),A1:B4,2) In article , "ynissel" wrote: I cant figure out what I am doing wrong. Why wont my lookup bring me back 2.7 ? This is what Im getting: If I do =VLOOKUP(a6*100,a1:b4,2) = 2.5 or if I do =VLOOKUP(a6*100,a1:b4,2,0) = #N/A a b 1 6.875 -2.375 2 7.000 -2.500 3 7.125 -2.750 4 7.250 -3.375 5 6 7.125% |
#3
|
|||
|
|||
It's because 0 looks for an exact match and what you probably have is a
rounded displayed value where the underlying value is something different, you might want to try with =VLOOKUP(ROUND(A6*100,3),A1:B4,2,0) or even round the table values in case their underlying values are different Regards, Peo Sjoblom "ynissel" wrote: I cant figure out what I am doing wrong. Why wont my lookup bring me back 2.7 ? This is what Im getting: If I do =VLOOKUP(a6*100,a1:b4,2) = 2.5 or if I do =VLOOKUP(a6*100,a1:b4,2,0) = #N/A a b 1 6.875 -2.375 2 7.000 -2.500 3 7.125 -2.750 4 7.250 -3.375 5 6 7.125% |
#4
|
|||
|
|||
Nope - Its an exact number ! No rounding at all !
"Peo Sjoblom" wrote: It's because 0 looks for an exact match and what you probably have is a rounded displayed value where the underlying value is something different, you might want to try with =VLOOKUP(ROUND(A6*100,3),A1:B4,2,0) or even round the table values in case their underlying values are different Regards, Peo Sjoblom "ynissel" wrote: I cant figure out what I am doing wrong. Why wont my lookup bring me back 2.7 ? This is what Im getting: If I do =VLOOKUP(a6*100,a1:b4,2) = 2.5 or if I do =VLOOKUP(a6*100,a1:b4,2,0) = #N/A a b 1 6.875 -2.375 2 7.000 -2.500 3 7.125 -2.750 4 7.250 -3.375 5 6 7.125% |
#5
|
|||
|
|||
Did you try my formula?
Regards, Peo Sjoblom "ynissel" wrote: Nope - Its an exact number ! No rounding at all ! "Peo Sjoblom" wrote: It's because 0 looks for an exact match and what you probably have is a rounded displayed value where the underlying value is something different, you might want to try with =VLOOKUP(ROUND(A6*100,3),A1:B4,2,0) or even round the table values in case their underlying values are different Regards, Peo Sjoblom "ynissel" wrote: I cant figure out what I am doing wrong. Why wont my lookup bring me back 2.7 ? This is what Im getting: If I do =VLOOKUP(a6*100,a1:b4,2) = 2.5 or if I do =VLOOKUP(a6*100,a1:b4,2,0) = #N/A a b 1 6.875 -2.375 2 7.000 -2.500 3 7.125 -2.750 4 7.250 -3.375 5 6 7.125% |
#6
|
|||
|
|||
Its not a rounding error - but it is the multiplication. If I enterin 7.125
I get the correct results but if I enter in .07125*100 I dont - even though the result is exactly 7.125 ??? Help ! "JE McGimpsey" wrote: You're probably getting very small rounding errors (See http://cpearson.com/excel/rounding.htm for more). Try =VLOOKUP(ROUND(A6*100,3),A1:B4,2) In article , "ynissel" wrote: I cant figure out what I am doing wrong. Why wont my lookup bring me back 2.7 ? This is what Im getting: If I do =VLOOKUP(a6*100,a1:b4,2) = 2.5 or if I do =VLOOKUP(a6*100,a1:b4,2,0) = #N/A a b 1 6.875 -2.375 2 7.000 -2.500 3 7.125 -2.750 4 7.250 -3.375 5 6 7.125% |
#7
|
|||
|
|||
I just did and it worked but I really dont understand why. 7.125 is exacltly
the same as .07125*100. Thanks nonetheless ! "Peo Sjoblom" wrote: Did you try my formula? Regards, Peo Sjoblom "ynissel" wrote: Nope - Its an exact number ! No rounding at all ! "Peo Sjoblom" wrote: It's because 0 looks for an exact match and what you probably have is a rounded displayed value where the underlying value is something different, you might want to try with =VLOOKUP(ROUND(A6*100,3),A1:B4,2,0) or even round the table values in case their underlying values are different Regards, Peo Sjoblom "ynissel" wrote: I cant figure out what I am doing wrong. Why wont my lookup bring me back 2.7 ? This is what Im getting: If I do =VLOOKUP(a6*100,a1:b4,2) = 2.5 or if I do =VLOOKUP(a6*100,a1:b4,2,0) = #N/A a b 1 6.875 -2.375 2 7.000 -2.500 3 7.125 -2.750 4 7.250 -3.375 5 6 7.125% |
#8
|
|||
|
|||
It *is* an internal rounding error. See the reference I gave you.
You've got a solution. In article , "ynissel" wrote: Its not a rounding error - but it is the multiplication. If I enterin 7.125 I get the correct results but if I enter in .07125*100 I dont - even though the result is exactly 7.125 ??? Help ! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Have Vlookup return a Value of 0 instead of #N/A | Excel Worksheet Functions | |||
make a vlookup using a variable path | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |