Returning column header corresponding to matched value

Question

need some help here.. I am looking to retrieve Gender from Sheet 2 corresponding to the name in Sheet 1.

• Step 1 - Match the name in sheet 1 to sheet 2 (not all names in sheet 1 will be in sheet 2, mark NA for non matching names)
• Step 2 - Look for the corresponding gender in sheet 2.
• Step 3 - Retrieve the column header or the last number in the column header (1,2,3...6)

Sheet 1

Name	Gender
w	???
e
r
t
y
u
i
q
w
e
r

Sheet 2

Name	Male 1	Female 2	other 3	other 4	other 5	Do not know 6
w	1	0	0	0	0	0
a	0	0	0	0	0	1
q	1	0	0	0	0	0
r	0	1	0	0	0	0
e	1	0	0	0	0	0
t	0	0	0	0	1	0
y	0	0	0	0	0	1
u	0	1	0	0	0	0

Answer 1

with Office 365 we can use FILTER:

=IFERROR(FILTER($F$1:$K$1,INDEX($F$2:$K$9,MATCH(A2,$E$2:$E$9,0),0)=1),"No Match")

---

With older versions we can use another INDEX/MATCH:

=IFERROR(INDEX($F$1:$K$1,MATCH(1,INDEX($F$2:$K$9,MATCH(A2,$E$2:$E$9,0),0),0)),"No Match")