VLOOKUP HELP

K

KevinM

I just took over a spreadsheet from a coworker and i noticed they are
trying to use a vlookup and it is returning the wrong value. I have
two values on a spreadsheet for 101.1% and 101.0% but when i do the
vlookup i get different numbers. Any help would be much appreciated

For 101.1% it will return the value of $5 which is correct.

For 101% it returns $0 which is incorrect as it should return back $5
too.


Here is the formula I am using
=VLOOKUP(101%,ColumnA,3,TRUE)

Table A
Column A Column B Column C
100% 100.99% $0
101% 101.99% $5
102% 102.99% $10
103% 103.99% $15
 
P

Pete_UK

I would suspect that the value of 101% is slightly lower - it might be
100.999999999999%. As such, it will match with 100% rather than 101%
in your table. Or it might be that the 101% in your table is actually
slightly higher - 101.000000001%.

Check these two out by increasing the number of decimal places.

Hope this helps.

Pete
 
E

Eduardo

Hi,
try
Supposing the value you want to Vlookup is in cell E1 enter the formula as
follow

=+VLOOKUP(E1,A2:C5,3,TRUE)
 
N

Niek Otten

Format ColumnA with many decimals and see if it's not actually slightly
smaller than 101%
Do you actually use the formula as shown or do you refer to a cell
containing the 101%?
 
K

KevinM

I refer back to a cell that contains the percentage and it can be in a
range 100% - 100.99% which is why i am using true and not false
 
K

KevinM

I just expanded my percentage and noticed it was 100.97% and not
101%. Sorry for not looking before, but that for all the help/
 
N

Niek Otten

Then it's prpbably a rounding issue. As advised, increase the number of
decimals to find the error or round both your table and the search argument
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Numbered list problem 6
Match 2 values 4
VLOOKUP returning #N/A result 2
Help me for exact Formula 3
Sort data 2
Mail Merge Help 1
VLookup Function HELP 7
VLOOKUP returning #N/A ?? 3

Top