Help with Hlookup, with Match specific/partial text, and possibly Small formula

[dlmoore99]

I've been Googling and trying many combinations that I've seen from multiple forums for a couple of weeks now, Now I'm going to the experts. Formula in E15, Once I match a date in J1 to the dates in column S, I'm trying to find the first cell in a row that contains the text "Holiday" and return that cell if True, if False (no cells that contain "Holiday" then return "". Then in E16 do the same but for the second cell that contains "Holiday" in the same row. ie, Formula in E15: if Date in J1 is 01/13/23 and matched the Date in S14, the first cell that contains "Holiday" is X14 (C006 Holiday), then return X14 to E15. The formula in E16 will do the same but look for the second cell that contains "Holiday" AC14 (C012 Holiday) and return it to E16. I've tried many things from =INDEX(T14:AF14,MATCH(TRUE,INDEX(ISTEXT(T14:AF14),0),0)), but that returns the first cell with text T14 (C001). This works =HLOOKUP("*H*",$T$14:$AF$14,1,FALSE) to find the first cell with "Holiday" but doesn't have the Date Match, nor how to get the second "Holiday" match in the row, Not sure if a Small function or what is needed. Thank you for your help.

Carrier Schedule.xlsx
	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
12	1/11/24						C007	C009		C011
13	1/12/24	C001							C010				C026
14	1/13/24	C001				C006 Holiday			C010		C012 Holiday		C026
15	1/14/24
16	1/15/24
LACONIA

 

[AhoyNC]

Try:
Drag formula across columns as needed.

Book1
	E	F	G	H	I	J								R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1						1/13/2024
11
12															1/11/2024						C007	C009		C011
13															1/12/2024	C001							C010				C026
14															1/13/2024	C001				C006 Holiday			C010		C012 Holiday		C026
15															1/14/2024
16	C006 Holiday	C012 Holiday													1/15/2024
17
Sheet1

Cell Formulas
Range	Formula
E16:H16	E16	=IFERROR(INDEX(INDEX($T$12:$AF$16,MATCH($J$1,$S$12:$S$16,0),0),AGGREGATE(15,6,(COLUMN($T$12:$AF$12)-COLUMN($T$12)+1)/(ISNUMBER(SEARCH(" Holiday "," "&INDEX($T$12:$AF$16,MATCH($J$1,$S$12:$S$16,0),0)&" "))),COLUMNS($E$16:E16))),"")

 

[dlmoore99]

Holy cow, I never would've come up with that. I had seen the aggregate in my research but couldn't figure out how to incorporate it. Is there any way to adjust it so I can have it in column E, E15, E16, E17. If I pull it down it gives me the same answer of C006 Holiday in E15 and E16, pulling it across works but that's not how my worksheet is set up. Thank you so much.

 

[Peter_SSs]

Looks like the data you have shown is in a formal Excel table. If that table is called Table1 and the table heading in column S is "Date" then you can try this formula in E15.

=TRANSPOSE(FILTER(FILTER(Table1,Table1[Date]=J1,""),ISNUMBER(SEARCH("Holiday",FILTER(Table1,Table1[Date]=J1,""))),""))

Not sure in your Excel 2019 version if all the results will automatically appear in the rows below? If they don't, then try this one in E15 and drag down.

=IFERROR(INDEX(FILTER(FILTER(Table1,Table1[Date]=$J$1,""),ISNUMBER(SEARCH("Holiday",FILTER(Table1,Table1[Date]=$J$1,""))),""),ROWS(E$15:E15)),"")

 

[AhoyNC]

Like this?

Book1
	E	F	G	H	I	J								R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1						1/13/2024
11
12															1/11/2024						C007	C009		C011
13															1/12/2024	C001							C010				C026
14															1/13/2024	C001				C006 Holiday			C010		C012 Holiday		C026
15	C006 Holiday														1/14/2024
16	C012 Holiday														1/15/2024
17
18
Sheet1

Cell Formulas
Range	Formula
E15:E16	E15	=IFERROR(INDEX(INDEX($T$12:$AF$16,MATCH($J$1,$S$12:$S$16,0),0),AGGREGATE(15,6,(COLUMN($T$12:$AF$12)-COLUMN($T$12)+1)/(ISNUMBER(SEARCH(" Holiday "," "&INDEX($T$12:$AF$16,MATCH($J$1,$S$12:$S$16,0),0)&" "))),ROWS($E$15:$E15))),"")
G16:H16	G16	=IFERROR(INDEX(INDEX($T$12:$AF$16,MATCH($J$1,$S$12:$S$16,0),0),AGGREGATE(15,6,(COLUMN($T$12:$AF$12)-COLUMN($T$12)+1)/(ISNUMBER(SEARCH(" Holiday "," "&INDEX($T$12:$AF$16,MATCH($J$1,$S$12:$S$16,0),0)&" "))),COLUMNS($E$16:G16))),"")

 

[AhoyNC]

In my post above use formula in E15 and drag down. Formula in G16 was the old fotmula.

 

[dlmoore99]

Peter, Thank you for your answer. Yes columns S:AF are in a table, sorry I didn't say that in original post. Unfortunately Excel for Mac doesn't have the Filter function and is invalid function and I cannot use it. I do appreciate your time. I've used some of your solutions you have provided others in other projects I've done.

Ahoy, Thank you for the correction. I had tried inputting ROWS in place of the last COLUMN function after I made the reply for pulling it down, but is wasn't working till I saw the placement of the $ in the updated answer.

Thank you both once again.

 

[Peter_SSs]

Unfortunately Excel for Mac doesn't have the Filter function

Ah, sorry, I mis-read the specifications. It is available for Mac but you need 365 or 2021. It is available for Excel 2019 - but only Windows.

 

[Peter_SSs]

I don't know what column your table starts in or what any other headers might be but if f you did want to utilise the table structure you could try adapting something like this.
I have also used a helper cell (D15 for me but could be anywhere & could be hidden) to keep the formula a bit shorter.
Further, I have assumed "Holiday" would be at the right like your samples but if that is not the case that part could be replaced with an ISNUMBER(SEARCH( structure

dlmoore99.xlsm
	D	E	F				J							Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1							13/01/2024
2
10
11															Hdr1	Date	Hdr2	Hdr3	Hdr4	Hdr5	Hdr6	Hdr7	Hdr8	Hdr9	Hdr10	Hdr11	Hdr12	Hdr13	Hdr14
12															a	11/01/2024						C007	C009		C011
13															b	12/01/2024	C001							C010				C026
14															c	13/01/2024	C001				C006 Holiday			C010		C012 Holiday		C026
15	3	C006 Holiday													d	14/01/2024
16		C012 Holiday													e	15/01/2024
17
18
Sheet1

Cell Formulas
Range	Formula
D15	D15	=IFNA(MATCH(J1,Table1[Date],0),"")
E15:E18	E15	=IFERROR(INDEX(INDEX(Table1,D$15,0),AGGREGATE(15,6,(COLUMN(Table1)-COLUMN(Table1[Hdr1])+1)/(RIGHT(INDEX(Table1,D$15,0),7)="Holiday"),ROWS(E$15:E15))),"")

 

[dlmoore99]

Ahoy or Peter are you still available, I realized an error in what I needed for my code. I was looking for one specific thing and wasn't looking at the big picture (aka the whole spreadsheet) for what I actually needed. I still need that code you provided me but there needs to be more added to it for the actual desired results.