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:


My formula is in cell G2


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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.