vlookup and rounding

H

hmz

I have a simple question, hopefullly.

I am new to the vlook up. I did a simple table:

Reference celll: 10.06%

A B C D
10.00%
10.10%

my formula is this: ==(VLOOKUP(10.06%,A1:D2,4))

I want my answer to deliver the result "28600." However, the formula is
rounding the 10.06% down to 10% and delivering a result of "2860" In other
words, anything above 10% is the next row.

Can someone tell me how to fix this rounding problem?

Thanks so much in advance.
 
T

T. Valko

You haven't provided enough info to make a suggest.

The way you have the formula written is why it's doing what it's doing.

VLOOKUP(10.06%,A1:D2,4)

You've left out the [range_lookup] argument so it defaults to TRUE which
means if there isn't an exact match of the lookup_value then the formula
matches the closest value that is less than the lookup_value. So:

Lookup 10.06

10.00
10.10

There is no exact match of 10.06 and 10.00 is the closest match that is less
than 10.06.

So, what do you want to happen when there isn't an exact match?
 
T

teylyn

Hi, you can try

=VLOOKUP(ROUNDUP(10.06%,1),A1:D2,4,FALSE)

This will round up the value to the nearest digit, so it is 10.1. Vlookup
then finds the exact match in your lookup table due to the FALSE operator.

does that help?
 
H

hmz

Thank you so much. All I needed to do was add "true" as the range look up,
and it works perfectly!
--
hmz
NYC


T. Valko said:
You haven't provided enough info to make a suggest.

The way you have the formula written is why it's doing what it's doing.

VLOOKUP(10.06%,A1:D2,4)

You've left out the [range_lookup] argument so it defaults to TRUE which
means if there isn't an exact match of the lookup_value then the formula
matches the closest value that is less than the lookup_value. So:

Lookup 10.06

10.00
10.10

There is no exact match of 10.06 and 10.00 is the closest match that is less
than 10.06.

So, what do you want to happen when there isn't an exact match?

--
Biff
Microsoft Excel MVP


hmz said:
I have a simple question, hopefullly.

I am new to the vlook up. I did a simple table:

Reference celll: 10.06%

A B C D
10.00%
10.10%

my formula is this: ==(VLOOKUP(10.06%,A1:D2,4))

I want my answer to deliver the result "28600." However, the formula is
rounding the 10.06% down to 10% and delivering a result of "2860" In
other
words, anything above 10% is the next row.

Can someone tell me how to fix this rounding problem?

Thanks so much in advance.


.
 
T

T. Valko

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


hmz said:
Thank you so much. All I needed to do was add "true" as the range look up,
and it works perfectly!
--
hmz
NYC


T. Valko said:
You haven't provided enough info to make a suggest.

The way you have the formula written is why it's doing what it's doing.

VLOOKUP(10.06%,A1:D2,4)

You've left out the [range_lookup] argument so it defaults to TRUE which
means if there isn't an exact match of the lookup_value then the formula
matches the closest value that is less than the lookup_value. So:

Lookup 10.06

10.00
10.10

There is no exact match of 10.06 and 10.00 is the closest match that is
less
than 10.06.

So, what do you want to happen when there isn't an exact match?

--
Biff
Microsoft Excel MVP


hmz said:
I have a simple question, hopefullly.

I am new to the vlook up. I did a simple table:

Reference celll: 10.06%

A B C D
10.00%
10.10%

my formula is this: ==(VLOOKUP(10.06%,A1:D2,4))

I want my answer to deliver the result "28600." However, the formula is
rounding the 10.06% down to 10% and delivering a result of "2860" In
other
words, anything above 10% is the next row.

Can someone tell me how to fix this rounding problem?

Thanks so much in advance.


.
 

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

Top