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.

[TABLE="class: grid, width: 100, align: left"]
<tbody>[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[/TR]
</tbody>[/TABLE]







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

 

[Aladin Akyurek]

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][/tr]
[tr][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td]row[/td][td]col[/td][/tr]


[tr][td]

2​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td][/td][td]

8​

[/td][td]

3​

[/td][td]

2​

[/td][/tr]


[tr][td]

3​

[/td][td]

4​

[/td][td]

5​

[/td][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]


[tr][td]

4​

[/td][td]

7​

[/td][td]

8​

[/td][td]

9​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]row[/TD]
[TD]col[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

1​

[/TD]
[TD]

2​

[/TD]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

8​

[/TD]
[TD]

3​

[/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

4​

[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

7​

[/TD]
[TD]

8​

[/TD]
[TD]

9​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.