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%

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%

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%

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%

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%

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%

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%

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 !