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

 

[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