Dave F wrote...
How would I make this formula work:

INDEX("a"&COLUMNS(INDIRECT(A1):INDIRECT(A2)):A30, 4)

A1 has F2 in it
A2 has Z8 in it
....

You need another level of INDIRECT calls, but you could eliminate one
of the existing INDIRECT calls. Try

=INDEX(INDIRECT("A"&COLUMNS(INDIRECT(A1&":"&A2))): A30,4)

However, this would just pull the value of the cell 3 rows below the
topmost cell in the resulting range. Given your sample values for A1
and A2, the COLUMNS call would return 21, making your formula
equivalent to

=INDEX(A21:A30,4)

and the result would be the value of cell A24. You could achieve the
same result with either

=OFFSET(A1,COLUMNS(INDIRECT(A1&":"&A2))+2,0)

or

=INDIRECT("A"&COLUMNS(INDIRECT(A1&":"&A2))+3)