MAX date from INDEX MATCH results

[thart21]

Hi there,

I need to find the max date from column A (DailyDate) after the results are determined by my INDEX MATCH formula. The example is part of the results set and I need to have it pull the Total_Demand column that matches the MAX of the date in column A - 7/29/2011, 2,350,935. The below formula is giving me the 1st row - 6/27/2011, 2,451,082


=INDEX(TotalDmd,MATCH(1,INDEX((DIV=$L$1)*(FuturesMo=$P$1)*(Data!$E$1:$E$6000=$R$1),0),0),)

I think I need to use something like MAX($A$1:$A$6000) but can't determine where in my formula to place it. Anywhere I put it give me the same results.

=INDEX(TotalDmd,MATCH(1,INDEX((DIV=$L$1)*(FuturesMo=$P$1)*(Data!$V$1:$V$6000=$R$1),MAX($A$1:$A$6000),0),0),0),)

Excel Workbook
	A	B	C	D	E
1	DailyDate	Div	Total_Demand	FuturesMo	RptgMonth
2	6/27/2011	20	2451082	6/1/2011	7/1/2011
3	6/28/2011	20	2441258	6/1/2011	7/1/2011
4	6/29/2011	20	2434215	6/1/2011	7/1/2011
5	7/27/2011	20	2351693	6/1/2011	7/1/2011
6	7/28/2011	20	2351649	6/1/2011	7/1/2011
7	7/29/2011	20	2350935	6/1/2011	7/1/2011
Sheet1

Excel 2007

Thanks for any ideas on this one,

Toni

 

[Aladin Akyurek]

Hi there,

I need to find the max date from column A (DailyDate) after the results are determined by my INDEX MATCH formula. The example is part of the results set and I need to have it pull the Total_Demand column that matches the MAX of the date in column A - 7/29/2011, 2,350,935. The below formula is giving me the 1st row - 6/27/2011, 2,451,082


=INDEX(TotalDmd,MATCH(1,INDEX((DIV=$L$1)*(FuturesMo=$P$1)*(Data!$E$1:$E$6000=$R$1),0),0),)

I think I need to use something like MAX($A$1:$A$6000) but can't determine where in my formula to place it. Anywhere I put it give me the same results.

=INDEX(TotalDmd,MATCH(1,INDEX((DIV=$L$1)*(FuturesMo=$P$1)*(Data!$V$1:$V$6000=$R$1),MAX($A$1:$A$6000),0),0),0),)

Excel Workbook
	A	B	C	D	E
1	DailyDate	Div	Total_Demand	FuturesMo	RptgMonth
2	6/27/2011	20	2451082	6/1/2011	7/1/2011
3	6/28/2011	20	2441258	6/1/2011	7/1/2011
4	6/29/2011	20	2434215	6/1/2011	7/1/2011
5	7/27/2011	20	2351693	6/1/2011	7/1/2011
6	7/28/2011	20	2351649	6/1/2011	7/1/2011
7	7/29/2011	20	2350935	6/1/2011	7/1/2011
Sheet1

Excel 2007

Thanks for any ideas on this one,

Toni

If the daily dates are in ascending order and there are no date repeats...

=LOOKUP(9.99999999999999E+307,A2:A7,C2:C7)

Otherwise, again with no date repeats...

=INDEX(C2:C7,MATCH(MAX(A2:A7),A2:A7,0))

 

[Marcelo Branco]

Maybe this (if dates in column A are in ascending order)

Array-formula
=INDEX(TotalDmd,MATCH(2,IF(DIV=$L$1,IF(FuturesMo=$P$1,IF(Data!$E$1:$E$6000=$R$1,1)))))

Ctrl+Shift+Enter

M.

@Aladin
I think the OP needs to check DIV and FuturesMO not just the MAX in column A

 

[thart21]

Thanks for the responses, not sure if I'm understanding correctly but, I am already getting the results I want from DIV & FuturesMo, I need to, from that results set, pull the MAX DailyDate. There are duplicate DailyDates prior to applying the formula but, after DIV & FuturesMo are filtered, there are not, per my example table. I am not looking for the MAX DIV or FuturesMonth, just MAX DailyDate. Am I making any sense??

Thanks again, you're awesome to take the time to help me!

 

[Aladin Akyurek]

Thanks for the responses, not sure if I'm understanding correctly but, I am already getting the results I want from DIV & FuturesMo, I need to, from that results set, pull the MAX DailyDate. There are duplicate DailyDates prior to applying the formula but, after DIV & FuturesMo are filtered, there are not, per my example table. I am not looking for the MAX DIV or FuturesMonth, just MAX DailyDate. Am I making any sense??

Thanks again, you're awesome to take the time to help me!

So post #2 does not provide what you are looking for?

 

[thart21]

That's where I was confused, I didn't see a formula for the MAX of column A, DailyDate, in it, did I miss something? I am getting an N/A error when I use it. Trying to evaluate it now, thanks.

 

[thart21]

Got it! I didn't realize it but, when I went to adjust my column references, the array kept disappearing, I just needed to do Ctl+Shift+Enter again. Now I just need to spend some time figuring out how exactly it's working, thank you so much!!

 

[Aladin Akyurek]

Got it! I didn't realize it but, when I went to adjust my column references, the array kept disappearing, I just needed to do Ctl+Shift+Enter again. Now I just need to spend some time figuring out how exactly it's working, thank you so much!!

You are welcome. Thanks for providing feedback.

 

[Pankaj Jaswani]

If the daily dates are in ascending order and there are no date repeats...

=LOOKUP(9.99999999999999E+307,A2:A7,C2:C7)

Otherwise, again with no date repeats...

=INDEX(C2:C7,MATCH(MAX(A2:A7),A2:A7,0)


if dates repeat and also sequence is changed so what will formula to find max date??

 

[Aladin Akyurek]

If the daily dates are in ascending order and there are no date repeats...

=LOOKUP(9.99999999999999E+307,A2:A7,C2:C7)

Otherwise, again with no date repeats...

=INDEX(C2:C7,MATCH(MAX(A2:A7),A2:A7,0)


if dates repeat and also sequence is changed so what will formula to find max date??

If A2:A7 consists of unsorted dates and dates can repeat... Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($C$2:$C$7,SMALL(IF($A$2:$A$7=MAX($A$2:$A$7),ROW($C$2:$C$7)-ROW($C$2)+1),ROWS($1:1))),"")