Vlookup and rounding

  • Thread starter Thread starter excelrookie
  • Start date Start date
E

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
 
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
Click to expand...

Try the CEILING formula.

Replace K15 with

=CEILING(K15,1000)

Hope this helps / Lars-Åke
 
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 :)
 
Hi,

Sort the range (assumed in C7:D10) 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
 
Back
Top