VLOOKUP does not find value in array

Question

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

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

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

In addition, I used the ROUNDUP function, but did not work.

In addition, I tried the INDEX / MATCH combination and did not work again.

In the example that I gave in the link, when I print between 15.00 - 15.20, it gives an error, but tries to use other values, it works.

How to fix it?

Answer 1

This seems to be a bug with VLOOKUP and MATCH using CEILING return values. 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 will see what happens. The wine must really be CEILING and ROUNDUP . 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 

Answer 2

It looks like you have encountered an Excel error.

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

And indeed, according to Excel: when they are asked if they are equal, the answer is always TRUE .

But searching for these mathematically equal numbers in the lookup table gives different results.

This must be a mistake.

Instead of CEILING(F4,0.1) , I suggest you use ROUNDUP(F4,1) , which seems to be error free. No, ROUNDUP also buggy. Axel Richter's answer suggests wrapping CEILING in ROUND , and this seems 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 have

 =VLOOKUP(VALUE(TEXT(ROUNDUP(F4,1),"0.0")),A:B,2,FALSE) 

Answer 3

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

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