vlookup and rounding

[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%,A12,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. 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%,A12,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?

 

[teylyn]

Hi, you can try

=VLOOKUP(ROUNDUP(10.06%,1),A12,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?

 

[hmz]

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

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%,A12,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

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%,A12,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. Valko]

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

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

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%,A12,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

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%,A12,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.

.