Formula to find first blank cell and return another value

[Jessica553]

Hi all, I'm looking for a formula to find the first blank cell in the row and then return the column title. Essentially it's trying to find who has attended the meeting from minutes entered and we are trying to find the first time that they have attended (IE when they joined) and then their last one (When they left) I have tried Index/Match, IsBlank, Vlookup etc but the data set is going to be over many years and can't get it to return the date in Row 2 as opposed to just the 1 in the range.

 

[arthurbr]

Deleted by author

 

[Cubist]

Note the last meeting requires an additional blank cell. Try:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		First Meeting	Last Meeting	1/3/24	1/4/24	1/5/24	1/6/24	1/7/24	1/8/24	1/9/24	1/10/24	1/11/24
2	Bob	1/3/24	1/8/24		1	1	1	1
3	Sally	1/7/24	1/10/24						1	1
4	Fred	1/7/24	1/11/24						1		1
5	George	1/5/24	1/8/24				1	1
Sheet3

Cell Formulas
Range	Formula
B2:B5	B2	=INDEX($D$1:$L$1,MATCH(1,D2:L2,0)-1)
C2:C5	C2	=LOOKUP(2,1/(D2:L2=1),$E$1:$M$1)

 

[Islam Mamdouh Ali]

Assuming that the dates begin from (D1) & ends (L1)

You can use the following formulas:

First meeting: =XLOOKUP(TRUE,NOT(ISBLANK(D2:L2)),$D$1:$L$1)


Last Meeting: =LOOKUP(2,1/(D2:L2<>""),$D$1:$L$1)

Hope I could assist you

 

[Fluff]

@Islam Mamdouh Ali the OP's profile shows 2010 & so does not have Xlookup.