Find a value's addres in an array

[kavehmj]

Hi
I have a n dimention array lika a matrix in excel which contain the data like this:

------1 --- 2 --- 3 --- 4 --- 5
Jan | 1.2 | 2.1 | 3.2 | 4.1 | 5.6
Feb| 6.2 | 1.1 | 12.2| 7.1 | 1.6
Mar | 1.2 | 2.1 3.2 | 9.1 | 2.6
Apr | 8.3 | 7.2 | 3.2 | 2.1 | 3.6

I want to find the location of the value 12.2 in this matrix. I need these two functions
1) find 12.2 in this matrix and return it's row name. (Here Feb)
2) find 12.2 in this matrix and return it's column name. (Here 3)

What functions should i use?

[zbor]

What if there's few same values?

[mlcb]

Maybe these array formulas

1) =INDEX($A$1:$A$4,MIN(IF($B$1:$F$4=12.2,ROW($B$1:$F$4))))

2) =MIN(IF($B$1:$F$4=12.2,COLUMN($B$1:$F$4)-COLUMN($B$1)+1))

both formulas must be confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

You can use a cell reference instead of hard coding 12.2

[kavehmj]

What if there's few same values?

Yeah this could be a problem. But i've created this example by C/P and this problem has not happened in my matrix. Because it's cell's have the values which passes from many functions.

Maybe these array formulas

1) =INDEX($A$1:$A$4,MIN(IF($B$1:$F$4=12.2,ROW($B$1:$F$4))))

2) =MIN(IF($B$1:$F$4=12.2,COLUMN($B$1:$F$4)-COLUMN($B$1)+1))

both formulas must be confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

You can use a cell reference instead of hard coding 12.2

Thanks. But unfortunately these functions dont worked. first formulas returns #REF! and the 2nd one returns the number which is not useful for me and i don't know why it returns it.
Is there a function that returns it's pure refrence? for example 12.2 is in coloumn F and row 24 in excell file. so these two function return these values:
1) find 12.2 in this matrix and return it's pure row name. (Here 24)
2) find 12.2 in this matrix and return it's column name. (Here F)


EDITED: Thank you so much. These two functions you posted here worked. I had Entered the formula in wrong way and i checkeck and fixed it. But i like to know my above two new quastion too.



And now how can i use cell reference for this problem?

[mlcb]

Thanks. And how can i use cell reference for this problem?

Say H2 houses the value of interest (12.2 above)

1) =INDEX($A$1:$A$4,MIN(IF($B$1:$F$4=$H$2,ROW($B$1:$F$4))))

2) =MIN(IF($B$1:$F$4=$H$2,COLUMN($B$1:$F$4)-COLUMN($B$1)+1))