Change the physical value of a cell instead of visual with roundup function

**MWI** · 03-01-2013, 10:57 AM

Hello!

I want to round up a value and use this value in vlookup function. When excel reads the cell he uses the underlying value.

Example: 300/7 = 42,85714286
H8=ROUNDUP(H7;2) = 42.86

table
42.85 X
42.86 Y

=vlookup(H8;"range";1;false) will return X and not Y

I've tried trunc() as well, but doesn't work

Can anyone help me with this problem?

**Ace_XL** · 03-01-2013, 11:01 AM

Make sure the range (table) for vlookup is correct

=vlookup(ROUNDUP(H7;2);$A$1:$B$2;2;false)

where A1:B2 is the table range

**MWI** · 03-01-2013, 12:10 PM

thx for the answer but that's not the problem, the vertical lookup function works perfectly,
disadvantage in this function is that if it doesnt find the exact value it will use the value beneath

I want excel to use 42.8600 as reference value and not 42.857... because the last one will return the value corresponding to 42.85

**Pepe Le Mokko** · 03-01-2013, 12:34 PM

The solution provided in post #2 seems to do what you want. Can you post a sample sheet with what you want to achieve if it is not correct?
roundup.xlsx