VLOOKUP with ROUNDUP between 0.7 and 0.8 will not work

[ian0411]

I have an interesting question regarding VLOOKUP and ROUNDUP functions use together for numbers between 0.7 and 0.8. As you can see the attached image, numbers greater than 0.7 but less than 0.8 will not work with the ROUNDUP or TRUNC (you can ignore the outcomes for 0.6, 0.7 and 0.8 since this is for testing) functions. It worked for CEILING but CEILING won't work for other numbers. Of course everything would work if I hard-coded those numbers into VLOOKUP directly. I also tried with INDEX/MATCH or made all the formulas listed there as array formulas but they come out the same as regular VLOOKUP. This is Excel 2007 and I haven't tried on other versions yet. Can anyone take a look and let me know if this is a bug that MS should fix or if there are any other ways that I can cover all these numbers and get the results? Thank you so much.

 

[wideboydixon]

Is that the real range/rate table? If so, why not:

=0.015+(ROUNDUP(D2,1)*0.025)

Strange problem though and it happens for me. I was able to use the following:

=VLOOKUP(INT(ROUNDUP(D2,1)*10)/10,$A$2:$B$12,2,0)

Rounding schmounding.

WBD

 

[Marcelo Branco]

Disregard

 

[Marcelo Branco]

The values in A2:A12 are hard coded or results of a formula?

M.

 

[ian0411]

The values in A2:A12 are hard coded. Column B is also hard coded.

 

[Kyle123]

It's a floating point issue, I suspect that the int approach above is probably the best option to resolve. Depending on your use case, you could set the "set precision as displayed flag" and use a helper column with the 0.8

 

[ian0411]

This worked but isn't this really odd. Totally forgot the INT function. Let's see if any other people come up with other solutions.

Is that the real range/rate table? If so, why not:

=0.015+(ROUNDUP(D2,1)*0.025)

Strange problem though and it happens for me. I was able to use the following:

=VLOOKUP(INT(ROUNDUP(D2,1)*10)/10,$A$2:$B$12,2,0)

Rounding schmounding.

WBD

 

[Marcelo Branco]

The values in A2:A12 are hard coded. Column B is also hard coded.

So the formula suggested by WBD should work.
Another
=VLOOKUP(TRUNC(ROUNDUP(D2,1),2),$A$2:$B$12,2,0)

M.

 

[ian0411]

Wow, this worked too and looks cleaner. Thank you so much.

So the formula suggested by WBD should work.
Another
=VLOOKUP(TRUNC(ROUNDUP(D2,1),2),$A$2:$B$12,2,0)

M.