Excel 2007 : Formula to return most recent item not exceeding present date

Hi and thanks for reading this.

I'm looking for help finding a formula that will return a value for a particular item.
Given there are multiple entries with varying dates for any particular item, I'm looking for a formula that would return the closest value for that item up to but not exceeding the date I specify.

I've attached a sample spreadsheet (while the info here is resented on one tab, the actual spreadsheet sits over two tabs- rows 1 through 14 are are sheet 1, the rest on sheet 2).

Rows 1-14 are my source data.
Rows 34 to 40 are what I would have on a separate sheet.
For example, on row 34, based on Item # and Date received, I'd like to have the formula return "34" (I show what I'd like in the blue shaded cells).

I tried with a vlookup and haven't cracked it.

I thank you for your help folks!

Try in C34:

Please Login or Register  to view this content.

used with Ctrl-Shift-Enter, then autofill down

Cheers,

Originally Posted by ConneXionLost

Try in C34:

Please Login or Register  to view this content.

used with Ctrl-Shift-Enter, then autofill down

Cheers,

OMG!!!

You are fantastic! Honestly I've been cracking my head on how to get it done, how to create a "conditional" vlookup, and you got it!!!! Pardon the exuberance but after 2 days of looking at it and letting it ferment in my head, I'm glad to see the answer.

Thanks for this. Out of curiosity, do you have a link to a place where I could read up on array formulae? The few I've seen don't show anything similar to what you've done here (which btw is a masterpiece).

Thanks again. I'll sleep easier tonight!

Just wanted to add another howl of happiness with a few fist pumps!!!!

Happy to help!

Array Formulas

Cheers,