MAX IF date formula

[collegeitdept]

Hello,

I wondering if someone can help me with a relatively simple questionL I am looking for a solution (formula) that look for the latest date within a category (column) - but the latest closest to a certain date (the if part).


[TABLE="width: 974"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product:[/TD]
[TD]Product 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Model:[/TD]
[TD]A[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Launch:[/TD]
[TD] 12/10/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Last Delivery Date:[/TD]
[TD]= FORMULA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Product 2[/TD]
[TD][/TD]
[TD][/TD]
[TD]Product 3[/TD]
[TD][/TD]
[TD]Product 4[/TD]
[TD][/TD]
[TD][/TD]
[TD]Product 5[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]A[/TD]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]9/16/2013[/TD]
[TD]9/8/2013[/TD]
[TD]8/23/2013[/TD]
[TD][/TD]
[TD]9/12/2013[/TD]
[TD]9/2/2013[/TD]
[TD][/TD]
[TD]8/19/2013[/TD]
[TD][/TD]
[TD]8/14/2013[/TD]
[TD]8/24/2013[/TD]
[TD][/TD]
[TD]8/25/2013[/TD]
[/TR]
[TR]
[TD]9/22/2013[/TD]
[TD]10/14/2013[/TD]
[TD]9/7/2013[/TD]
[TD][/TD]
[TD]9/16/2013[/TD]
[TD]10/10/2013[/TD]
[TD][/TD]
[TD]8/21/2013[/TD]
[TD][/TD]
[TD]8/17/2013[/TD]
[TD]9/24/2013[/TD]
[TD][/TD]
[TD]9/4/2013[/TD]
[/TR]
[TR]
[TD]10/7/2013[/TD]
[TD]10/19/2013[/TD]
[TD]9/25/2013[/TD]
[TD][/TD]
[TD]9/27/2013[/TD]
[TD]11/7/2013[/TD]
[TD][/TD]
[TD]9/4/2013[/TD]
[TD][/TD]
[TD]8/28/2013[/TD]
[TD]10/7/2013[/TD]
[TD][/TD]
[TD]9/10/2013[/TD]
[/TR]
[TR]
[TD]10/12/2013[/TD]
[TD]10/21/2013[/TD]
[TD]10/8/2013[/TD]
[TD][/TD]
[TD]10/27/2013[/TD]
[TD]11/26/2013[/TD]
[TD][/TD]
[TD]9/14/2013[/TD]
[TD][/TD]
[TD]9/6/2013[/TD]
[TD]10/17/2013[/TD]
[TD][/TD]
[TD]9/13/2013[/TD]
[/TR]
[TR]
[TD]11/1/2013[/TD]
[TD]11/20/2013[/TD]
[TD]10/14/2013[/TD]
[TD][/TD]
[TD]11/6/2013[/TD]
[TD]11/26/2013[/TD]
[TD][/TD]
[TD]9/17/2013[/TD]
[TD][/TD]
[TD]9/9/2013[/TD]
[TD]10/20/2013[/TD]
[TD][/TD]
[TD]10/3/2013[/TD]
[/TR]
[TR]
[TD]11/1/2013[/TD]
[TD]12/16/2013[/TD]
[TD]10/19/2013[/TD]
[TD][/TD]
[TD]11/7/2013[/TD]
[TD]12/24/2013[/TD]
[TD][/TD]
[TD]10/10/2013[/TD]
[TD][/TD]
[TD]10/8/2013[/TD]
[TD]11/4/2013[/TD]
[TD][/TD]
[TD]10/8/2013[/TD]
[/TR]
[TR]
[TD]11/6/2013[/TD]
[TD]12/18/2013[/TD]
[TD]10/20/2013[/TD]
[TD][/TD]
[TD]11/9/2013[/TD]
[TD]12/28/2013[/TD]
[TD][/TD]
[TD]10/30/2013[/TD]
[TD][/TD]
[TD]10/16/2013[/TD]
[TD]11/9/2013[/TD]
[TD][/TD]
[TD]10/12/2013[/TD]
[/TR]
[TR]
[TD]11/9/2013[/TD]
[TD]12/20/2013[/TD]
[TD]10/25/2013[/TD]
[TD][/TD]
[TD]11/11/2013[/TD]
[TD]1/19/2014[/TD]
[TD][/TD]
[TD]11/8/2013[/TD]
[TD][/TD]
[TD]10/29/2013[/TD]
[TD]1/11/2014[/TD]
[TD][/TD]
[TD]11/2/2013[/TD]
[/TR]
[TR]
[TD]11/11/2013[/TD]
[TD]1/25/2014[/TD]
[TD]10/29/2013[/TD]
[TD][/TD]
[TD]12/28/2013[/TD]
[TD]2/7/2014[/TD]
[TD][/TD]
[TD]12/4/2013[/TD]
[TD][/TD]
[TD]11/11/2013[/TD]
[TD]1/14/2014[/TD]
[TD][/TD]
[TD]11/8/2013[/TD]
[/TR]
[TR]
[TD]11/15/2013[/TD]
[TD]2/19/2014[/TD]
[TD]11/1/2013[/TD]
[TD][/TD]
[TD]1/7/2014[/TD]
[TD]2/15/2014[/TD]
[TD][/TD]
[TD]12/17/2013[/TD]
[TD][/TD]
[TD]11/12/2013[/TD]
[TD]2/6/2014[/TD]
[TD][/TD]
[TD]11/9/2013[/TD]
[/TR]
[TR]
[TD]12/5/2013[/TD]
[TD][/TD]
[TD]11/4/2013[/TD]
[TD][/TD]
[TD]1/10/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/21/2013[/TD]
[TD][/TD]
[TD]12/4/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD]11/20/2013[/TD]
[/TR]
[TR]
[TD]12/12/2013[/TD]
[TD][/TD]
[TD]11/7/2013[/TD]
[TD][/TD]
[TD]1/23/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/25/2013[/TD]
[TD][/TD]
[TD]12/9/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/3/2013[/TD]
[/TR]
[TR]
[TD]12/26/2013[/TD]
[TD][/TD]
[TD]11/15/2013[/TD]
[TD][/TD]
[TD]2/11/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/4/2014[/TD]
[TD][/TD]
[TD]12/12/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/7/2013[/TD]
[/TR]
[TR]
[TD]1/7/2014[/TD]
[TD][/TD]
[TD]11/16/2013[/TD]
[TD][/TD]
[TD]2/20/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12/13/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/8/2013[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]11/28/2013[/TD]
[TD][/TD]
[TD]2/25/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12/13/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/10/2013[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]12/7/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12/13/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD]12/11/2013[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1/11/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12/16/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/10/2014[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1/12/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12/24/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD]1/25/2014[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1/24/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]12/29/2013[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/21/2014[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1/29/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1/5/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD]2/25/2014[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2/8/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1/11/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2/22/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1/13/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]2/25/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]2/17/2014[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



I was thinking of some soft of SUMPRODUCT formula like: (to be entered in B5)

=SUMPRODUCT(($A$8:$M$8=B2)*($A$9:$M$9=B3),--($A$10:$M$35>B4))


Thanks!

 

[Aladin Akyurek]

Hi you guys!

So I'm at work and I thought that it would work... but it doesn't because it's the schedule data file is not organized in columns but in rows. I thought I could simply modify the formula simply because the layout is organized differently - but it didn't work.

Please see the table below that's laid-out in rows:

[...]

The changes to the formulas, which you need, are not that drastic...

Shown partially:


[TABLE="width: 653"]
<COLGROUP><COL style="WIDTH: 161pt; mso-width-source: userset; mso-width-alt: 7651" width=215><COL style="WIDTH: 113pt; mso-width-source: userset; mso-width-alt: 5347" width=150><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4750" width=134><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4380" span=2 width=123><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4494" width=126><TBODY>[TR]
[TD="class: xl67, width: 215, bgcolor: white"]Product:[/TD]
[TD="class: xl65, width: 150, bgcolor: white"]Product 1[/TD]
[TD="class: xl68, width: 134, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 123, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 123, bgcolor: transparent"] [/TD]
[TD="class: xl68, width: 126, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 215, bgcolor: white"]Model:[/TD]
[TD="class: xl65, width: 150, bgcolor: white"]A[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 215, bgcolor: white"]Launch:[/TD]
[TD="class: xl66, width: 150, bgcolor: white"]12/10/2013[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 215, bgcolor: white"]Last Delivery Date:[/TD]
[TD="class: xl66, width: 150, bgcolor: white"]12/5/2013[/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl67, width: 215, bgcolor: white"] [/TD]
[TD="class: xl66, width: 150, bgcolor: white"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[TD="class: xl68, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, width: 215, bgcolor: transparent"]Product 1[/TD]
[TD="class: xl69, width: 150, bgcolor: transparent"]A[/TD]
[TD="class: xl70, width: 134, bgcolor: transparent"]9/16/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]9/22/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]10/7/2013[/TD]
[TD="class: xl70, width: 126, bgcolor: transparent"]10/12/2013[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 215, bgcolor: transparent"]Product 1[/TD]
[TD="class: xl69, width: 150, bgcolor: transparent"]B[/TD]
[TD="class: xl70, width: 134, bgcolor: transparent"]9/8/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]10/14/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]10/19/2013[/TD]
[TD="class: xl70, width: 126, bgcolor: transparent"]10/21/2013[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 215, bgcolor: transparent"]Product 1[/TD]
[TD="class: xl69, width: 150, bgcolor: transparent"]C[/TD]
[TD="class: xl70, width: 134, bgcolor: transparent"]8/23/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]9/7/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]9/25/2013[/TD]
[TD="class: xl70, width: 126, bgcolor: transparent"]10/8/2013[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 215, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 150, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 134, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 123, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 123, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 126, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, width: 215, bgcolor: transparent"]Product 2[/TD]
[TD="class: xl69, width: 150, bgcolor: transparent"]A[/TD]
[TD="class: xl70, width: 134, bgcolor: transparent"]9/12/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]9/16/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]9/27/2013[/TD]
[TD="class: xl70, width: 126, bgcolor: transparent"]10/27/2013[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 215, bgcolor: transparent"]Product 2[/TD]
[TD="class: xl69, width: 150, bgcolor: transparent"]B[/TD]
[TD="class: xl70, width: 134, bgcolor: transparent"]9/2/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]10/10/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]11/7/2013[/TD]
[TD="class: xl70, width: 126, bgcolor: transparent"]11/26/2013[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 215, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 150, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 134, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 123, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 123, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 126, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, width: 215, bgcolor: transparent"]Product 3[/TD]
[TD="class: xl69, width: 150, bgcolor: transparent"]A[/TD]
[TD="class: xl70, width: 134, bgcolor: transparent"]8/19/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]8/21/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]9/4/2013[/TD]
[TD="class: xl70, width: 126, bgcolor: transparent"]9/14/2013[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 215, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 150, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 134, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 123, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 123, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 126, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, width: 215, bgcolor: transparent"]Product 4[/TD]
[TD="class: xl69, width: 150, bgcolor: transparent"]A[/TD]
[TD="class: xl70, width: 134, bgcolor: transparent"]8/14/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]8/17/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]8/28/2013[/TD]
[TD="class: xl70, width: 126, bgcolor: transparent"]9/6/2013[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 215, bgcolor: transparent"]Product 4[/TD]
[TD="class: xl69, width: 150, bgcolor: transparent"]B[/TD]
[TD="class: xl70, width: 134, bgcolor: transparent"]8/24/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]9/24/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]10/7/2013[/TD]
[TD="class: xl70, width: 126, bgcolor: transparent"]10/17/2013[/TD]
[/TR]
[TR]
[TD="class: xl69, width: 215, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 150, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 134, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 123, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 123, bgcolor: transparent"] [/TD]
[TD="class: xl69, width: 126, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl69, width: 215, bgcolor: transparent"]Product 5[/TD]
[TD="class: xl69, width: 150, bgcolor: transparent"]A[/TD]
[TD="class: xl70, width: 134, bgcolor: transparent"]8/25/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]9/4/2013[/TD]
[TD="class: xl70, width: 123, bgcolor: transparent"]9/10/2013[/TD]
[TD="class: xl70, width: 126, bgcolor: transparent"]9/13/2013[/TD]
[/TR]
</TBODY>[/TABLE]

The data is in: $6:$18

The processing in A:B in the first 4 rows.

B4, control+shift+enter, not just enter:

=LOOKUP(B$3-1,INDEX($6:$18,MATCH(B$2,IF($A$6:$A$18=B$1,$B$6:$B$18),0),0))

While the foregoing is good enough, the MAX formula would be: again control+shift+enter...

=MAX(IF($A$6:$A$18 = B$1, IF($B$6:$B$18 = B$2, IF($C$6:$Y$18 < B$3, $C$6:$Y$18))))