INDIRECT MAX with criteria (variable range length)

[dellzy]

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

 

[Aladin Akyurek]

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.

 

[dellzy]

Wow! Thanks so much for the swift response, !

It works awesomely!! Yeayyy!

Really appreciate it!

 

[Aladin Akyurek]

Wow! Thanks so much for the swift response, !

It works awesomely!! Yeayyy!

Really appreciate it!

You are welcome. Thanks for the update.

 

[arthurbr]

Cross posting under different names without links INDIRECT MAX with criteria (variable range length)