Hlookup with Rounding

[wisemank]

Hi all,

I have a formula:

=IF(ISERROR(HLOOKUP(BD23,'Laser Tables'!C1:R2,2,FALSE)),"",(HLOOKUP(BD23,'Laser Tables'!C1:R2,2,FALSE)))

My issue is the data table for the lookup is in Tenths... So for example user inputs 2.40 into BD23 and the formula works great. If the user inputs 2.35 it fails because the lookup table doesn't contain the data into Hundredths. Since there is very little difference in the data in Hundredths vs. Tenths rounding up is a most desirable solution. So if the user inputs 2.35 into BD23 I need the equation to round up to 2.40. Any suggestions?

Thanks,

Kip

 

[Jonmo1]

Try

=IF(ISERROR(HLOOKUP(ROUNDUP(BD23,1),'Laser Tables'!C1:R2,2,FALSE)),"",(HLOOKUP(ROUNDUP(BD23,1),'Laser Tables'!C1:R2,2,FALSE)))

 

[AliGW]

Use TRUE instead of FALSE in the HLOOKUP formula for an approximate match and make sure that the lookup array is in ascending order from left to right.