MAX IF date formula

collegeitdept

Board Regular
Joined
Nov 14, 2008
Messages
185
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!
 
(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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
You can try this too (a small modification in my formula of the post #10):

Code:
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
 
Upvote 0
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.
 
Upvote 0
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]
 
Upvote 0
Try this (with Product in B15, Model in B16 and Date in B17):

Code:
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
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top