VLOOKUP not interpolating all cells?

Question

My VLOOKUP formula is not interpolating all cells

    AD           AE   AF
11-6-2014 15:20 90,4 #N/A
11-6-2014 15:21 89,1 #N/A
11-6-2014 15:22 90,4 Good
11-6-2014 15:23 89,1 Good

AF column is my formula: (in dutch) =AE4-(VERT.ZOEKEN(AD4;$AL$3:$AM$39714;2;WAAR)-1000) (tried translation in english) =AE4-(VLOOKUP(AD4;$AL$3:$AM$39714;2;True)-1000)

This is part of the matrix column:

        AL        AM
11-6-2014 15:22 1026,9
11-6-2014 15:23 1027,3
11-6-2014 15:24 1027,2
11-6-2014 15:25 1026,9

My data (1026.9) at 11-6-2014 15:22 gets found. But i want this value to be interpolated for 11-6-2014 15:20 and 11-6-2014 15:21 as well. Afaik it should just lookup the closest date and output the value in the matrix, but it does not.

Any ideas?

Answer 1

That is the way VLOOKUP is designed to work when the Range Lookup parameter is set to TRUE. It will return the value for the nearest value less than or equal to the lookup value. If no value meets that criterion, an error is returned.

A couple of options for you:

1. Set an exception for values below the lower bound and let VLOOKUP handle the rest. To do this, just wrap your function in an IFERROR function.

   =IFERROR(AE4-(VLOOKUP(AD4;$AL$3:$AM$39714;2;True)-1000);AE4-$AM$3)
   

   This just defaults all error-producing lookups to the first value in column AM.

2. If instead you want to return the data for the nearest date, not just the nearest that is less than or equal to your lookup value, you can use something other than VLOOKUP. The array formula below will return the value from AM for the nearest date. Midpoints between dates are mapped to the later date.

   =AE4-INDEX($AM$3:$AM$39714,MAX(IF(ABS($AL$3:$AL$39714-AD4)=MIN(ABS($AL$3:$AL$39714-AD4)),ROW($AL$3:$AL$39714)-2,-1)))
   

   This is an array formula so it must be entered by pressing Ctrl+Shift+Enter to work properly. It will appear in the formula bar wrapped in curly brackets if this is done correctly.

   With a long arrays like yours (n = 39,712), array formulas can be computationally slow and may bog down your spreadsheet. So take that into consideration when deciding which method to use.