Harlan Grove wrote:
Alan Beban wrote...
...

Cute. But I suspect the real reason you don't want to deal with it is
that the so-called "efficiency" to which you and many
programmer/developers sometimes refer often involves nanoseconds of
difference that are totally irrelevant to most users in most
applications; interesting to you for purposes of posting oneupmanship,
but somewhat misleading for users generally.


Fine. 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.

For that matter, you could also have used

=INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1 ),COLUMNS(DataTable))

which would have been a LOT simpler than either of the others.
Simplicity is good.

Both the MakeArray and the single ArrayMatch formulas involve no
volatile function calls, so they won't cause Excel to prompt users to
save any file containing them if users try to close such workbooks
without making any changes. Your two ArrayMatch formula, due to OFFSET
and INDIRECT calls, would cause such confusing prompts.

Is that an acceptable user consideration?

It's certainly more constructive, particularly the suggestion of

=INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1 ),COLUMNS(DataTable))

which is significantly faster than the one including the two ArrayMatch
function calls that I originally posted. Would have been nice had you
focused on the instructional value for the users in the first place,
rather than just on stroking your ego. But then, there you go!

By the way, the formula with the MakeArray function call seems to return
an error if the data table exceeds 65536 elements (I haven't yet
identified why; it might be fixable), while neither of the ArrayMatch
formulas seems to--though they are slower.

Alan Beban