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

E

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

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
 
E

Eric

Thank you very much for your suggestions
Eric

T. Valko said:
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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top