How to look up a value from a table of matrix?

[Eric]

Does anyone have any suggestions on following statement?
=INDEX(A3:A26,MATCH(MIN(AI3:AI26),K3:K26,0)), which work OK
but if the lookup arrary is a table of matrix C3:Q26,
=INDEX(A3:A26,MATCH(MIN(AI3:AI26),C3:Q26,0)), then it returns #N/A,
The task is to look for the minimum value under AI3:AI26 matching with a
table of matrix under C3:Q26, and then return the value under A3:A26 on the
same row.
it seems to me that the match function cannot work with a table of matrix.
Does anyone have any suggestions on how to match a value from a table of
matrix?
Thank you for any suggestions
Eric

 

[T. Valko]

but if the lookup arrary is a table of matrix C3:Q26 ...

then it returns #N/A

The lookup_array must be a 1 dimensional array.

Try this array formula** :

=INDEX(A3:A26,MATCH(TRUE,MMULT(--(C3:Q26=MIN(AI3:AI26)),TRANSPOSE(COLUMN(C3:Q26)^0))>0,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

 

[Eric]

Thank you very much for your suggestions
Eric

The lookup_array must be a 1 dimensional array.

Try this array formula** :

=INDEX(A3:A26,MATCH(TRUE,MMULT(--(C3:Q26=MIN(AI3:AI26)),TRANSPOSE(COLUMN(C3:Q26)^0))>0,0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

 

[T. Valko]

You're welcome!

Biff

Thank you very much for your suggestions
Eric

 

[T. Valko]

Just to further explain:

The lookup_array must be a 1 dimensional array.

Try this array formula** :

=INDEX(A3:A26,MATCH(TRUE,MMULT(--(C3:Q26=MIN(AI3:AI26)),TRANSPOSE(COLUMN(C3:Q26)^0))>0,0))

The formula processes the range C3:Q26 by testing each individual row for
the presence of the MIN value. The result of this process is a 1 dimensional
array which MATCH can then handle.

Biff