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

LCoops

New Member
Joined
Sep 8, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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
    Capture 1.PNG
    9.7 KB · Views: 8
  • Capture 2.PNG
    Capture 2.PNG
    11.9 KB · Views: 8

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
7,417
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
NameMeeting 1Meeting 2Meeting 3NameJoanna JeffiresNameFreda Fox
John Smith11
Freda Fox111AttributeAttribute
Paul Jones1Meeting 1Meeting 1
Joanna Jeffires11Meeting 3Meeting 2
Meeting 3
 

LCoops

New Member
Joined
Sep 8, 2020
Messages
2
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Oct 24, 2015
Messages
7,417
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
Joined
May 28, 2005
Messages
47,493
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 30, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
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
2Topic Heading
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,789
Messages
5,574,303
Members
412,586
Latest member
Medhum
Top