Earliest date in row if the text in the adjacent columns matches specific

[kcmuppet]

I'm trying to find the earliest date in row across a range of columns if the text in the column immediate left of the matches words in a specific list

I have a table (too large to post here), that contains columns with headings including like these:

"Q1Task", "Q1TaskDate" .... "Q58Task", "Q58TaskDate", with text in the columns ending *Task and dates in those ending *TaskDate,

I can successfully count the number of times specific text from a list in a named range (called "lst_These_Tasks") appears in a given row using

=SUM(COUNTIF(Data_Table[@[Q1Task]:[Q58TaskDate],"*"&lst_These_Tasks&"*"))

, and I can find the earliest task date using

=MINIFS(Data_Table[@[Q1Task]:[Q58TaskDate],Data_Table[#Headers],[Data_Table[@[Q1Task]:[Q58TaskDate]],"*TaskDate"))

, so I thought combing the two might work, but I'm not getting anywhere. Help would be greatly appreciated!

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Q1Task	Q1TaskDate	Q2Task	Q2TaskDate	Q3Task	Q3TaskDate	Q4Task	Q4TaskDate	Earliest Matching Task	Expected Result		lst_These_Tasks
2	This	01 Jan 23	Other	01 Dec 22	That	02 Feb 23				01 Jan 23		This
3			This	02 Nov 22	This	04 Jul 22				02 Nov 22		That
4							Other	09 Dec 22
5	02 Jan	Other		Other	This	15 Mar 23				15 Mar 23
Sheet1

 

[Fluff]

How about

=MIN(MINIFS(Data_Table[@[Q1Task]:[Q58Task],Data_Table[@[Q1TaskDate]:[Q58TaskDate],"*"&lst_These_Tasks&"*"))

 

[kcmuppet]

How about

=MIN(MINIFS(Data_Table[@[Q1Task]:[Q58Task],Data_Table[@[Q1TaskDate]:[Q58TaskDate],"*"&lst_These_Tasks&"*"))

It didn't accept that as a formula.
I changed it to: =MIN(MINIFS(Data_Table[@[Q1Task]:[Q4Task]],Data_Table[@[Q1TaskDate]:[Q4TaskDate]],"*"&lst_These_Tasks&"*")) ...but that gave me just zeros:

Book3.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Q1Task	Q1TaskDate	Q2Task	Q2TaskDate	Q3Task	Q3TaskDate	Q4Task	Q4TaskDate	Earliest Matching Task	Expected Result		lst_These_Tasks
2	This	01 Jan 23	Other	01 Dec 22	That	02 Feb 23			0	01 Jan 23		This
3			This	02 Nov 22	This	04 Jul 22			0	02 Nov 22		That
4							Other	09 Dec 22	0
5	02 Jan	Other		Other	This	15 Mar 23			0	15 Mar 23
Sheet1

Cell Formulas
Range	Formula
I2:I5	I2	=MIN(MINIFS(Data_Table[@[Q1Task]:[Q4Task]],Data_Table[@[Q1TaskDate]:[Q4TaskDate]],"*"&lst_These_Tasks&"*"))

Named Ranges
Name	Refers To	Cells
lst_These_Tasks	=Sheet1!$L$2:$L$3	I2:I5

 

[Fluff]

Are you using 365?

 

[kcmuppet]

Are you using 365?

Yes, it looks like what is happening is that the MINFS returns a vertical array of the lowest matched date for each match but includes zero for no matches. AGGREGATE doesn't seem to work instead of MIN because zero isn't an error value...

... so testing

=MINIFS(
MINIFS(Data_Table[@[Q1Task]:[Q4Task]],Data_Table[@[Q1TaskDate]:[Q4TaskDate]],"*"&lst_These_Tasks&"*"),
MINIFS(Data_Table[@[Q1Task]:[Q4Task]],Data_Table[@[Q1TaskDate]:[Q4TaskDate]],"*"&lst_These_Tasks&"*")
,">0")

..but that doesn't seem to parse

 

[Fluff]

Ok, how about

=LET(m,MINIFS(Data_Table[@[Q1TaskDate]:[Q4TaskDate]],Data_Table[@[Q1Task]:[Q4Task]],lst_These_Tasks),IFERROR(MIN(FILTER(m,m<>0)),""))

 

[kcmuppet]

Ok, how about

=LET(m,MINIFS(Data_Table[@[Q1TaskDate]:[Q4TaskDate]],Data_Table[@[Q1Task]:[Q4Task]],lst_These_Tasks),IFERROR(MIN(FILTER(m,m<>0)),""))

Fantastic... thank you (even corrects J3 in my example, sorry if that caused confusion!)

So let's see:

The LET sets m to be the array produced by MINIFS(Data_Table[@[Q1TaskDate]:[Q4TaskDate]],Data_Table[@[Q1Task]:[Q4Task]],lst_These_Tasks),
then in (MIN(FILTER(m,m<>0)), FILTER produces and array of the results of m where m doesn't equal 0, and is wrapped with MIN to supply only the smallest. Very smart,

The bit I'm still struggling with is the ranges for the MINIFS function. I don't really understand why the whole range of columns in each of the MINIFS arguments doesn't work but the subsets in the working formula do work - .i.e.

[Q1TaskDate]:[Q4TaskDate] doesn't just have dates (its effectively all columns B:H)
Q1Task]:[Q4Task]] doesn't just have tasks (its effectively all columns A:G)

So why does it work, where [Q1Task]:[Q4TaskDate]] doesn't?

 

[Fluff]

The two ranges are offset by one column, so if any column matches your list the minifs will return the next column