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!

 

[collegeitdept]

(a) The first formula should work as advertised. A test with the second formula using V() ends up in #VALUE!. It looks like V() cannot handle the OFFSET bit here, which is a surprise to me.

(b) Repeating the product names as you propose above simplifies the task...

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

=MAX(IF($A$5:$M$5 = B$1, IF($A$6:$M$6 = B$2, IF($A$7:$M$29 < B$3, $A$7:$M$29))))

Hi Aladin it work... however, after I realized that the product label headers were actually repeated on all columns as shown in the new table.

Can the formula be simplified from the original first one??

=MAX(IF(OFFSET(INDEX($A$7:$M$7,MATCH($B$1,$A$5:$M$5,0)),0,MATCH($B$2,{"A","B","C"},0)-1,
ROWS(A:A)-MIN(ROW($A$7))) < $B$3,OFFSET(INDEX($A$7:$M$7,MATCH($B$1,$A$5:$M$5,0)),0,
MATCH($B$2,{"A","B","C"},0)-1,ROWS(A:A)-MIN(ROW($A$7)))))


without using the {"A","B","C"}?? The reason being... is that model numbers change frequently (more model variants are added all the time) so it won't always work.

Thanks.

 

[Aladin Akyurek]

Hi Aladin it work... however, after I realized that the product label headers were actually repeated on all columns as shown in the new table.

Can the formula be simplified from the original first one??

=MAX(IF(OFFSET(INDEX($A$7:$M$7,MATCH($B$1,$A$5:$M$5,0)),0,MATCH($B$2,{"A","B","C"},0)-1,
ROWS(A:A)-MIN(ROW($A$7))) < $B$3,OFFSET(INDEX($A$7:$M$7,MATCH($B$1,$A$5:$M$5,0)),0,
MATCH($B$2,{"A","B","C"},0)-1,ROWS(A:A)-MIN(ROW($A$7)))))


without using the {"A","B","C"}?? The reason being... is that model numbers change frequently (more model variants are added all the time) so it won't always work.

Thanks.

Did you also try...

{=MAX(IF($A$5:$M$5 = B$1,IF($A$6:$M$6 = B$2, IF($A$7:$M$29 < B$3, $A$7:$M$29))))}

