Return column header by searching multiple rows with same header

[aniruddhm]

Hello! Need the help of an expert on Index-Match.

The task at hand is to return the column header, based on the row header and its value in the row.

	Z1	Z2	Z3
S1	Q	R	A
S1	W	T	B
S1	E	Y	C
S2	R	Q	J
S2	T	W	K
S2	Y	E	L
S3	A	J	Q
S3	B	K	W
S3	C	L	E

<tbody>
</tbody>

Please note a value can only appear once in the S1/S2/S3 section, and only once under Z1/Z2/Z3, in case that is helpful.

From the above table, I want to return the column header based on the row. For example: For the following table (with column 1 and row 1 given - shown in bold), I want it to populate as below (the Z values):

	S1	S2	S3
Q	Z1	Z2	Z3
R	Z2	Z1	-

<tbody>
</tbody>

Essentially, if the bolded values are given, I want to return the Z values (i.e. column headers) based on the previous table.

I would really appreciate if you could help me out with this.

Thanks in advance!!

 

[Aladin Akyurek]

Row\Col	A​	B​	C​	D​	E​	F​	G​	H​	I​
1​		Z1	Z2	Z3			S1	S2	S3
2​	S1	Q	R	A		Q	Z1	Z2	Z3
3​	S1	W	T	B		R	Z2	Z1	-
4​	S1	E	Y	C
5​	S2	R	Q	J
6​	S2	T	W	K
7​	S2	Y	E	L
8​	S3	A	J	Q
9​	S3	B	K	W
10​	S3	C	L	E

G2, copied across and down:

=IFERROR(INDEX($B$1:$D$1,MIN(IF(IF(IF($B$2:$D$10=$F2,
    $A$2:$A$10)=IF(G$1="","",G$1),$B$1:$D$1,"")=$B$1:$D$1,
    COLUMN($B$1:$D$1)-COLUMN($B$1)+1,9.99999999999999E+307))),"-")

 

[aniruddhm]

Thanks a lot for your taking time to help Aladin. Unfortunately, when I am using this formula (in the exact syntax prescribed) it is returning "-" in all cells.

Is it simpler if the formula doesn't need to return a "-" when no result is found, a simple blank cell would work as well.

Let me know if you can help further.

Thanks again! I really appreciate it.

 

[Aladin Akyurek]

Thanks a lot for your taking time to help Aladin. Unfortunately, when I am using this formula (in the exact syntax prescribed) it is returning "-" in all cells.

Is it simpler if the formula doesn't need to return a "-" when no result is found, a simple blank cell would work as well.

Let me know if you can help further.

Thanks again! I really appreciate it.

You need to confirm the formula with control+shift+enter, not just enter. then copy across and down.

Control+shift+enter means: Press down the control and the shift keys at the same time while you hit the enter key.

 

[aniruddhm]

That works perfectly. Thank you so much, you truly are the MrExcel MVP!

 

[Aladin Akyurek]

That works perfectly. Thank you so much, you truly are the MrExcel MVP!

You are welcome.