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:

	A	B	C	D
P				4
Q		2
R		3	1
S

<tbody>
</tbody>

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:

	X axis	Y axis
1	C	R
2	B	Q
3	B	R
4	D	P

<tbody>
</tbody>

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.