Formula to find the first and last cell containing data in a row

[Leydenhousen]

Hi I am looking for a formula to find the first and last cell of data in a row. This data is to determine start and end dates and needs to return data from the header row as a return. So in the example below I want to know that Job A starts in Apr 23 and finishes in Aug 23. Can you please help explain what formula I should use please?

	Jan 23	Feb 23	Mar 23	Apr 23	May 23	Jun 23	Jul 23	Aug 23	Sep 23
Job A				1000	200	30	5000	50
Job B		2000	2000	200	20000	2000
Job C			3	3	3	3	3	3	3
Job D							5	5	5

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23
2	Job A				1000	200	30	5000	50		01/04/2023	01/08/2023
3	Job B		2000	2000	200	20000	2000				01/02/2023	01/06/2023
4	Job C			3	3	3	3	3	3	3	01/03/2023	01/09/2023
5	Job D							5	5	5	01/07/2023	01/09/2023
Master

Cell Formulas
Range	Formula
K2:L5	K2	=CHOOSECOLS(FILTER($B$1:$J$1,B2:J2<>""),1,-1)
Dynamic array formulas.

 

[Leydenhousen]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1		Jan-23	Feb-23	Mar-23	Apr-23	May-23	Jun-23	Jul-23	Aug-23	Sep-23
2	Job A				1000	200	30	5000	50		01/04/2023	01/08/2023
3	Job B		2000	2000	200	20000	2000				01/02/2023	01/06/2023
4	Job C			3	3	3	3	3	3	3	01/03/2023	01/09/2023
5	Job D							5	5	5	01/07/2023	01/09/2023
Master

Cell Formulas
Range	Formula
K2:L5	K2	=CHOOSECOLS(FILTER($B$1:$J$1,B2:J2<>""),1,-1)
Dynamic array formulas.

Sorry daft question but how do I write that as a formula to *** range and return the answer to K2

 

[Fluff]

Sorry but I don't understand what you are asking.

 

[Leydenhousen]

Sorry but I don't understand what you are asking.

If I go to the cell I want the data to return in and type in

=CHOOSECOLS(FILTER($B$1:$J$1,B2:J2<>""),1,-1) it gives me a Name error

 

[Fluff]

Do you have the FILTER function?

 

[Leydenhousen]

Do you have the FILTER function?

Yes. What do I type into the cell to return the right date?

 

[Joe4]

Do you also have the CHOOSECOLS function?

 

[Leydenhousen]

Do you also have the CHOOSECOLS function?

I can see CHOOSE from the formula options but not CHOOSECOLS

 

[Fluff]

Ok, how about

=LET(f,FILTER($B$1:$J$1,B2:J2<>""),CHOOSE({1,2},INDEX(f,,1),INDEX(f,,COLUMNS(f))))