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

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

On Jan 12, 1:27*pm, KevinM wrote:
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

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

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

"KevinM" wrote:

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

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%?

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"KevinM" wrote in message
...
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

try this
=VLOOKUP(101%,ColumnA,3,FALSE)
--
Thanks
Suleman Peerzade


"KevinM" wrote:

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

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

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/

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"KevinM" wrote in message
...
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