with your new lay-out (where product labels are repeated? Note that a larger set of models won't affect this formula.

 

[collegeitdept]

Did you also try...

{=MAX(IF($A$5:$M$5 = B$1,IF($A$6:$M$6 = B$2, IF($A$7:$M$29 < B$3, $A$7:$M$29))))}

with your new lay-out (where product labels are repeated? Note that a larger set of models won't affect this formula.

Thanks Aladin... I have the actual complete reference file of all delivery schedules at work. I will test on the actual full (and LONG) list on Monday and get back to you if it doesn't work.

Thanks.

 

[Aladin Akyurek]

Thanks Aladin... I have the actual complete reference file of all delivery schedules at work. I will test on the actual full (and LONG) list on Monday and get back to you if it doesn't work.

Thanks.

If you have a huge set, we can switch to dynamic named ranges.

 

[markmzz]

Hi Collegeitdept,

Did you try my formulas of the post #9 and #10?

If yes, what you got?

Markmzz

 

[Aladin Akyurek]

Thanks Aladin... I have the actual complete reference file of all delivery schedules at work. I will test on the actual full (and LONG) list on Monday and get back to you if it doesn't work.

Thanks.

If you have a huge set, we can switch to dynamic named ranges.

This is also possible with the new lay-out and it's dynamic, that is, it keeps up with additions of new records and deletions:

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

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

which exploits the fact the dates are all in ascending order.

 

[markmzz]

You can try this too (a small modification in my formula of the post #10):

In B5 - use Ctrl+Shift+Enter and not only Enter to enter the formula

=IFERROR(LOOKUP(B$4-1,INDEX($A$10:$M$100,,MATCH(B$2&B$3,$A$8:$M$8&$A$9:$M$9,0))),"")

Or

=IFERROR(LOOKUP(B$4-1,INDEX($A:$M,,MATCH(B$2&B$3,$A$8:$M$8&$A$9:$M$9,0))),"")

Markmzz

 

[collegeitdept]

This is also possible with the new lay-out and it's dynamic, that is, it keeps up with additions of new records and deletions:

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

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

which exploits the fact the dates are all in ascending order.

Thanks Aladin!

I will try in the morning on my test file and then again on the real file at work. It's 4 AM here in California.

Thank you of your help.

 

[collegeitdept]

Thanks Aladin!

I will try in the morning on my test file and then again on the real file at work. It's 4 AM here in California.

Thank you of your help.

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:


[TABLE="width: 1818"]
<colgroup><col width="64" span="2" style="width: 48pt;"><col width="68" style="width: 51pt;"><col width="75" span="18" style="width: 56pt;"><col width="68" span="4" style="width: 51pt;"></colgroup><tbody>[TR]
[TD="width: 64"]Product 1[/TD]
[TD="width: 64"]A[/TD]
[TD="class: xl65, width: 68, align: right"]9/16/2013[/TD]
[TD="class: xl65, width: 75, align: right"]9/22/2013[/TD]
[TD="class: xl65, width: 75, align: right"]10/7/2013[/TD]
[TD="class: xl65, width: 75, align: right"]10/12/2013[/TD]
[TD="class: xl65, width: 75, align: right"]11/1/2013[/TD]
[TD="class: xl65, width: 75, align: right"]11/1/2013[/TD]
[TD="class: xl65, width: 75, align: right"]11/6/2013[/TD]
[TD="class: xl65, width: 75, align: right"]11/9/2013[/TD]
[TD="class: xl65, width: 75, align: right"]11/11/2013[/TD]
[TD="class: xl65, width: 75, align: right"]11/15/2013[/TD]
[TD="class: xl65, width: 75, align: right"]12/5/2013[/TD]
[TD="class: xl65, width: 75, align: right"]12/12/2013[/TD]
[TD="class: xl65, width: 75, align: right"]12/26/2013[/TD]
[TD="class: xl65, width: 75, align: right"]1/7/2014[/TD]
[TD="class: xl65, width: 75"][/TD]
[TD="class: xl65, width: 75"][/TD]
[TD="class: xl65, width: 75"][/TD]
[TD="class: xl65, width: 75"][/TD]
[TD="class: xl65, width: 75"][/TD]
[TD="class: xl65, width: 68"][/TD]
[TD="class: xl65, width: 68"][/TD]
[TD="class: xl65, width: 68"][/TD]
[TD="class: xl65, width: 68"][/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]B[/TD]
[TD="class: xl65, align: right"]9/8/2013[/TD]
[TD="class: xl65, align: right"]10/14/2013[/TD]
[TD="class: xl65, align: right"]10/19/2013[/TD]
[TD="class: xl65, align: right"]10/21/2013[/TD]
[TD="class: xl65, align: right"]11/20/2013[/TD]
[TD="class: xl65, align: right"]12/16/2013[/TD]
[TD="class: xl65, align: right"]12/18/2013[/TD]
[TD="class: xl65, align: right"]12/20/2013[/TD]
[TD="class: xl65, align: right"]1/25/2014[/TD]
[TD="class: xl65, align: right"]2/19/2014[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD]Product 1[/TD]
[TD]C[/TD]
[TD="class: xl65, align: right"]8/23/2013[/TD]
[TD="class: xl65, align: right"]9/7/2013[/TD]
[TD="class: xl65, align: right"]9/25/2013[/TD]
[TD="class: xl65, align: right"]10/8/2013[/TD]
[TD="class: xl65, align: right"]10/14/2013[/TD]
[TD="class: xl65, align: right"]10/19/2013[/TD]
[TD="class: xl65, align: right"]10/20/2013[/TD]
[TD="class: xl65, align: right"]10/25/2013[/TD]
[TD="class: xl65, align: right"]10/29/2013[/TD]
[TD="class: xl65, align: right"]11/1/2013[/TD]
[TD="class: xl65, align: right"]11/4/2013[/TD]
[TD="class: xl65, align: right"]11/7/2013[/TD]
[TD="class: xl65, align: right"]11/15/2013[/TD]
[TD="class: xl65, align: right"]11/16/2013[/TD]
[TD="class: xl65, align: right"]11/28/2013[/TD]
[TD="class: xl65, align: right"]12/7/2013[/TD]
[TD="class: xl65, align: right"]1/11/2014[/TD]
[TD="class: xl65, align: right"]1/12/2014[/TD]
[TD="class: xl65, align: right"]1/24/2014[/TD]
[TD="class: xl65, align: right"]1/29/2014[/TD]
[TD="class: xl65, align: right"]2/8/2014[/TD]
[TD="class: xl65, align: right"]2/22/2014[/TD]
[TD="class: xl65, align: right"]2/25/2014[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]A[/TD]
[TD="class: xl65, align: right"]9/12/2013[/TD]
[TD="class: xl65, align: right"]9/16/2013[/TD]
[TD="class: xl65, align: right"]9/27/2013[/TD]
[TD="class: xl65, align: right"]10/27/2013[/TD]
[TD="class: xl65, align: right"]11/6/2013[/TD]
[TD="class: xl65, align: right"]11/7/2013[/TD]
[TD="class: xl65, align: right"]11/9/2013[/TD]
[TD="class: xl65, align: right"]11/11/2013[/TD]
[TD="class: xl65, align: right"]12/28/2013[/TD]
[TD="class: xl65, align: right"]1/7/2014[/TD]
[TD="class: xl65, align: right"]1/10/2014[/TD]
[TD="class: xl65, align: right"]1/23/2014[/TD]
[TD="class: xl65, align: right"]2/11/2014[/TD]
[TD="class: xl65, align: right"]2/20/2014[/TD]
[TD="class: xl65, align: right"]2/25/2014[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD]Product 2[/TD]
[TD]B[/TD]
[TD="class: xl65, align: right"]9/2/2013[/TD]
[TD="class: xl65, align: right"]10/10/2013[/TD]
[TD="class: xl65, align: right"]11/7/2013[/TD]
[TD="class: xl65, align: right"]11/26/2013[/TD]
[TD="class: xl65, align: right"]11/26/2013[/TD]
[TD="class: xl65, align: right"]12/24/2013[/TD]
[TD="class: xl65, align: right"]12/28/2013[/TD]
[TD="class: xl65, align: right"]1/19/2014[/TD]
[TD="class: xl65, align: right"]2/7/2014[/TD]
[TD="class: xl65, align: right"]2/15/2014[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD]Product 3[/TD]
[TD]A[/TD]
[TD="class: xl65, align: right"]8/19/2013[/TD]
[TD="class: xl65, align: right"]8/21/2013[/TD]
[TD="class: xl65, align: right"]9/4/2013[/TD]
[TD="class: xl65, align: right"]9/14/2013[/TD]
[TD="class: xl65, align: right"]9/17/2013[/TD]
[TD="class: xl65, align: right"]10/10/2013[/TD]
[TD="class: xl65, align: right"]10/30/2013[/TD]
[TD="class: xl65, align: right"]11/8/2013[/TD]
[TD="class: xl65, align: right"]12/4/2013[/TD]
[TD="class: xl65, align: right"]12/17/2013[/TD]
[TD="class: xl65, align: right"]12/21/2013[/TD]
[TD="class: xl65, align: right"]12/25/2013[/TD]
[TD="class: xl65, align: right"]1/4/2014[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD]Product 4[/TD]
[TD]A[/TD]
[TD="class: xl65, align: right"]8/14/2013[/TD]
[TD="class: xl65, align: right"]8/17/2013[/TD]
[TD="class: xl65, align: right"]8/28/2013[/TD]
[TD="class: xl65, align: right"]9/6/2013[/TD]
[TD="class: xl65, align: right"]9/9/2013[/TD]
[TD="class: xl65, align: right"]10/8/2013[/TD]
[TD="class: xl65, align: right"]10/16/2013[/TD]
[TD="class: xl65, align: right"]10/29/2013[/TD]
[TD="class: xl65, align: right"]11/11/2013[/TD]
[TD="class: xl65, align: right"]11/12/2013[/TD]
[TD="class: xl65, align: right"]12/4/2013[/TD]
[TD="class: xl65, align: right"]12/9/2013[/TD]
[TD="class: xl65, align: right"]12/12/2013[/TD]
[TD="class: xl65, align: right"]12/13/2013[/TD]
[TD="class: xl65, align: right"]12/13/2013[/TD]
[TD="class: xl65, align: right"]12/13/2013[/TD]
[TD="class: xl65, align: right"]12/16/2013[/TD]
[TD="class: xl65, align: right"]12/24/2013[/TD]
[TD="class: xl65, align: right"]12/29/2013[/TD]
[TD="class: xl65, align: right"]1/5/2014[/TD]
[TD="class: xl65, align: right"]1/11/2014[/TD]
[TD="class: xl65, align: right"]1/13/2014[/TD]
[TD="class: xl65, align: right"]2/17/2014[/TD]
[/TR]
[TR]
[TD]Product 4[/TD]
[TD]B[/TD]
[TD="class: xl65, align: right"]8/24/2013[/TD]
[TD="class: xl65, align: right"]9/24/2013[/TD]
[TD="class: xl65, align: right"]10/7/2013[/TD]
[TD="class: xl65, align: right"]10/17/2013[/TD]
[TD="class: xl65, align: right"]10/20/2013[/TD]
[TD="class: xl65, align: right"]11/4/2013[/TD]
[TD="class: xl65, align: right"]11/9/2013[/TD]
[TD="class: xl65, align: right"]1/11/2014[/TD]
[TD="class: xl65, align: right"]1/14/2014[/TD]
[TD="class: xl65, align: right"]2/6/2014[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD]Product 5[/TD]
[TD]A[/TD]
[TD="class: xl65, align: right"]8/25/2013[/TD]
[TD="class: xl65, align: right"]9/4/2013[/TD]
[TD="class: xl65, align: right"]9/10/2013[/TD]
[TD="class: xl65, align: right"]9/13/2013[/TD]
[TD="class: xl65, align: right"]10/3/2013[/TD]
[TD="class: xl65, align: right"]10/8/2013[/TD]
[TD="class: xl65, align: right"]10/12/2013[/TD]
[TD="class: xl65, align: right"]11/2/2013[/TD]
[TD="class: xl65, align: right"]11/8/2013[/TD]
[TD="class: xl65, align: right"]11/9/2013[/TD]
[TD="class: xl65, align: right"]11/20/2013[/TD]
[TD="class: xl65, align: right"]12/3/2013[/TD]
[TD="class: xl65, align: right"]12/7/2013[/TD]
[TD="class: xl65, align: right"]12/8/2013[/TD]
[TD="class: xl65, align: right"]12/10/2013[/TD]
[TD="class: xl65, align: right"]12/11/2013[/TD]
[TD="class: xl65, align: right"]1/10/2014[/TD]
[TD="class: xl65, align: right"]1/25/2014[/TD]
[TD="class: xl65, align: right"]2/21/2014[/TD]
[TD="class: xl65, align: right"]2/25/2014[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="colspan: 2"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="colspan: 25"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="colspan: 2"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
[TR]
[TD="colspan: 2"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[/TR]
</tbody>[/TABLE]

 

[markmzz]

Try this (with Product in B15, Model in B16 and Date in B17):

In B18 - use only Enter

=IFERROR(LOOKUP(B$17-1,INDEX($1:$13,MATCH(B$15&B$16,INDEX($A$1:$A$13&$B$1:$B$13,),0),)),"")

Or

In B18 - use Ctrl+Shift+Enter

=IFERROR(LOOKUP(B$17-1,INDEX($1:$13,MATCH(B$15&B$16,$A$1:$A$13&$B$1:$B$13,0),)),"")

Markmzz