Need to extract column title if value exists in a 2d array in microsoft excel

Question

I have this 2d array table (image attached-table 1).

table 1

I want to extract column names for all tags which are marked with "x". For eg: For Tag 1, I want the values A and G returned. Similarly, for Tag 2, A, C and F should be returned. How should I do it in excel?

Thanks in anticipation.

Do you want the answers as a comma-separated list?? – Gary's Student Feb 22 '18 at 17:57 — Gary's Student, Feb 22 '18 at 17:57

score 0 · Answer 1 · answered Feb 22 '18 at 18:16

0

in I2 enter:

=MID(IF(B2="x","," & $B$1,"") & IF(C2="x","," & $C$1,"") & IF(D2="x","," & $D$1,"") & IF(E2="x","," & $E$1,"") & IF(F2="x","," & $F$1,"") & IF(G2="x","," & $G$1,"") &  IF(H2="x","," & $H$1,""),2,99)

and copy downwards:

answered Feb 22 '18 at 18:16

Gary's Student

95,722
10
59
99

Thank you. I wanted a list like the below answer. However, this helped in another way. :) – Hellboy Feb 28 '18 at 07:31

score 0 · Accepted Answer · edited Feb 22 '18 at 18:48

0

{=IFERROR(INDEX($A$1:$H$1,0,SMALL(IF(INDIRECT("$A$"&SMALL(IF($A$1:$A$5=B$7,ROW($A$1:$A$5)),1)&":$H$"&SMALL(IF($A$1:$A$5=B$7,ROW($A$1:$A$5)),1))="X",COLUMN($A$1:$H$1)),ROW(1:1))),"")}

You can then drag that down and across for all other rows. Assuming your data starts in A1, here is what it looks like:

edited Feb 22 '18 at 18:48

rollstuhlfahrer

3,988
9
25
38

answered Feb 22 '18 at 18:27

Ryan S

41
4

Thank you. This gave me the list as per my requirement. – Hellboy Feb 28 '18 at 07:31

Need to extract column title if value exists in a 2d array in microsoft excel

2 Answers2