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 |
CodePudding user response：
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")
