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

 

[Peter_SSs]

Well, tell us what you actually need.
(BTW, It doesn't need to be either AhoyNC or me to answer - anybody might be able to help )

 

[dlmoore99]

I added more of my spreadsheet so you can see what I'm for got. I need to incorporate the formula I asked for the the original post with the formula I have in E3:E14. As you can see in the spreadsheet that TYO appears twice E3 and E6 when E6 should say C006 Holiday, Same goes for PRICE in E10 and E12 (E12 should be C012 Holiday. I hope I'm explaining it well enough. It's way past my bed time.

Carrier Schedule.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1								Sat		01/13/2024						Name	RTE		Date	C001	C002	C004	C005	C006	C007	C009	C010	C011	C012	C013	C026	T6
2	Route #	A/L	S/L	Other	Name	OT	U-SETS	1	2	3	4	5	6			FOOTE	C001		1/1/24
3	C001				TYO		PRICE	9	10	12	5	2	NS			BRUNELLE	C002		1/2/24													T6
4	C002				BRUNELLE		TYO	11	1	6	13	4	NS			ANDERSON	C004		1/3/24						C007	C009		C011
5	C004				ANDERSON		Non T6 Routes	7	26							MOORE	C005		1/4/24	C001							C010				C026
6	C005				MOORE		JON - MEDICAL									TAVANO	C006		1/5/24					C006					C012
7	C006				TYO		PTF'S									FINNIGAN	C007		1/6/24					C006					C012
8	C007				FINNIGAN		Damian									OLINGER	C009		1/7/24
9	C009				OLINGER		Merideth									DORE	C010		1/8/24				C005							C013
10	C010				PRICE		Aimee									HUME	C011		1/9/24		C002	C004
11	C011				HUME		Mike									PETERS	C012		1/10/24													T6
12	C012				PRICE											CARMAGANI	C013		1/11/24						C007	C009		C011
13	C013				CARMAGANI		COLLECTIONS:									TARBELE	C026		1/12/24	C001							C010				C026
14	C026				PTF:		All:									PRICE	T6		1/13/24	C001				C006 Holiday			C010		C012 Holiday		C026
15					C006 Holiday		Outside:									TYO	T6		1/14/24
16					C012 Holiday		Downtown:												1/15/24
17																			1/16/24				C005							C013
18							Run:												1/17/24		C002	C004
19																			1/18/24													T6
20																			1/19/24						C007	C009		C011
21								Sun		01/14/2024						US Holidays	Holiday Name		1/20/24						C007	C009		C011
22	Route #	A/L	S/L	Other	Name	OT	U-SETS	1	2	3	4	5	6			01-Jan-24	New Year's		1/21/24
LACONIA

Cell Formulas
Range	Formula
H1,H21	H1	=TEXT(J1, "ddd")
E3	E3	=IF(COUNTIF(Table4[US Holidays],$J$1),"Holiday",IF(INDEX($T$2:$T$732,MATCH($J$1,$S$2:$S$732,0))="",INDEX($P$2:$P$18,MATCH($A3,$Q$2:$Q$18,0)),"TYO"))
E4	E4	=IF(COUNTIF(Table4[US Holidays],$J$1),"Holiday",IF(INDEX($U$2:$U$732,MATCH($J$1,$S$2:$S$732,0))="",INDEX($P$2:$P$18,MATCH($A4,$Q$2:$Q$18,0)),"PRICE"))
E5	E5	=IF(COUNTIF(Table4[US Holidays],$J$1),"Holiday",IF(INDEX($V$2:$V$732,MATCH($J$1,$S$2:$S$732,0))="",INDEX($P$2:$P$18,MATCH($A5,$Q$2:$Q$18,0)),"TYO"))
E6	E6	=IF(COUNTIF(Table4[US Holidays],$J$1),"Holiday",IF(INDEX($W$2:$W$732,MATCH($J$1,$S$2:$S$732,0))="",INDEX($P$2:$P$18,MATCH($A6,$Q$2:$Q$18,0)),"PRICE"))
E7	E7	=IF(COUNTIF(Table4[US Holidays],$J$1),"Holiday",IF(INDEX($X$2:$X$732,MATCH($J$1,$S$2:$S$732,0))="",INDEX($P$2:$P$18,MATCH($A7,$Q$2:$Q$18,0)),"TYO"))
E8	E8	=IF(COUNTIF(Table4[US Holidays],$J$1),"Holiday",IF(INDEX($Y$2:$Y$732,MATCH($J$1,$S$2:$S$732,0))="",INDEX($P$2:$P$18,MATCH($A8,$Q$2:$Q$18,0)),"PTF:"))
E9	E9	=IF(COUNTIF(Table4[US Holidays],$J$1),"Holiday",IF(INDEX($Z$2:$Z$732,MATCH($J$1,$S$2:$S$732,0))="",INDEX($P$2:$P$18,MATCH($A9,$Q$2:$Q$18,0)),"PRICE"))
E10	E10	=IF(COUNTIF(Table4[US Holidays],$J$1),"Holiday",IF(INDEX($AA$2:$AA$732,MATCH($J$1,$S$2:$S$732,0))="",INDEX($P$2:$P$18,MATCH($A10,$Q$2:$Q$18,0)),"PRICE"))
E11	E11	=IF(COUNTIF(Table4[US Holidays],$J$1),"Holiday",IF(INDEX($AB$2:$AB$732,MATCH($J$1,$S$2:$S$732,0))="",INDEX($P$2:$P$18,MATCH($A11,$Q$2:$Q$18,0)),"TYO"))
E12	E12	=IF(COUNTIF(Table4[US Holidays],$J$1),"Holiday",IF(INDEX($AC$2:$AC$732,MATCH($J$1,$S$2:$S$732,0))="",INDEX($P$2:$P$18,MATCH($A12,$Q$2:$Q$18,0)),"PRICE"))
E13	E13	=IF(COUNTIF(Table4[US Holidays],$J$1),"Holiday",IF(INDEX($AD$2:$AD$732,MATCH($J$1,$S$2:$S$732,0))="",INDEX($P$2:$P$18,MATCH($A13,$Q$2:$Q$18,0)),"TYO"))
E14	E14	=IF(COUNTIF(Table4[US Holidays],$J$1),"Holiday",IF(INDEX($AE$2:$AE$732,MATCH($J$1,$S$2:$S$732,0))="",INDEX($P$2:$P$18,MATCH($A14,$Q$2:$Q$18,0)),"PTF:"))
E15:E17	E15	=IFERROR(INDEX(INDEX($T$2:$AF$732,MATCH($J$1,$S$2:$S$732,0),0),AGGREGATE(15,6,(COLUMN($T$2:$AF$2)-COLUMN($T$2)+1)/(ISNUMBER(SEARCH("Holiday"," "&INDEX($T$2:$AF$732,MATCH($J$1,$S$2:$S$732,0),0)&" "))),ROWS($E$15:$E15))),"")
J21	J21	=($J$1+1)

Named Ranges
Name	Refers To	Cells
_C001	=LACONIA!$Q$3:$Q$18	E3:E14
Name	=LACONIA!$P$2:$P$15	E3:E14

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
H2	Expression	=WEEKDAY($J$1)=2	text	NO
I2	Expression	=WEEKDAY($J$1)=3	text	NO
J2	Expression	=WEEKDAY($J$1)=4	text	NO
K2	Expression	=WEEKDAY($J$1)=5	text	NO
L2	Expression	=WEEKDAY($J$1)=6	text	NO
M2	Expression	=WEEKDAY($J$1)=7	text	NO
H22	Expression	=WEEKDAY($J$21)=2	text	NO
I22	Expression	=WEEKDAY($J$21)=3	text	NO
J22	Expression	=WEEKDAY($J$21)=4	text	NO
K22	Expression	=WEEKDAY($J$21)=5	text	NO
L22	Expression	=WEEKDAY($J$21)=6	text	NO
M22	Expression	=WEEKDAY($J$21)=7	text	NO

 

[dlmoore99]

I don't need anything in E15:E19.

 

[Peter_SSs]

1. I don't see any logic re the 'fall-back' values of "TYO", "PRICE" & "PTF:" so that leads to a rather messy mix of different formulas down column E - but that is what you had anyway.
   
   
2. I don't know what your table names are apart from 'Table4' being the small holiday one in P21:Q22. In my sample below the table in P1:Q18 is 'Table2' and the one in S1:AF22 is 'Table5'If my formulas are what you want, you will need to either adjust the table names in all the formulas or else rearrange your table names to match mine.

See if this is what you want.
Rather than trying to put all the different formulas into column E it might be easier to copy E3 all the way down and then just swap "TYO" to "PRICE" or "PTF:" in the relevant rows since that is the only difference between the various formulas.

dlmoore99.xlsm
	A				E					J					O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1										13/01/2024						Name	RTE		Date	C001	C002	C004	C005	C006	C007	C009	C010	C011	C012	C013	C026	T6
2	Route #				Name					3						FOOTE	C001		1/01/2024
3	C001				TYO					12						BRUNELLE	C002		2/01/2024													T6
4	C002				BRUNELLE					6						ANDERSON	C004		3/01/2024						C007	C009		C011
5	C004				ANDERSON											MOORE	C005		4/01/2024	C001							C010				C026
6	C005				MOORE											TAVANO	C006		5/01/2024					C006					C012
7	C006				C006 Holiday											FINNIGAN	C007		6/01/2024					C006					C012
8	C007				FINNIGAN											OLINGER	C009		7/01/2024
9	C009				OLINGER											DORE	C010		8/01/2024				C005							C013
10	C010				PRICE											HUME	C011		9/01/2024		C002	C004
11	C011				HUME											PETERS	C012		10/01/2024													T6
12	C012				C012 Holiday											CARMAGANI	C013		11/01/2024						C007	C009		C011
13	C013				CARMAGANI											TARBELE	C026		12/01/2024	C001							C010				C026
14	C026				PTF:											PRICE	T6		13/01/2024	C001				C006 Holiday			C010		C012 Holiday		C026
15																TYO	T6		14/01/2024
16																			15/01/2024
17																			16/01/2024				C005							C013
18																			17/01/2024		C002	C004
19																			18/01/2024													T6
20																			19/01/2024						C007	C009		C011
21																US Holidays	Holiday Name		20/01/2024						C007	C009		C011
22	Route #				Name					3						1/01/2024	New Year's		21/01/2024
23
Sheet2

Cell Formulas
Range	Formula
E3,E5,E7,E11,E13	E3	=IF(COUNTIF(Table4[US Holidays],$J$1),"Holiday",IF(INDEX(Table5,MATCH(J$1,Table5[Date],0),MATCH(A3,Table5[#Headers],0))="",INDEX(Table2[Name],MATCH(A3,Table2[RTE],0),1),IF(INDEX(Table5,MATCH(J$1,Table5[Date],0),MATCH(A3,Table5[#Headers],0))=A3,"TYO",INDEX(Table5,MATCH(J$1,Table5[Date],0),MATCH(A3,Table5[#Headers],0)))))
E4,E6,E9:E10,E12	E4	=IF(COUNTIF(Table4[US Holidays],$J$1),"Holiday",IF(INDEX(Table5,MATCH(J$1,Table5[Date],0),MATCH(A4,Table5[#Headers],0))="",INDEX(Table2[Name],MATCH(A4,Table2[RTE],0),1),IF(INDEX(Table5,MATCH(J$1,Table5[Date],0),MATCH(A4,Table5[#Headers],0))=A4,"PRICE",INDEX(Table5,MATCH(J$1,Table5[Date],0),MATCH(A4,Table5[#Headers],0)))))
E8,E14	E8	=IF(COUNTIF(Table4[US Holidays],$J$1),"Holiday",IF(INDEX(Table5,MATCH(J$1,Table5[Date],0),MATCH(A8,Table5[#Headers],0))="",INDEX(Table2[Name],MATCH(A8,Table2[RTE],0),1),IF(INDEX(Table5,MATCH(J$1,Table5[Date],0),MATCH(A8,Table5[#Headers],0))=A8,"PTF:",INDEX(Table5,MATCH(J$1,Table5[Date],0),MATCH(A8,Table5[#Headers],0)))))

 

[dlmoore99]

In regards to the 'fall-back' values of "TYO", "PRICE" & "PTF:", they are the people who will cover certain routes. Price will cover the routes in G3:M3, TYO will cover the routes in G4:M4, PTF's will cover things not covered by PRICE & TYO. The spaces are small in G3:M5, so I didn't use the full route names found in A3:A14. PTF's, G8:G11 don't have a set daily work schedule and names are written in E3:E14 on a daily basis to cover whatever needs to be covered. Would it be better to create another table for Price & Tyo for the routes they cover. Table 2 T3:AF732 contains the routes Day off and that will be covered by either Price or Tyo. Not sure how to explain it better. Would I be better if I put the names in Table 2 T3:AF732, and somehow Index/Match instead of looking for blanks. I would still need the Holiday part in the original post.

 

[Peter_SSs]

Not sure that I understand all of that but I think adding a lookup table to get TYP/PRICE etc would be a good idea. I have attempted that in P24:Q34 below and then a single formula can be copied down column E. Is that any better?
BTW, I have copied my previous sheet so the table names have changed
P:Q top table is Table27
P:Q middle table (holidays) is Table48
P:Q bottom table (new table) is tblCover
S:AF table is Table 59
You will have to adjust my formula to match your table names.

dlmoore99.xlsm
	A				E					J					O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1										13/01/2024						Name	RTE		Date	C001	C002	C004	C005	C006	C007	C009	C010	C011	C012	C013	C026	T6
2	Route #				Name											FOOTE	C001		1/01/2024
3	C001				TYO											BRUNELLE	C002		2/01/2024													T6
4	C002				BRUNELLE											ANDERSON	C004		3/01/2024						C007	C009		C011
5	C004				ANDERSON											MOORE	C005		4/01/2024	C001							C010				C026
6	C005				MOORE											TAVANO	C006		5/01/2024					C006					C012
7	C006				C006 Holiday											FINNIGAN	C007		6/01/2024					C006					C012
8	C007				FINNIGAN											OLINGER	C009		7/01/2024
9	C009				OLINGER											DORE	C010		8/01/2024				C005							C013
10	C010				PRICE											HUME	C011		9/01/2024		C002	C004
11	C011				HUME											PETERS	C012		10/01/2024													T6
12	C012				C012 Holiday											CARMAGANI	C013		11/01/2024						C007	C009		C011
13	C013				CARMAGANI											TARBELE	C026		12/01/2024	C001							C010				C026
14	C026				PTF:											PRICE	T6		13/01/2024	C001				C006 Holiday			C010		C012 Holiday		C026
15																TYO	T6		14/01/2024
16																			15/01/2024
17																			16/01/2024				C005							C013
18																			17/01/2024		C002	C004
19																			18/01/2024													T6
20																			19/01/2024						C007	C009		C011
21																US Holidays	Holiday Name		20/01/2024						C007	C009		C011
22	Route #				Name					3						1/01/2024	New Year's		21/01/2024
23
24																Route	Cover
25																C001	TYO
26																C002	PRICE
27																C004	TYO
28																C005	PRICE
29																C006	TYO
30																C009	PRICE
31																C010	PRICE
32																C011	TYO
33																C012	PRICE
34																C013	TYO
35
Sheet3

Cell Formulas
Range	Formula
E3:E14	E3	=IF(COUNTIF(Table48[US Holidays],$J$1),"Holiday",IF(INDEX(Table59,MATCH(J$1,Table59[Date],0),MATCH(A3,Table59[#Headers],0))="",INDEX(Table27[Name],MATCH(A3,Table27[RTE],0),1),IF(INDEX(Table59,MATCH(J$1,Table59[Date],0),MATCH(A3,Table59[#Headers],0))=A3,IFNA(VLOOKUP(A3,tblCover,2,0),"PTF:"),INDEX(Table59,MATCH(J$1,Table59[Date],0),MATCH(A3,Table59[#Headers],0)))))

 

[dlmoore99]

That works great, What if I just add the PTF and the 2 routes C007 & C026 to the cover table and adjust the IF statement. Wouldn't that make formula shorter?

 

[Peter_SSs]

That works great, What if I just add the PTF and the 2 routes C007 & C026 to the cover table and adjust the IF statement. Wouldn't that make formula shorter?

Sure, if they are two fixed substitutions. It's just that the following sounded to me like "PTF:" was yet another fall-back position

In regards to the 'fall-back' values of "TYO", "PRICE" & "PTF:", they are the people who will cover certain routes. Price will cover the routes in G3:M3, TYO will cover the routes in G4:M4, PTF's will cover things not covered by PRICE & TYO.

The formula only gets marginally shorter though.

dlmoore99.xlsm
	A				E					J					O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
1										13/01/2024						Name	RTE		Date	C001	C002	C004	C005	C006	C007	C009	C010	C011	C012	C013	C026	T6
2	Route #				Name											FOOTE	C001		1/01/2024
3	C001				TYO											BRUNELLE	C002		2/01/2024													T6
4	C002				BRUNELLE											ANDERSON	C004		3/01/2024						C007	C009		C011
5	C004				ANDERSON											MOORE	C005		4/01/2024	C001							C010				C026
6	C005				MOORE											TAVANO	C006		5/01/2024					C006					C012
7	C006				C006 Holiday											FINNIGAN	C007		6/01/2024					C006					C012
8	C007				FINNIGAN											OLINGER	C009		7/01/2024
9	C009				OLINGER											DORE	C010		8/01/2024				C005							C013
10	C010				PRICE											HUME	C011		9/01/2024		C002	C004
11	C011				HUME											PETERS	C012		10/01/2024													T6
12	C012				C012 Holiday											CARMAGANI	C013		11/01/2024						C007	C009		C011
13	C013				CARMAGANI											TARBELE	C026		12/01/2024	C001							C010				C026
14	C026				PTF:											PRICE	T6		13/01/2024	C001				C006 Holiday			C010		C012 Holiday		C026
15																TYO	T6		14/01/2024
16																			15/01/2024
17																			16/01/2024				C005							C013
18																			17/01/2024		C002	C004
19																			18/01/2024													T6
20																			19/01/2024						C007	C009		C011
21																US Holidays	Holiday Name		20/01/2024						C007	C009		C011
22	Route #				Name					3						1/01/2024	New Year's		21/01/2024
23
24																Route	Cover
25																C001	TYO
26																C002	PRICE
27																C004	TYO
28																C005	PRICE
29																C006	TYO
30																C009	PRICE
31																C010	PRICE
32																C011	TYO
33																C012	PRICE
34																C013	TYO
35																C007	PTF:
36																C026	PTF:
Sheet3

Cell Formulas
Range	Formula
E3:E14	E3	=IF(COUNTIF(Table48[US Holidays],$J$1),"Holiday",IF(INDEX(Table59,MATCH(J$1,Table59[Date],0),MATCH(A3,Table59[#Headers],0))="",INDEX(Table27[Name],MATCH(A3,Table27[RTE],0),1),IF(INDEX(Table59,MATCH(J$1,Table59[Date],0),MATCH(A3,Table59[#Headers],0))=A3,VLOOKUP(A3,tblCover,2,0),INDEX(Table59,MATCH(J$1,Table59[Date],0),MATCH(A3,Table59[#Headers],0)))))