# Lookup nth non-unique value in row and return nth instance header

#### LCoops

##### New Member
I have data where individual names are listed in Column A. In Row 1 are different meeting names. Data is listed in cells B3:D6, where all the data is the number 1 indicating that a person has attended that meeting.
I have individual sheets for each person and would like the person's sheet to show which meetings they have attended.
Ie - John Smith has attended Meeting 1 and Meeting 3. Freda Fox has attended all meetings.

I cannot seem to work out the formula to get excel to lookup the name of person in Cell B1 of their individual sheet and then return the header of the first 1 it finds and then return the header of the second 1 it finds etc.

I have tried INDEX MATCH, VLOOKUP, Reverse LOOKUP but have struggled to find the correct combination. Any help would be appreciated.

#### Attachments

• Capture 1.PNG
9.7 KB · Views: 8
• Capture 2.PNG
11.9 KB · Views: 8

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### sandy666

##### Well-known Member
you can try with Power Query and Pivot Table
Power Query:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
Unpvt = Table.UnpivotOtherColumns(Source, {"Name"}, "Attribute", "Value")
in
Unpvt``````
 Name Meeting 1 Meeting 2 Meeting 3 Name Joanna Jeffires Name Freda Fox John Smith 1 1 Freda Fox 1 1 1 Attribute Attribute Paul Jones 1 Meeting 1 Meeting 1 Joanna Jeffires 1 1 Meeting 3 Meeting 2 Meeting 3

#### LCoops

##### New Member
Never used Power Query, but have used Pivot Tables. I would prefer just formulas as there might be other calculations I may need to enter later on (ie sum of hours etc.)

#### sandy666

##### Well-known Member
You've XL2016 so Power Query (Get&Transform) is built-in
but ok if you prefer formula
Have a nice day

#### Peter_SSs

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

Try this in A3 of the person's sheet, copied down (after adjusting the sheet name of the main data)

Excel Formula:
``=IFERROR(INDEX('Data Sheet'!\$1:\$1,AGGREGATE(15,6,COLUMN('Data Sheet'!B\$1:D\$1)/(INDEX('Data Sheet'!B\$2:D\$5,MATCH(B\$1,'Data Sheet'!A\$2:A\$5,0),0)=1),ROWS(A\$3:A3))),"")``

#### Habtest

##### New Member
I have data where individual names are listed in Column A. In Row 1 are different meeting names. Data is listed in cells B3:D6, where all the data is the number 1 indicating that a person has attended that meeting.
I have individual sheets for each person and would like the person's sheet to show which meetings they have attended.
Ie - John Smith has attended Meeting 1 and Meeting 3. Freda Fox has attended all meetings.

I cannot seem to work out the formula to get excel to lookup the name of person in Cell B1 of their individual sheet and then return the header of the first 1 it finds and then return the header of the second 1 it finds etc.

I have tried INDEX MATCH, VLOOKUP, Reverse LOOKUP but have struggled to find the correct combination. Any help would be appreciated.
Assuming Capture 1.png is from Sheet1, here are formulas for each individual's tab:
Book2
ABCDEFGHIJ
1Mtg 1Mtg 2Mtg 3Mtg 4Mtg 5Mtg 6Mtg 7Mtg 8Mtg 9
2John Smith11111
3Freda Fox111111
Sheet1

Book2
AB
1NameJohn Smith
3Mtg 1
4Mtg 3
5Mtg 4
6Mtg 6
7Mtg 8
John Smith
Cell Formulas
RangeFormula
A3A3=INDEX(Sheet1!\$1:\$1,1,MATCH(1,OFFSET(Sheet1!\$1:\$10,MATCH(\$B\$1,Sheet1!A:A,0)-1,0,1),0))
A4:A7A4=IFERROR(INDEX(Sheet1!\$1:\$1,1,MATCH(1,OFFSET(Sheet1!\$1:\$10,MATCH(\$B\$1,Sheet1!A:A,0)-1,MATCH(A3,Sheet1!\$1:\$1,0),1,999),0)+MATCH(A3,Sheet1!\$1:\$1,0)),"")

Cheers

Replies
5
Views
38
Replies
12
Views
60
Replies
3
Views
105
Replies
2
Views
80
Replies
1
Views
94