Excel: using formula for cell reference

Question

I want to use the =MAX() function but get the start and stop cell from cell values:

Given cell-values like this:

A1 = 110 (the row start)

A2 = 130 (the row end)

A3 = 4 (the column-id ie "D")

Using this the max function should become

MAX(D110:D130)

When I change the cell values the range MAX uses should change. Any way to do this? INDIRECT seems useful but i cannot make it work. Im using the same worksheet for everything.

Thanks!

Answer 1

Try this:

= MAX(INDEX($A:$XFD,A1,A3):INDEX($A:$XFD,A2,A3))

Quick note: INDIRECT should be avoided because it is volatile. INDEX is preferred.