6

I used VLOOKUP function to find a value in an array but some of the values gave #N/A answer despite of available in array.

To round up the numbers, I used CEILING function but interesting point is in some values, it did not work.

I checked the type of value if it is number or not.

Also, I used ROUNDUP function but did not work.

Also, I tried INDEX/MATCH combination and again did not work.

In the example that I gave in the link, when I type between 15.00 - 15.20, it gives error but trying other values, it works.

How do I fix this?

Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
render ly
  • 63
  • 5

3 Answers3

5

This seems to be a bug with VLOOKUP and MATCH using the return values of CEILING. If you use:

=VLOOKUP(ROUND(CEILING(F4,0.1),1),A:B,2,FALSE)

then it works as expected.

If we look at this with VBA then we see what happens. To blame should be really CEILING and ROUNDUP. See example:

Sub testCeilingAndRoundup()

 Dim v As Double, test As Boolean, diff As Double

 v = [CEILING(15.1,0.1)] '15.1
 test = (v = 15.1) 'FALSE
 diff = 15.1 - v '-1.776...E-15

 v = [ROUNDUP(15.25,1)] '15.3
 test = (v = 15.3) 'FALSE
 diff = 15.3 - v '1.776...E-15

End Sub
Axel Richter
  • 56,077
  • 6
  • 60
  • 87
5

Looks like you've run into an Excel bug.

Applying CEILING to the number 15.1 should return the exact same result (15.1) regardless of whether the significance is 0.1, 0.01, 0.001, etc.

And indeed it does, according to Excel: when asked if they're equal, the answer is always TRUE.

But looking up these mathematically equal numbers in the look-up table gives different results.

enter image description here

This has to be a bug.

Instead of CEILING(F4,0.1), I suggest you use ROUNDUP(F4,1) which appears to be bug-free. Nope, ROUNDUP is buggy as well. Axel Richter's answer suggests wrapping CEILING in a ROUND and this does appear to make the problem go away. You can also convert to string and back to number:

VALUE(TEXT(ROUNDUP(F4,1),"0.0"))

so you'd have

=VLOOKUP(VALUE(TEXT(ROUNDUP(F4,1),"0.0")),A:B,2,FALSE)
Community
  • 1
  • 1
Jean-François Corbett
  • 37,420
  • 30
  • 139
  • 188
0

Your CEILING function should be more precise if you want to find a match for 15.10

Change it to CEILING(F4,0.01) and it will works :)

Sim1
  • 534
  • 4
  • 24
  • This fails if the input is e.g. 15.09. The intent is clearly to round this up to 15.10 and return 95, but your formula gives an error. – Jean-François Corbett Mar 25 '15 at 09:39
  • Also, I don't understand *why* `CEILING(15.10,0.1)` and `CEILING(15.10,0.01)` would behave differently (though testing shows that they do). Both should in principle return the exact same number i.e. 15.1. So I don't understand why you suggested this answer! – Jean-François Corbett Mar 25 '15 at 09:43
  • `CEILING(15.10, 0.1)` and `CEILING(15.10, 0,01)` are the same. There is no difference. – render ly Mar 25 '15 at 09:48