Thread: 2 dimensional array look up

Hello,

I am in need of help on a 2 dimensional array table. I have a table that have unsorted data and would like to return the column heading as well as the row heading of the minimum value in the table.


AA BB CC DD EE
HH 205 208 341 295 132
II 109 112 245 199 228
JJ 141 83 180 170 293
KK 122 119 50 125 459
LL 476 472 339 386 812


Column Min KK
Row Min CC

In this example, the minimum value is 50 under the heading CC and KK. I would like to return these 2 values in 2 separate cell. I have tried index(match) but this will only work on a 1 Dimensional array.

Any help would be appreciated.

Tin

You could use a helper row & column.

helper row: B7: = MIN(B2:B6), copied across to F7

helper column: G2: = MIN(B2:F2), copied down to G6

C9: =INDEX(A2:A6,MATCH(MIN(G2:G6),G2:G6,0))
C10: =INDEX(B1:F1,,MATCH(MIN(B7:F7),B7:F7,0)) :

Hi scorer,

To do this without helper rows, the formulas get a little more complex.

For Column Min use:

=INDEX(A2:A6,SMALL(IF(B2:F6=MIN(B2:F6),ROW(A2:A6)-ROW(A2)+1),1),1)

entered with Ctrl-Shift-Enter.

And for Row Min use:

=INDEX(B1:F1,1,SMALL(IF(B2:F6=MIN(B2:F6),COLUMN(B1:F1)-COLUMN(B1)+1),1))

also entered with Ctrl-Shift-Enter.

Cheers,