Google-sheets – Why does VLOOKUP find a match in a sequence of dates when there is none

google sheetsgoogle-sheets-datesvlookup

I am working on spreadsheet that works with a school calendar. I have a sequence of dates that is a list of Wednesdays. I am trying to do a VLOOKUP on it to find out what type of day a specific date is. (Wednesdays are either Alt or Half days, all other days are Standard days) My intent was to use the VLOOKUP to find Alt or Half, and IFERROR to fill in Standard if the day is not a Wednesday (Not found in the range) For some reason, all days are matching. If the search key is not a Wednesday, the returned value is the previous Wednesday. Why is this happening? What can I do to solve it?
Here's a sample spreadsheet that demonstrates the problem:

https://docs.google.com/spreadsheets/d/1TWjMMk6t5bv0DwCG-qOSuwFYd9Yz9uoAEoOsqioHIMw/edit?usp=sharing

My formula is in cell G2

Best Answer

Finally figured this out. The issue is in the details of how a match is found based on the [is_sorted] parameter. I had it set to TRUE, because the dates are sorted. However, "If is_sorted is TRUE or omitted, the nearest match (less than or equal to the search key) is returned." -https://support.google.com/docs/answer/3093318?hl=en

So the solution is to set is_sorted to FALSE even though my range is sorted.

Related Solutions

Google Sheets – Query to Find Entries Not in Another Column

This can be done with FILTER by MATCH:

=FILTER(C2:C240,ISERROR(MATCH(C2:C240,B2:B240,0)))

Here, MATCH returns an error if the element is not found in column B (the third argument, 0, requires exact match). The command ISERROR returns true in such a case, and this is what the column C is filtered on.

In SQL, this would be implemented using, e.g., select ... where not exists ... The query language available in Sheets is not powerful enough for that.

Google Sheets – Why Days Are Used as Unit of Time When Entering a Number

The zero mark for date/time in Google Sheets is 12/30/1899 0:00:00, as you can see by entering 0 in a cell and formatting it as date/time. That said, sheets are mostly 1-based, so the idea here is that day 1 is the last day of 1899.

Spreadsheets are designed for office workers, not for programmers. In an office, time is measured in days more often than in seconds or milliseconds. Measuring time in days allows one to use, e.g., =A2+1 to get next day after the date specified in A2. Using =A2+86400 for the same purpose would not be as intuitive for the target audience.

For the same reason, the time refers to local timezone, not UTC.

Best Answer

Related Solutions

Google Sheets – Query to Find Entries Not in Another Column

Google Sheets – Why Days Are Used as Unit of Time When Entering a Number

Related Topic