Excel VLOOKUP breaking with certain value

0
votes

I have an Excel document that pulls in values from multiple sheets depending on a date value. I use this VLOOKUP formula to find the data:

=VLOOKUP($E$1,Data!$B$2:$N$100,2,FALSE)

The formula works perfectly fine as long as the E1 value is any date before December 1st, 2019, formatted as 12/1/2019. The "Data" sheet is set up like so:

Month       Date    Users
11/1/2019 | 43770 | 143,015 | ...
12/1/2019 | 43800 | 78,410  | ...

Stepping through the formula, it evaluates to:

VLOOKUP(43800,Data!$B$2:$N$100,2,FALSE)

With any date in December 2019 or later, Excel throws a "Value not found error". Which doesn't make sense to me since 43800 can be found in the data. Toggling the E1 date back to 11/1/2019 works just fine. I have no idea how to fix it or what the root cause even is.

1
excelvlookup
Can you make sure your date, data does not have any decimals that are hidden by the format of the cell? – Scott Craner
The date is in the same format as the previous dates. I use the EDATE() function to format the searched value. – aruppairlift
but the format can be hidding the true value. it may be 43800.5, but decimal may be hidden due to the format. If that is the case Excel will not see them as a match. Excel tries to match on the actual value and not the formatted representation of the number – Scott Craner
Copying the format from a working cell does not seem to solve the issue. – aruppairlift
not the format. in the cell that you are looking up. manually put in 43800. the format may be causing the problem. – Scott Craner

1 Answers

0
votes

The issue turned out to be in the VLOOKUP formula: VLOOKUP($E$1,Data!$B$2:$N$100,2,FALSE)

$N$100 was limiting the lookup to the first 100 rows. Changing it to $N$1000 raised the row limit to 1000.