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

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

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

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