• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

VLOOKUP approximate match not working

In the attached file, I am using a simple VLOOKUP to return the proper invoice period based on the lookup value. This would require an approximate match as the 4th argument. I tried a descending sort on column F and that didn't work.

All dates are in date format.

What am I overlooking?
 

Attachments

  • VLOOKUP Approximate Match on a Date Not Working.xlsx
    9.2 KB · Views: 8
VLOOKUP returns an exact match or the next lower value from the table. Since your search date is lower than any date in the lookup range, you will get an error. The same would happen with LOOKUP; the only function capable of searching for an exact match or the next higher is the new XLOOKUP function that is not yet widely available.

To correct your search, use the 1st of each month for the table as opposed to the end date.
If, for some reason, that is not possible, you could instead look up the last day of the month
EOMONTH(value, 0)
 
Last edited:
I forgot about that. I ended up using an INDEX/MATCH combination. Then I had to do a descending sort on the lookup values (the dates) for the MATCH function.
 
Last edited by a moderator:
Back
Top