Alan Beban wrote...
....
=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!

Of course you could have offerred it too, if you had thought of it. 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.

Excel can't handle any arrays with more than 65,535 entries in either
of 1 or 2 dimensions, as you should know. It's questionable whether
anyone should try to use brute force matching on so many cells. It'd be
slow even without any udfs or volatile functions. There are tasks for
which indexed database searches would be far more appropriate than
unindexed spreadsheet searches. If the OP has so many entries to
search, the OP is being foolish using a spreadsheet for the task.
However, if the OP is only searching a few hundred entries or fewer,
your caveat provides completeness of specification but is of no
practical relevance.