INDIRECT MAX with criteria (variable range length)

[Dahlia]

Hello experts,

I got the below formula to find MAX date when meet a criteria:-

=IF($P2="","",MAX(IF($A3:$A$9999=$A2,$P3:$P$9999))) with Ctrl+Shift+Enter

However, I have a situation where the last row of the range is variable.. meaning, it may not be only row 9999. And the start row of the range must always follow the row below the current row (where the formula is applied). It will grow for day to day, as the data will be added on daily basis. When I tried to change as below, I got error #VALUE!. Can anybody correct me, please?

=IF(INDIRECT("$P"&ROW())="","",MAX(IF(INDIRECT("$A"&ROW()+1&":$A$"&COUNT(A:A))=INDIRECT("$A"&ROW()),INDIRECT("$A"&ROW()+1&":$A$"&COUNT(A:A))))) with Ctrl+Shift+Enter

Thank you in advance.

DZ

[AlKey]

Try this one

Please Login or Register  to view this content.

[Dahlia]

Hello AlKey,

I can't put a fix start range for A as A3 and P as P3 because the range must start from the row below the current row. Your formula will only return the first max date from column P.

Thanks.

DZ

[Dahlia]

Hi,

Aladin Akyurek from MrExcel forum has given me the solution as below:-

In A1 enter:

=MATCH(9.99999999999999E+307,P:P)

Now invoke: Control+shift+enter...

=IF($P2="","",MAX(IF($A3:INDEX($A:$A,$A$1)=$A2,$P3:INDEX($P:$P,$A$1))))

This adjusts automatically to the changing ranges.

---------------------------------------

My problem solved! Thanks!

[Beamernsw]

Wow, that's much simpler than mine
I found that you can't use ROW() in an array....so I cheated with:

=IF(INDIRECT("P"&CELL("row",R2))="","",MAX(IF(INDIRECT("A"&CELL("row",R3)&":A"&CELL("row",R3)+9999)=INDIRECT("A"&CELL("row",R2)),INDIRECT("P"&CELL("row",R3)&":P"&CELL("row",R3)+9999))))

[Pepe Le Mokko]

Cross posting under different names without links ... http://www.mrexcel.com/forum/excel-q...ge-length.html