Alan Beban wrote:
Harlan Grove wrote:
. . .Then consider whether the MakeArray formula,

=INDEX(DataTable,INT((MATCH(J1*9,MakeArray(DataTab le,1),0)-1)
/COLUMNS(DataTable))+1,COLUMNS*(DataTable))

a single MATCH against the data range transformed into a 1D array, with
the result adjusted by a division inside INT to return the row number,
against the ArrayMatch formula,

=OFFSET(INDIRECT(ArrayMatch(J*19,dataTable,"A")),0 ,
-INDEX(ArrayMatch(J19,dataTab*le),1,2)+COLUMNS(data Table))

first returning the cell address of the matching cell then using
another call to fix the column offset. It's subjective whether the row
index contortions of the MakeArray formula are more obscure than the
column offset contortions of the 2 ArrayMatch formula.

...By the way, the formula with the MakeArray function call seems to return
an error if the data table exceeds 65536 elements . . . .

The problem is with the built-in INDEX function; it fails if the array
or reference contains more than 65536 elements.

Alan Beban