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: 12
  • Capture 2.PNG
    Capture 2.PNG
    11.9 KB · Views: 12

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
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.)
 
Upvote 0
You've XL2016 so Power Query (Get&Transform) is built-in
but ok if you prefer formula
Have a nice day
 
Upvote 0
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))),"")
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top