Dynamic formula to pull in column headers based on column values within a row

[n64kps]

Hello,

I'm looking to create a dynamic formula to capture an employee name if they have sales associated with a workflow. Some workflows have multiple employees with sales (some have none) and I would like to uniquely identify all employees with sales in each workflow in separate rows.

I've tried XLOOKUP, INDEX, MATCH, FILTER, etc but can't seem to get the right combination of formulas to grab the employee name and move to the next name with sales in a workflow once the first employee has been identified.

Referencing the mini-sheet below, the formula should produce the following results:
A2 = Michael
A3 = Stanley
A4 = Andy
A5 = Michael
A6 = Andy
A7 = Stanley
A8 = Angela

Allocation Example.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Employee	Workflow				Workflow	Michael	Dwight	Jim	Andy	Stanley	Oscar	Angela
2		ABC123				ABC123	10				90
3		ABC123				DEF456
4		HIK789				HIK789				100
5		JKL1011				JKL1011	25			25	25		25
6		JKL1011
7		JKL1011
8		JKL1011
Sheet1

Thank you!!

 

[Cubist]

How about this? The formula below does not depends on column B, but only the table to the right.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Employee	Workflow				Workflow	Michael	Dwight	Jim	Andy	Stanley	Oscar	Angela
2	Michael	ABC123				ABC123	10				90
3	Stanley	ABC123				DEF456
4	Andy	HIK789				HIK789				100
5	Michael	JKL1011				JKL1011	25			25	25		25
6	Andy	JKL1011
7	Stanley	JKL1011
8	Angela	JKL1011
Sheet4

Cell Formulas
Range	Formula
A2:B8	A2	=HSTACK(TOCOL(IFS(G2:M5<>"",G1:M1),2),TOCOL(IFS(G2:M5<>"",F2:F5),2))
Dynamic array formulas.

 

[n64kps]

How about this? The formula below does not depends on column B, but only the table to the right.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Employee	Workflow				Workflow	Michael	Dwight	Jim	Andy	Stanley	Oscar	Angela
2	Michael	ABC123				ABC123	10				90
3	Stanley	ABC123				DEF456
4	Andy	HIK789				HIK789				100
5	Michael	JKL1011				JKL1011	25			25	25		25
6	Andy	JKL1011
7	Stanley	JKL1011
8	Angela	JKL1011
Sheet4

Cell Formulas
Range	Formula
A2:B8	A2	=HSTACK(TOCOL(IFS(G2:M5<>"",G1:M1),2),TOCOL(IFS(G2:M5<>"",F2:F5),2))
Dynamic array formulas.

@Cubist - Yes, this works for me. Thank you so much!

I had created a formula for the workflow column but this solution negates the need for that. I was able to combine sales sets from multiple tables and use VSTACK to make one consolidated table.