MAX IF date formula

C

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!
 
Sort by date Sort by votes
misrasomendra said:
Hi collegeitdept....What is the result you are expecting out of this formula?Regards,SM.
Click to expand...


The result should be:
12/5/2013

because of the following:

Product 1
Model A
Launch date = 12/10/2013


12/5/2013 is the latest date that occurred for Product 1 Model A before the its launch date on 12/10/2013.

Thanks!





 
Upvote 0
collegeitdept said:
The result should be:
12/5/2013

because of the following:

Product 1
Model A
Launch date = 12/10/2013


12/5/2013 is the latest date that occurred for Product 1 Model A before the its launch date on 12/10/2013.

Thanks!
Click to expand...

Let A:M house the processing area (first 4 rows in A:B) and the data, with the product headers in row 5 and the model headers in row 6. See below (not all rows shown).

[TABLE="width: 888"]
<TBODY>[TR]
[TD="class: xl65, width: 157, bgcolor: white"]Product:
[/TD]
[TD="class: xl65, width: 110, bgcolor: white"]Product 1
[/TD]
[TD="class: xl65, width: 105, bgcolor: white"][/TD]
[TD="class: xl67, width: 28, bgcolor: white"][/TD]
[TD="class: xl65, width: 125, bgcolor: white"][/TD]
[TD="class: xl65, width: 92, bgcolor: white"][/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl65, width: 142, bgcolor: white"][/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl65, width: 121, bgcolor: white"][/TD]
[TD="class: xl65, width: 92, bgcolor: white"][/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl65, width: 125, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 157, bgcolor: white"]Model:
[/TD]
[TD="class: xl65, width: 110, bgcolor: white"]A
[/TD]
[TD="class: xl65, width: 105, bgcolor: white"][/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl65, width: 125, bgcolor: white"][/TD]
[TD="class: xl65, width: 92, bgcolor: white"][/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl65, width: 142, bgcolor: white"][/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl65, width: 121, bgcolor: white"][/TD]
[TD="class: xl65, width: 92, bgcolor: white"][/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl65, width: 125, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 157, bgcolor: white"]Launch:
[/TD]
[TD="class: xl67, width: 110, bgcolor: white"]12/10/2013
[/TD]
[TD="class: xl65, width: 105, bgcolor: white"][/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl65, width: 125, bgcolor: white"][/TD]
[TD="class: xl65, width: 92, bgcolor: white"][/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl65, width: 142, bgcolor: white"][/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl65, width: 121, bgcolor: white"][/TD]
[TD="class: xl65, width: 92, bgcolor: white"][/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl65, width: 125, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 157, bgcolor: white"]Last Delivery Date:
[/TD]
[TD="class: xl67, width: 110, bgcolor: white"]12/5/2013
[/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 157, bgcolor: white"]Product 1
[/TD]
[TD="class: xl65, width: 110, bgcolor: white"][/TD]
[TD="class: xl65, width: 105, bgcolor: white"][/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl66, width: 125, bgcolor: white"]Product 2
[/TD]
[TD="class: xl65, width: 92, bgcolor: white"][/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl66, width: 142, bgcolor: white"]Product 3
[/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl66, width: 121, bgcolor: white"]Product 4
[/TD]
[TD="class: xl65, width: 92, bgcolor: white"][/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl66, width: 125, bgcolor: white"]Product 5
[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 157, bgcolor: white"]A
[/TD]
[TD="class: xl65, width: 110, bgcolor: white"]B
[/TD]
[TD="class: xl65, width: 105, bgcolor: white"]C
[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl65, width: 125, bgcolor: white"]A
[/TD]
[TD="class: xl65, width: 92, bgcolor: white"]B
[/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl65, width: 142, bgcolor: white"]A
[/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl65, width: 121, bgcolor: white"]A
[/TD]
[TD="class: xl65, width: 92, bgcolor: white"]B
[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl65, width: 125, bgcolor: white"]A
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 157, bgcolor: white"]9/16/2013
[/TD]
[TD="class: xl67, width: 110, bgcolor: white"]9/8/2013
[/TD]
[TD="class: xl67, width: 105, bgcolor: white"]8/23/2013
[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl67, width: 125, bgcolor: white"]9/12/2013
[/TD]
[TD="class: xl67, width: 92, bgcolor: white"]9/2/2013
[/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl67, width: 142, bgcolor: white"]8/19/2013
[/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl67, width: 121, bgcolor: white"]8/14/2013
[/TD]
[TD="class: xl67, width: 92, bgcolor: white"]8/24/2013
[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl67, width: 125, bgcolor: white"]8/25/2013
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 157, bgcolor: white"]9/22/2013
[/TD]
[TD="class: xl67, width: 110, bgcolor: white"]10/14/2013
[/TD]
[TD="class: xl67, width: 105, bgcolor: white"]9/7/2013
[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl67, width: 125, bgcolor: white"]9/16/2013
[/TD]
[TD="class: xl67, width: 92, bgcolor: white"]10/10/2013
[/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl67, width: 142, bgcolor: white"]8/21/2013
[/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl67, width: 121, bgcolor: white"]8/17/2013
[/TD]
[TD="class: xl67, width: 92, bgcolor: white"]9/24/2013
[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl67, width: 125, bgcolor: white"]9/4/2013
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 157, bgcolor: white"]10/7/2013
[/TD]
[TD="class: xl67, width: 110, bgcolor: white"]10/19/2013
[/TD]
[TD="class: xl67, width: 105, bgcolor: white"]9/25/2013
[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl67, width: 125, bgcolor: white"]9/27/2013
[/TD]
[TD="class: xl67, width: 92, bgcolor: white"]11/7/2013
[/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl67, width: 142, bgcolor: white"]9/4/2013
[/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl67, width: 121, bgcolor: white"]8/28/2013
[/TD]
[TD="class: xl67, width: 92, bgcolor: white"]10/7/2013
[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl67, width: 125, bgcolor: white"]9/10/2013
[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 157, bgcolor: white"]10/12/2013
[/TD]
[TD="class: xl67, width: 110, bgcolor: white"]10/21/2013
[/TD]
[TD="class: xl67, width: 105, bgcolor: white"]10/8/2013
[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl67, width: 125, bgcolor: white"]10/27/2013
[/TD]
[TD="class: xl67, width: 92, bgcolor: white"]11/26/2013
[/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl67, width: 142, bgcolor: white"]9/14/2013
[/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl67, width: 121, bgcolor: white"]9/6/2013
[/TD]
[TD="class: xl67, width: 92, bgcolor: white"]10/17/2013
[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl67, width: 125, bgcolor: white"]9/13/2013
[/TD]
[/TR]
</TBODY>[/TABLE]

B4, control+shift+enter, not just enter:
Rich (BB code): 
=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)))))

If desired, we can avoid computing the same thing twice using Dunn's udf V()...
Rich (BB code): 
=MAX(IF(V(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,V()))
 
Upvote 0
Aladin Akyurek said:
Let A:M house the processing area (first 4 rows in A:B) and the data, with the product headers in row 5 and the model headers in row 6. See below (not all rows shown).

[TABLE="width: 888"]
<tbody>[TR]
[TD="class: xl65, width: 157, bgcolor: white"]Product:[/TD]
[TD="class: xl65, width: 110, bgcolor: white"]Product 1[/TD]
[TD="class: xl65, width: 105, bgcolor: white"][/TD]
[TD="class: xl67, width: 28, bgcolor: white"][/TD]
[TD="class: xl65, width: 125, bgcolor: white"][/TD]
[TD="class: xl65, width: 92, bgcolor: white"][/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl65, width: 142, bgcolor: white"][/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl65, width: 121, bgcolor: white"][/TD]
[TD="class: xl65, width: 92, bgcolor: white"][/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl65, width: 125, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 157, bgcolor: white"]Model:[/TD]
[TD="class: xl65, width: 110, bgcolor: white"]A[/TD]
[TD="class: xl65, width: 105, bgcolor: white"][/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl65, width: 125, bgcolor: white"][/TD]
[TD="class: xl65, width: 92, bgcolor: white"][/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl65, width: 142, bgcolor: white"][/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl65, width: 121, bgcolor: white"][/TD]
[TD="class: xl65, width: 92, bgcolor: white"][/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl65, width: 125, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 157, bgcolor: white"]Launch:[/TD]
[TD="class: xl67, width: 110, bgcolor: white"]12/10/2013[/TD]
[TD="class: xl65, width: 105, bgcolor: white"][/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl65, width: 125, bgcolor: white"][/TD]
[TD="class: xl65, width: 92, bgcolor: white"][/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl65, width: 142, bgcolor: white"][/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl65, width: 121, bgcolor: white"][/TD]
[TD="class: xl65, width: 92, bgcolor: white"][/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl65, width: 125, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl65, width: 157, bgcolor: white"]Last Delivery Date:[/TD]
[TD="class: xl67, width: 110, bgcolor: white"]12/5/2013[/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[TD="class: xl68, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 157, bgcolor: white"]Product 1[/TD]
[TD="class: xl65, width: 110, bgcolor: white"][/TD]
[TD="class: xl65, width: 105, bgcolor: white"][/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl66, width: 125, bgcolor: white"]Product 2[/TD]
[TD="class: xl65, width: 92, bgcolor: white"][/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl66, width: 142, bgcolor: white"]Product 3[/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl66, width: 121, bgcolor: white"]Product 4[/TD]
[TD="class: xl65, width: 92, bgcolor: white"][/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl66, width: 125, bgcolor: white"]Product 5[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 157, bgcolor: white"]A[/TD]
[TD="class: xl65, width: 110, bgcolor: white"]B[/TD]
[TD="class: xl65, width: 105, bgcolor: white"]C[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl65, width: 125, bgcolor: white"]A[/TD]
[TD="class: xl65, width: 92, bgcolor: white"]B[/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl65, width: 142, bgcolor: white"]A[/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl65, width: 121, bgcolor: white"]A[/TD]
[TD="class: xl65, width: 92, bgcolor: white"]B[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl65, width: 125, bgcolor: white"]A[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 157, bgcolor: white"]9/16/2013[/TD]
[TD="class: xl67, width: 110, bgcolor: white"]9/8/2013[/TD]
[TD="class: xl67, width: 105, bgcolor: white"]8/23/2013[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl67, width: 125, bgcolor: white"]9/12/2013[/TD]
[TD="class: xl67, width: 92, bgcolor: white"]9/2/2013[/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl67, width: 142, bgcolor: white"]8/19/2013[/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl67, width: 121, bgcolor: white"]8/14/2013[/TD]
[TD="class: xl67, width: 92, bgcolor: white"]8/24/2013[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl67, width: 125, bgcolor: white"]8/25/2013[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 157, bgcolor: white"]9/22/2013[/TD]
[TD="class: xl67, width: 110, bgcolor: white"]10/14/2013[/TD]
[TD="class: xl67, width: 105, bgcolor: white"]9/7/2013[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl67, width: 125, bgcolor: white"]9/16/2013[/TD]
[TD="class: xl67, width: 92, bgcolor: white"]10/10/2013[/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl67, width: 142, bgcolor: white"]8/21/2013[/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl67, width: 121, bgcolor: white"]8/17/2013[/TD]
[TD="class: xl67, width: 92, bgcolor: white"]9/24/2013[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl67, width: 125, bgcolor: white"]9/4/2013[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 157, bgcolor: white"]10/7/2013[/TD]
[TD="class: xl67, width: 110, bgcolor: white"]10/19/2013[/TD]
[TD="class: xl67, width: 105, bgcolor: white"]9/25/2013[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl67, width: 125, bgcolor: white"]9/27/2013[/TD]
[TD="class: xl67, width: 92, bgcolor: white"]11/7/2013[/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl67, width: 142, bgcolor: white"]9/4/2013[/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl67, width: 121, bgcolor: white"]8/28/2013[/TD]
[TD="class: xl67, width: 92, bgcolor: white"]10/7/2013[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl67, width: 125, bgcolor: white"]9/10/2013[/TD]
[/TR]
[TR]
[TD="class: xl67, width: 157, bgcolor: white"]10/12/2013[/TD]
[TD="class: xl67, width: 110, bgcolor: white"]10/21/2013[/TD]
[TD="class: xl67, width: 105, bgcolor: white"]10/8/2013[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl67, width: 125, bgcolor: white"]10/27/2013[/TD]
[TD="class: xl67, width: 92, bgcolor: white"]11/26/2013[/TD]
[TD="class: xl65, width: 29, bgcolor: white"][/TD]
[TD="class: xl67, width: 142, bgcolor: white"]9/14/2013[/TD]
[TD="class: xl65, width: 30, bgcolor: white"][/TD]
[TD="class: xl67, width: 121, bgcolor: white"]9/6/2013[/TD]
[TD="class: xl67, width: 92, bgcolor: white"]10/17/2013[/TD]
[TD="class: xl65, width: 28, bgcolor: white"][/TD]
[TD="class: xl67, width: 125, bgcolor: white"]9/13/2013[/TD]
[/TR]
</tbody>[/TABLE]

B4, control+shift+enter, not just enter:
Rich (BB code): 
=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)))))

If desired, we can avoid computing the same thing twice using Dunn's udf V()...
Rich (BB code): 
=MAX(IF(V(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,V()))
Click to expand...





It's not working... it's returning: #NAME?

I do prefer the second one because the formula is shorter. However, Excel is returning #NAME?.

Thanks.
 
Last edited:
Upvote 0
collegeitdept said:
It's not working... it's returning: #NAME?

I do prefer the second one because the formula is shorter. However, Excel is returning #NAME?.

Thanks.
Click to expand...

Second formula requires installing the following udf:

Public Function V(Optional vrnt As Variant) As Variant
'
' Stephen Dunn
' 2002-09-12
'
Static vrntV As Variant
If Not IsMissing(vrnt) Then vrntV = vrnt
V = vrntV
End Function


You need to add this code to your workbook as a module, using Alt+F11.
 
Upvote 0
collegeitdept said:
It's not working... it's returning: #NAME?

I do prefer the second one because the formula is shorter. However, Excel is returning #NAME?.

Thanks.
Click to expand...


Because the formula didn't work... I was thinking about why:

I realized that above the each model number, the product name is listed again... please see the amended table:


I think the repeated names can help with the formula - that's why I thought the SUMPRODUCT formula could work.


[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]Product 1[/TD]
[TD]Product 1[/TD]
[TD][/TD]
[TD]Product 2[/TD]
[TD]Product 2[/TD]
[TD][/TD]
[TD]Product 3[/TD]
[TD][/TD]
[TD]Product 4[/TD]
[TD]Product 4[/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]
 
Last edited:
Upvote 0
collegeitdept said:
Because the formula didn't work... I was thinking about why:

I realized that above the each model number, the product name is listed again... please see the amended table:


I think the repeated names can help with the formula - that's why I thought the SUMPRODUCT formula could work.

[...]
Click to expand...

(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))))
 
Upvote 0
Maybe this:

Layout

[TABLE="width: 529"]
<colgroup><col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;" span="2"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;" span="2"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;" span="2"> <col width="17" style="width: 13pt; mso-width-source: userset; mso-width-alt: 621;"> <col width="67" style="width: 50pt; mso-width-source: userset; mso-width-alt: 2450;"> <tbody>[TR]
[TD="class: xl63, width: 103, bgcolor: transparent"]**[/TD]
[TD="class: xl63, width: 67, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 67, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 67, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 67, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 67, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 67, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 67, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 17, bgcolor: transparent"] [/TD]
[TD="class: xl63, width: 67, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Product:[/TD]
[TD="class: xl63, bgcolor: transparent"]Product 1[/TD]
[TD="class: xl63, bgcolor: transparent"]Product 5[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Model:[/TD]
[TD="class: xl63, bgcolor: transparent"]A[/TD]
[TD="class: xl63, bgcolor: transparent"]A[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Launch:[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10/12/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]12/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Last Delivery Date:[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]05/12/2013[/TD]
[TD="class: xl65, bgcolor: yellow, align: right"]11/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]**[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]**[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]Product 1[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"]Product 2[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"]Product 3[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"]Product 4[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"]Product 5[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]A[/TD]
[TD="class: xl63, bgcolor: transparent"]B[/TD]
[TD="class: xl63, bgcolor: transparent"]C[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"]A[/TD]
[TD="class: xl63, bgcolor: transparent"]B[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"]A[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"]A[/TD]
[TD="class: xl63, bgcolor: transparent"]B[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"]A[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]16/09/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/09/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]23/08/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]12/09/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]02/09/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]19/08/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]14/08/2013[/TD]
[TD="class: xl63, bgcolor: transparent"]8/24/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]25/08/2013[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]22/09/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]14/10/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]07/09/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]16/09/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10/10/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]21/08/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]17/08/2013[/TD]
[TD="class: xl63, bgcolor: transparent"]9/24/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]04/09/2013[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]07/10/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]19/10/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]25/09/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]27/09/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]07/11/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]04/09/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]28/08/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10/07/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10/09/2013[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]12/10/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]21/10/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/10/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]27/10/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]26/11/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]14/09/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]06/09/2013[/TD]
[TD="class: xl63, bgcolor: transparent"]10/17/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]13/09/2013[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]01/11/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]20/11/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]14/10/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]06/11/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]26/11/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]17/09/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]09/09/2013[/TD]
[TD="class: xl63, bgcolor: transparent"]10/20/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]03/10/2013[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]01/11/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]16/12/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]19/10/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]07/11/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]24/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10/10/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/10/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]11/04/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/10/2013[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]06/11/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]18/12/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]20/10/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]09/11/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]28/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]30/10/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]16/10/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]11/09/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]12/10/2013[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]09/11/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]20/12/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]25/10/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]11/11/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]19/01/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/11/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]29/10/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]01/11/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]02/11/2013[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]11/11/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]25/01/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]29/10/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]28/12/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]07/02/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]04/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]11/11/2013[/TD]
[TD="class: xl63, bgcolor: transparent"]1/14/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/11/2013[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]15/11/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]19/02/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]01/11/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]07/01/2014[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]15/02/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]17/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]12/11/2013[/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]02/06/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]09/11/2013[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]05/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]04/11/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10/01/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]21/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]04/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]20/11/2013[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]12/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]07/11/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]23/01/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]25/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]09/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]03/12/2013[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]26/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]15/11/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]11/02/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]04/01/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]12/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]07/12/2013[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: transparent, align: right"]07/01/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]16/11/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]20/02/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]13/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/12/2013[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]28/11/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]25/02/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]13/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10/12/2013[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]07/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]13/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]11/12/2013[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]11/01/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]16/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]10/01/2014[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]12/01/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]24/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]25/01/2014[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]24/01/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]29/12/2013[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]21/02/2014[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]29/01/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]05/01/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]25/02/2014[/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]08/02/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]11/01/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]22/02/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]13/01/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]25/02/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl64, bgcolor: transparent, align: right"]17/02/2014[/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[TD="class: xl63, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl63, bgcolor: transparent"]*******************[/TD]
[TD="class: xl63, bgcolor: transparent"]************[/TD]
[TD="class: xl63, bgcolor: transparent"]************[/TD]
[TD="class: xl63, bgcolor: transparent"]**[/TD]
[TD="class: xl63, bgcolor: transparent"]************[/TD]
[TD="class: xl63, bgcolor: transparent"]************[/TD]
[TD="class: xl63, bgcolor: transparent"]**[/TD]
[TD="class: xl63, bgcolor: transparent"]************[/TD]
[TD="class: xl63, bgcolor: transparent"]**[/TD]
[TD="class: xl63, bgcolor: transparent"]************[/TD]
[TD="class: xl63, bgcolor: transparent"]************[/TD]
[TD="class: xl63, bgcolor: transparent"]**[/TD]
[TD="class: xl63, bgcolor: transparent"]************[/TD]
[/TR]
</tbody>[/TABLE]


Formula

Code: 
In B5 - use only Enter to enter the formula

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


Markmzz
 
Upvote 0
With your new layout, maybe this can helps:

Code: 
In B5

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

By the way, my first formula only work correctly if you have valid data in cells B2 and B3.

Markmzz
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
710
Asbestos_Jen
Asbestos_Jen
A
  • Question Question
Make a table from data which updated monthly.
Replies
1
Views
382
adnan1975
A
C
  • Question Question
Incorrect date
2 3
Replies
25
Views
908
awoohaw
awoohaw
D
  • Question Question
Formula help
Replies
1
Views
137
6StringJazzer
6StringJazzer
D
  • Question Question
Generate unique subsets with even distribution.
Replies
6
Views
191
Kaper
Kaper

Share this page

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