How to use aggregate function to show first instance of value

[jc352]

	A	B	C	D
1	Name	Date	Date	Date
2	Bill			5-6-24
3	George	5-6-24
4	Adam		5-6-24

=AGGREGATE(15,6,COLUMN($B$2:$D$4)/(B$2:$D$4=DATE(2024,5,6)),ROWS($A$3:$A3))

This formula reports 2, 3 and 4 when pulled down. How do i get this formula to report 4, 2 and 3 in the order these dates are on the table?

 

[Cubist]

Try:

Book1.xlsx
	A	B	C	D	E
1	Name	Date	Date	Date
2	Bill			5/6/24	4
3	George	5/6/24			2
4	Adam		5/6/24		3
Sheet9

Cell Formulas
Range	Formula
E2:E4	E2	=TOCOL(IF(B2:D4=DATE(2024,5,6),COLUMN(B2:D4),NA()),3)
Dynamic array formulas.

 

[jc352]

Thank you, that works great. However, I need to add it in as part of another formula and I am getting a spill error with it. Basically what I am trying to do is create a list on a calendar. If the date is in column B (1), column C (2) and column D (3). The return would be:

Bill (3)
George (1)
Adam (2)

 

[Cubist]

Is this what you mean?

Book1.xlsx
	A	B	C	D	E
1	Name	Date	Date	Date
2	Bill			5/6/24	Bill (3)
3	George	5/6/24			George (1)
4	Adam		5/6/24		Adam (2)
Sheet9

Cell Formulas
Range	Formula
E2:E4	E2	=A2:A4 & " (" & TOCOL(IF(B2:D4=DATE(2024,5,6),COLUMN(B2:D4),NA()),3)-1 & ")"
Dynamic array formulas.

 

[Tetra201]

Another approach:

=MMULT(--(B2:D4=DATE(2024,5,6)),TRANSPOSE(COLUMN(B1:D1)-COLUMN(B1)+1))

 

[jc352]

What i actually need it to do is be able to indicate the type of entitlement based on the column number the date is in. If it is in column B (PTO), column C (PH) and column D (DH).

Bill (DH)
George (PTO)
Adam (PH)

Both formulas indicate the column correctly, but won't allow me to incorporate them into existing formulas to accomplish this.

 

[Tetra201]

Assuming you have "PTO", "PH", "DH" in range B1:D1

=INDEX(B1:D1,MMULT(--(B2:D4=DATE(2024,5,6)),TRANSPOSE(COLUMN(B1:D1)-COLUMN(B1)+1)))

 

[Cubist]

Book1.xlsx
	A	B	C	D	E
1	Name	Date	Date	Date
2	Bill			5/6/24	DH
3	George	5/6/24			PTO
4	Adam		5/6/24		PH
Sheet9

Cell Formulas
Range	Formula
E2:E4	E2	= CHOOSE(TOCOL(IF(B2:D4=DATE(2024,5,6),COLUMN(B2:D4),NA()),3),"","PTO","PH","DH")
Dynamic array formulas.