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

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

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

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" wrote in message
...
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

Thank you very much for your suggestions
 

Thank you very much for your suggestions
Eric

"T. Valko" wrote:

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" wrote in message
...
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

Thank you very much for your suggestions
 

You're welcome!

Biff

"Eric" wrote in message
...
Thank you very much for your suggestions
Eric

"T. Valko" wrote:

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" wrote in message
...
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

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

Just to further explain:

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))

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

"T. Valko" wrote in message
...
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" wrote in message
...
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