Formula to find the column and row of a unique value within a table?

[Intermediate]

Hi,

Say I have a 3x3 grid containing numbers 1-9, populated in rows from left to right. I need two formulae that will tell me the column and the row of any number, eg. "8" = column 2, row 3.

1	2	3
4	5	6
7	8	9

<tbody>
</tbody>

Thanks in advance for any help you can offer.
Much Appreciated.

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​						row	col
2​	1​	2​	3​		8​	3​	2​
3​	4​	5​	6​
4​	7​	8​	9​

In F2 control+shift+enter, not just enter:

=IFERROR(1/(1/MIN(IF($A$2:$C$4=E2,ROW($A$2:$C$4)-ROW($A$2)+1))),"")<strike></strike>

In G2 control+shift+enter, not just enter:

=IFERROR(1/(1/MIN(IF($A$2:$C$4=E2,COLUMN($A$2:$C$4)-COLUMN($A$2)+1))),"")<strike></strike>

 

[Intermediate]

Hey Aladin,

Thanks for your quick reply. That works really well... I don't pretend to understand array formulae yet but I can cut and paste someone else's! Maybe one day!

Thanks

Intermediate.

Row\Col	A​	B​	C​	D​	E​	F​	G​
1​						row	col
2​	1​	2​	3​		8​	3​	2​
3​	4​	5​	6​
4​	7​	8​	9​

<tbody>
</tbody>

In F2 control+shift+enter, not just enter:

=IFERROR(1/(1/MIN(IF($A$2:$C$4=E2,ROW($A$2:$C$4)-ROW($A$2)+1))),"")<strike></strike>

In G2 control+shift+enter, not just enter:

=IFERROR(1/(1/MIN(IF($A$2:$C$4=E2,COLUMN($A$2:$C$4)-COLUMN($A$2)+1))),"")<strike></strike>

 

[Aladin Akyurek]

Hey Aladin,

Thanks for your quick reply. That works really well... I don't pretend to understand array formulae yet but I can cut and paste someone else's! Maybe one day!

Thanks

Intermediate.

Glad to help.