Looking for a MAX date based on variable row number

[kenhunter]

Good afternoon, I have looked thru multiple forums but have not found what I am trying to do. Any help is appreciated. I have an Excel sheet with locations in the first column and multiple truck delivery dates per location off to the right, What I am trying to do is look for the latest delivery date prior to a specific date for a specific location. MAX formula works great to find the date but I'm having trouble finding based on location number lookup. In the example below, I'm looking for the latest date prior to 7/20/2020 for location 110. Correct date is 7/8/2020. Location and dates will vary so I need a formula that can reference 2 cells for looking up location (110) and process date (7/20/2020)

105 6/17/2020 6/26/2020 7/15/2020 7/29/2020 8/14/2020 8/28/2020
106 6/17/2020 6/26/2020 7/15/2020 7/29/2020 8/14/2020 8/28/2020
110 6/9/2020 6/23/2020 7/8/2020 7/22/2020 8/6/2020 8/20/2020

MAX formula that I'm using
=MAX(IF('2020 Schedule'!$D$3:$AA$3<C1,'2020 Schedule'!$D$3:$AA$3))

C1 is the location number I'm looking up and in this case row 3 has all the dates for location 110. This works great as long as I can manually point to row 3 but each week I have hundreds that need looking up.

Where I'm stuck is how do I fold in a lookup or Index/Match etc. to look down for the location number then across for my max date.

Thanks in advance for your assistance.

[Limor_OP]

Hi,
Can you upload your matrix to excel?
Look at the yellow banner at the top of the page.

[kenhunter]

Thank you Belinda200, I'm new here. Appreciate the guidance.

[Limor_OP]

I'm copying here the guidelines:
"Fast answers need clear examples. Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. Just before posting, scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen."

[kenhunter]

I added the spreadsheet to my post, is there something I should have done in addition?

[Fluff13]

How about
=AGGREGATE(14,6,B1:U13/(A1:A13=A17),1)

Where A17 has the location of interest.

[Limor_OP]

F16 =index($B$1:$U$13,match(D16,$A$1:$A$13,0),small(if((lookup(E16,B1:U13))=$B$1:$B$13,column($B$1:$B$13)),1)-1)

This is an array formula, meaning -
To work correctly, it needs to be entered with control + shift + enter. When you enter a formula this way, you'll see the formula...

[Fluff13]

Missed the before certain date part.
=AGGREGATE(14,6,B1:U13/((A1:A13=A17)*(B1:U13<A18)),1)

Location in A17 & Date in A18

[Limor_OP]

Message delete by user

[kenhunter]

Thanks Fluff13, this shows me the MAX date in the set for the location but I need also the ability to supply specified date that the result needs to be prior to. In the above example location 110, I looking for the MAX date prior to 7/20/2020. Result being 7/8/2020.

[kenhunter]

Fluff13, just saw your update, let me try that. :-)

[Fluff13]

Yeah, I missed that originally & so reposted in post#8

[kenhunter]

Fluff13, that works PERFECTLY!! Thank you so much for your help I greatly appreciate it!

[Fluff13]

You're welcome & thanks for the feedback.

Did you try belinda200's formula as well?

[Limor_OP]

Mine is wrong.....now I realize.
Nice solution Fluff13, easy and short

[kenhunter]

Hi Belinda200, not sure why I didn't see your suggestion earlier but wanted to thank you. I knew there had to be someway to pair up lookup & Index/match. Thank you again for your help!

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

[bebo021999]

Alternative solution;

Please Login or Register  to view this content.

[kenhunter]

Thanks for your solution

[kenhunter]

Thanks for your guidance, have a great day.