Nesting Indirect function in Max array

Question

I have a cell that is supposed to scan a range for a name, and then return the max (most recent) date from another range. Essentially I'm making a last date contacted cell for case managers. I had it all worked out but because I was using direct cell references, everything got messed up when I sorted the rows.

This is the closest I could get to an INDIRECT array formula:

=MAX(IF(Encounters!A$3:A$1000 = INDIRECT("Caselist"&"!"&"B"&ROW()), Encounters!C$3:C$1000))

A little help with syntax would be much appreciated. If I type

=INDIRECT("Caselist"&"!"&"B"&ROW())

into another cell it returns the value I'm looking for.

Answer 1

Avoid the volatile INDIRECT whenever possible. In most cases, the non-volatile INDEX can be used.

=MAX(IF(Encounters!A$3:A$1000 = INDEX(Caselist!B:B, ROW()), Encounters!C$3:C$1000))

This is an array formula and requires CSE.