Vlookup and rounding

[excelrookie]

1,000 $2
2,000 $2.50
3,000 $2.75
4,000 $2.89

When I am looking for 2,500 it is giving me 2.50 and I need it to return 2.75

By formula is =VLOOKUP(K15,Information!AE2:AF1001,2) Where K15 is the
"2,500". How can I make K15 round up to the next thousand so I will get 2.75?

TIA

 

[Lars-Åke Aspelin]

1,000 $2
2,000 $2.50
3,000 $2.75
4,000 $2.89

When I am looking for 2,500 it is giving me 2.50 and I need it to return 2.75

By formula is =VLOOKUP(K15,Information!AE2:AF1001,2) Where K15 is the
"2,500". How can I make K15 round up to the next thousand so I will get 2.75?

TIA

Try the CEILING formula.

Replace K15 with

=CEILING(K15,1000)

Hope this helps / Lars-Åke

 

[Joe User]

By formula is =VLOOKUP(K15,Information!AE2:AF1001,2)
Where K15 is the "2,500". How can I make K15 round up
to the next thousand so I will get 2.75?

Ostensibly:

=VLOOKUP(ROUNDUP(K15,-3),Information!AE2:AF1001,2)

But perhaps your table could be constructed differently, to wit:

0 $2
1000 $2.50
2000 $2.75
3000 $2.89

If you want a lookup of 1000 to return $2 instead of $2.50, change 1000 in
the table to something appropriate. "Something appropriate" depends on the
precision that you need. For example, if the lookup value is an integer,
then:

0 $2
1001 $2.50
2001 $2.75
3001 $2.89

If the lookup value has a precision of 2 decimal places, then:

0 $2
1000.01 $2.50
2000.01 $2.75
3000.01 $2.89

Each of these solutions, including your original method, has it pros and cons.


----- original message -----

 

[excelrookie]

Thanks Joe, The first one did it!! I didn't create the table I am using and
it has 1,000's of entries so I would prefer not to change them all

 

[Ashish Mathur]

Hi,

Sort the range (assumed in C710) in descending order of column C. Cell
C14 has 2500. You could then try this formula.

=INDEX($C$7:$D$10,MATCH(C14,C7:C10,-1),2)

There are more formula based approached but this is a simple one

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com