Using an Array formula to Pull Header information from Datasheet

[Syncert]

Hello all! This is my first post but I've been following this forum a solid 2-4 months in my journeys learning Excel and building dashboards. The resources present on this website have been of immense assistance. I've ran into quite a roadblock on my current project and was wondering if you could shed some insight.

Below is my data table:

Data Table

[TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Username[/TD]
[TD]Order #[/TD]
[TD]Category 1[/TD]
[TD]Category 2[/TD]
[TD]Category 3[/TD]
[TD]Category 4[/TD]
[TD]Category 5[/TD]
[TD]Category 6[/TD]
[TD]Category 7[/TD]
[TD]Category 1 Note[/TD]
[TD]Category 2 Note[/TD]
[TD]Category 3 Note[/TD]
[TD]Category 4 Note[/TD]
[TD]Category 5 Note[/TD]
[TD]Category 6 Note[/TD]
[TD]Category 7 Note[/TD]
[TD]Error Count[/TD]
[/TR]
[TR]
[TD]1/1/1900[/TD]
[TD]Kermit[/TD]
[TD]4516[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]Muppet[/TD]
[TD]none[/TD]
[TD]none[/TD]
[TD]none[/TD]
[TD]none[/TD]
[TD]Henson[/TD]
[TD]none[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]2/1/1900[/TD]
[TD]Gandolf[/TD]
[TD]4517[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]none[/TD]
[TD]none[/TD]
[TD]Balrog[/TD]
[TD]none[/TD]
[TD]none[/TD]
[TD]LOTR[/TD]
[TD]Moth[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2/1/1900[/TD]
[TD]Ozzy[/TD]
[TD]4518[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]none[/TD]
[TD]none[/TD]
[TD]none[/TD]
[TD]none[/TD]
[TD]none[/TD]
[TD]Ironman[/TD]
[TD]Rock[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3/1/1900[/TD]
[TD]Bowie[/TD]
[TD]4519[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]none[/TD]
[TD]none[/TD]
[TD]none[/TD]
[TD]Labryinth[/TD]
[TD]Classic[/TD]
[TD]Guitars[/TD]
[TD]RIP[/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

I've been working towards a format on the main sheet something like this. With the order number repeating the number of error counts:

Main Sheet

[TABLE="width: 500"]
<tbody>[TR]
[TD]Month[/TD]
[TD]Order #[/TD]
[TD]Error Count[/TD]
[TD]Error Type[/TD]
[TD]Error Note[/TD]
[/TR]
[TR]
[TD]1/1/1900[/TD]
[TD]4516[/TD]
[TD]2[/TD]
[TD]Category 1[/TD]
[TD]Muppet[/TD]
[/TR]
[TR]
[TD]1/1/1900[/TD]
[TD]4516[/TD]
[TD]2[/TD]
[TD]Category 6[/TD]
[TD]Henson[/TD]
[/TR]
[TR]
[TD]2/1/1900[/TD]
[TD]4517[/TD]
[TD]3[/TD]
[TD]Category 3[/TD]
[TD]Balrog[/TD]
[/TR]
[TR]
[TD]2/1/1900[/TD]
[TD]4517[/TD]
[TD]3[/TD]
[TD]Category 6[/TD]
[TD]LOTR[/TD]
[/TR]
[TR]
[TD]2/1/1900[/TD]
[TD]4517[/TD]
[TD]3[/TD]
[TD]Category 7[/TD]
[TD]Moth[/TD]
[/TR]
[TR]
[TD]2/1/1900[/TD]
[TD]4518[/TD]
[TD]2[/TD]
[TD]Category 6[/TD]
[TD]Ironman[/TD]
[/TR]
</tbody>[/TABLE]

The Order number repeating was pretty straightforward based upon an array formula of this nature

=IFERROR(INDEX(datasheet!$C$2:$J$10000,SMALL(IF(datasheet!$C$2:$J$10000=1,ROW(datasheet!$C$2:$J$10000)-MIN(ROW(datasheet!$C$2:$J$10000))+1," "),ROW()-2),1)," ")

Using the order # column I can easily index all of the non-dynamic columns onto the main page (i.e Error Count, order month, etc) by adjusting the column number in the INDEX Formula.

What I am having an issue with is getting the Error Type and Error Note columns to dynamically display the error category and error note to match in their respective rows. I've experimented with a few different formulas. Currently on the error type column I'm looking at something like this.

=INDEX(datasheet!$C$1:$J$1,MAX(IF(datasheet!$C$2:$J$10000=1,COLUMN(datasheet!$C$2:$J$10000)-COLUMN(datasheet!$C$1)+1)))

Thus far I havn't been able to toggle it properly to pull the accurate error type from the header column of the datasheet sheet table, and I'm realizing I need to tie it into the order number somehow but make it variable enough that it can be inserted into D1 of the main page

For the Error Note column I was assuming I'd be doing something like an INDEX MATCH MATCH setup referencing the header in some way. Since I'd have the error type and order number present I could easily target the row and column with an INDEX MATCH MATCH setup.

Any insight would be greatly appreciated! Love this forum and love all that you do!!!

 

[Syncert]

For the Error Type column of the main page I've discovered using this Formula

=IFERROR(INDEX(datasheet!$C$1:$J$1,MATCH(1,INDEX(datasheet!$C$2:$J$15000,MATCH($B2,datasheet!$C$2:$C$15000,0),),0))," ")

Yields the initial Category type. However it doesn't go the extra step and yield more than the first result which is problematic.

 

[Fluff]

How about
=INDEX(datasheet!$D$1:$J$1,AGGREGATE(15,6,(COLUMN(datasheet!$D$1:$J$1)-COLUMN(datasheet!$D$1)+1)/((datasheet!$C$2:$C$5=B2)*(datasheet!$D$2:$J$5=1)),COUNTIF($B$2:$B2,$B2)))

 

[Syncert]

OMG it works. I can't thank you enough this has saved me so much time. I've never used the AGGREGATE function before in this fashion. This helping hand has shed a lot of insight into new ways to combine functions.

 

[Fluff]

You're welcome & thanks for the feedback