• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 399
  • Last Modified:

Excel cell to pull value from array that is less than or equal to a specific date

I have a spreadsheet where I have a date that I am using in conjunction with an ID to match data in an array. The location ID is a unique (unsorted variable length and text based ID) is for a specific location and the corresponding date field is when the location was last analyzed. The location may be analyzed once per day (no more than that). New analyses are appended to the bottom of my lookup array. So I am trying to use the MATCH function and it works for when my lookup date is older than anything I am looking up, but when I have a lookup date newer than my lookup array for the given location it works however I don't know how to make it pull the most recent date that is less than or equal to the lookup date. In my code below you can see the equation (it is a CTRL+SHIFT+ENTER) and it works as it will resolve to ERROR for the cell but it won't pull the value. Any help? Thank you in advance.

Open in new window

1 Solution
barry houdiniCommented:
Assuming E1+15 is your "lookup date" try this version


confirmed with CTRL+SHIFT+ENTER

If there's no date that matches then you'll get zero

regards, barry
jwhst10Author Commented:

I thank you so much. That is incredible, clean and it works!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now