Look up a unique value from a matrix and return the X/Y values

[edcostas]

<embed id="xunlei_com_thunder_helper_plugin_d462f475-c18e-46be-bd10-327458d045bd" type="application/thunder_download_plugin" height="0" width="0">Hello

I have a matrix in Sheet 1:[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]P[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Q[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
The numbers in the matrix should be unique and come of the cells maybe empty.

In Sheet 2, I want to look for the numbers in Sheet 1, and return the values of the X/Y axis, like the following illustration:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]X axis[/TD]
[TD]Y axis[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]C[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]B[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]B[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]D[/TD]
[TD]P[/TD]
[/TR]
</tbody>[/TABLE]

What formula should I setup? Thanks!
Regards<embed id="xunlei_com_thunder_helper_plugin_d462f475-c18e-46be-bd10-327458d045bd" type="application/thunder_download_plugin" height="0" width="0"><embed id="xunlei_com_thunder_helper_plugin_d462f475-c18e-46be-bd10-327458d045bd" type="application/thunder_download_plugin" height="0" width="0"><embed id="xunlei_com_thunder_helper_plugin_d462f475-c18e-46be-bd10-327458d045bd" type="application/thunder_download_plugin" height="0" width="0">

 

[azumi]

Assumed your data table in ranges A1:E5 in Sheet 1 as given table attach.

and Result in Sheet 2 as given table attach:

formula for X Axis : (put in Cell B2)
=INDEX(Sheet1!$B$1:$E$1;;MAX(IF(Sheet1!$B$2:$E$5=$A2;COLUMN(Sheet1!$B$1:$E$1)-COLUMN(Sheet1!$B$1)+1)))

Formula for Y Axis: (put in Cell C2)
=INDEX(Sheet1!$A$2:$A$5;MAX(IF(Sheet1!$B$2:$E$5=$A2;ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1)))

All is Array Formula so you need to hit CTRL-SHIFT-ENTER button all together, not ENTER alone

Azumi

 

[edcostas]

Thanks Azumi, it works.

What if I would like to enhance the formula to
1) Show "Error" if there are multiple appearance of the same value in the matrix (currently it shows only one result, I don't want both as the value should be unique. The Error can serve as a check if the values are unquie.)
2) Show "Error" if the value I look for is not in the matrix, say when I look for "5" it will show Error (as a check of the total number of values in the matrix)

 

[azumi]

For X Axis :
=IF(COUNTIF(Sheet1!$B$2:$E$5,A2)=0,"Not In The List",IF(COUNTIF(Sheet1!$B$2:$E$5,A2)>1,"Not Unique",INDEX(Sheet1!$B$1:$E$1,,MAX(IF(Sheet1!$B$2:$E$5=$A2,COLUMN(Sheet1!$B$1:$E$1)-COLUMN(Sheet1!$B$1)+1)))))

For Y Axis :
=IF(COUNTIF(Sheet1!$B$2:$E$5,A2)=0,"Not In The List",IF(COUNTIF(Sheet1!$B$2:$E$5,A2)>1,"Not Unique",INDEX(Sheet1!$A$2:$A$5,MAX(IF(Sheet1!$B$2:$E$5=$A2,ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1)))))

Hope it works

Azumi

 

[edcostas]

Thanks a lot, you saved my life.