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.
=IF($A49="","",IF(MAX(IF($A49=AnalysisMeterID,AnalysisSampleDate))>$E$1+15,"ERROR",MAX(IF($A49=AnalysisMeterID,AnalysisSampleDate))))

Open in new window

jwhst10Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

=IF($A49="","",MAX(IF($A49=AnalysisMeterID,IF(AnalysisSampleDate<=$E$1+15,AnalysisSampleDate))))

confirmed with CTRL+SHIFT+ENTER

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

regards, barry
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jwhst10Author Commented:
Barry,

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

Justin
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.