Lookup list of name in one colum then go along the row of that name for a value and display the date in the "header" multiple matches

[ashk0812]

Good morning I wonder if anyone can point me in the right direction I have a holiday tracker that I wish to create a lookup sheet so i enter a name and get the dates returned that they have booked.

The sheet contains a list of names in one column so i would want to match that then go along the row of that name for a few defined values (Holiday, ServiceDay) and display the date that is contained in row 7

I have included a sample sheet if this helps in my explination any better

Test Holiday Tracker.xlsx
	B	C	D										N		P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
2	2021 Holiday Tracker												Left		TOTAL PER DAY:		0	1	1	0	0			0	0	1	1	0
3															#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!	#REF!
4	Total Days Remaining: 242
5
6															Sat	Sun	Mon	Tue	Wed	Thu	Fri	Sat	Sun	Mon	Tue	Wed	Thu	Fri
7	Area	Role	Name												02/01	03/01	04/01	05/01	06/01	07/01	08/01	09/01	10/01	11/01	12/01	13/01	14/01	15/01
8	G2S	Supervisor	Name 1										3
9	G2S	Teamleader	Name 2										5
10	G2S	Associate 01	Name 3										6
11	G2S	Associate 02	Name 4										6					AA
12	G2S	Associate 03	Name 5										7													AA
13	G2S	Associate 04	Name 6										9
14	G2S	Associate 05	Name 7										10
15	G2S	Associate 06	Name 8										11
16	G2S	Associate 07	Name 9										11														FH
17	G2S	Associate 08	Name 10										13
18	G2S	Associate 09	Name 11										14
19	G2S	Associate 10	Name 12										14						SERVICE
20	G2S	Associate 11	Name 13										16											DENIED
21	G2S	Associate 12	Name 14										17
22	G2S	Associate 13	Name 15										18
23	G2S	Associate 14	Name 16										19
24	G2S	Associate 15	Name 17										20
25	G2S	Associate 16	Name 18										21
26	G2S	Associate 17	Name 19										22
27
28
Holidays

Cell Formulas
Range	Formula
B2	B2	=Setup!$D$11&" Holiday Tracker"
R2:AC2	R2	=IF(R4="SH","",IF(R4="BH","", IF(WEEKDAY(R6)=1,"", IF(WEEKDAY(R6)=7,"",COUNTIF(R8:R28,Setup!$D$27)+COUNTIF(R8:R28,Setup!$D$28)+COUNTIF(R8:R28,Setup!$D$29)+COUNTIF(R8:R28,Setup!$D$30)+COUNTIF(R8:R28,Setup!$D$31)+COUNTIF(R8:R28,Setup!$D$32)+COUNTIF(R8:R28,Setup!$D$33)+COUNTIF(R8:R28,Setup!$D$34)+COUNTIF(R8:R28,Setup!$D$35)+COUNTIF(R8:R28,Setup!$D$36)+COUNTIF(R8:R28,Setup!$D$37)))))
P3:AA3	P3	=IF(P4="", IF(P7>=$I$3, IF(WEEKDAY(P6)=1, IF(Q3="SH",O5,""), IF(WEEKDAY(P6)=2,R5, IF(WEEKDAY(P6)=3,Q5, IF(WEEKDAY(P6)=4,P5, IF(WEEKDAY(P6)=5,O5, IF(WEEKDAY(P6)=6,N5, IF(WEEKDAY(P6)=7,""))))))),P3),P4)
AB3	AB3	=IF(AB4="", IF(AB7>=$I$3, IF(WEEKDAY(AB6)=1, IF(AC3="SH",AA5,""), IF(WEEKDAY(AB6)=2,#REF!, IF(WEEKDAY(AB6)=3,AC5, IF(WEEKDAY(AB6)=4,AB5, IF(WEEKDAY(AB6)=5,AA5, IF(WEEKDAY(AB6)=6,Z5, IF(WEEKDAY(AB6)=7,""))))))),AB3),AB4)
AC3	AC3	=IF(AC4="", IF(AC7>=$I$3, IF(WEEKDAY(AC6)=1, IF(#REF!="SH",AB5,""), IF(WEEKDAY(AC6)=2,#REF!, IF(WEEKDAY(AC6)=3,#REF!, IF(WEEKDAY(AC6)=4,AC5, IF(WEEKDAY(AC6)=5,AB5, IF(WEEKDAY(AC6)=6,AA5, IF(WEEKDAY(AC6)=7,""))))))),AC3),AC4)
B4	B4	="Total Days Remaining: "&SUM(N:N)
P6:Q6	P6	=WEEKDAY(P7)
R6:AC6	R6	=R7
P7:AC7	P7	=O7+1
N8:N28	N8	=IFERROR(IF(D8="","",SUM(K8+M8+L8-COUNTIF(O8:AD8,Setup!$D$27)-COUNTIF(O8:AD8,Setup!$D$28)-COUNTIF(O8:AD8,Setup!$D$29)-COUNTIF(O8:AD8,Setup!$D$30)-COUNTIF(O8:AD8,Setup!$D$31)-COUNTIF(O8:AD8,Setup!$D$32)-COUNTIF(O8:AD8,Setup!$D$33)-COUNTIF(O8:AD8,Setup!$D$34)-COUNTIF(O8:AD8,Setup!$D$35)-COUNTIF(O8:AD8,Setup!$D$36)-COUNTIF(O8:AD8,Setup!$D$37))),(IF(D8="","",SUM(K8+M8-COUNTIF(O8:AD8,Setup!$D$27)-COUNTIF(O8:AD8,Setup!$D$28)-COUNTIF(O8:AD8,Setup!$D$29)-COUNTIF(O8:AD8,Setup!$D$30)-COUNTIF(O8:AD8,Setup!$D$31)-COUNTIF(O8:AD8,Setup!$D$32)-COUNTIF(O8:AD8,Setup!$D$33)-COUNTIF(O8:AD8,Setup!$D$34)-COUNTIF(O8:AD8,Setup!$D$35)-COUNTIF(O8:AD8,Setup!$D$36)-COUNTIF(O8:AD8,Setup!$D$37)))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B8:AD28	Expression	=$N8<0	text	NO
O8:AD28	Cell Value	contains "DENIED"	text	NO
O8:AD28	Cell Value	contains "ALLOC"	text	NO
O8:AD28	Cell	does not contain a blank value	text	NO
O6:AD28	Expression	=O$3="BH"	text	NO
O5:AD7	Expression	=O$3="Nights"	text	NO
O5:AD7	Expression	=O$3="Backs"	text	NO
O5:AD7	Expression	=O$3="Days"	text	NO
D:E	Cell Value	duplicates	text	NO
O5:AD5	Expression	=O$3="BH"	text	NO
O6:AD28	Expression	=O$3="SD"	text	NO
O5:AD5	Expression	=O$3="SD"	text	NO
O5:AD5	Expression	=O$3="SH"	text	NO
O6:AD28	Expression	=O$3="SH"	text	NO
O6:AD28	Expression	=IF(WEEKDAY(O$7)=7,TRUE)	text	NO
O6:AD28	Expression	=IF(WEEKDAY(O$7)=1,TRUE)	text	NO
O5:AD5	Expression	=IF(WEEKDAY(O$7)=7,TRUE)	text	YES
O5:AD5	Expression	=IF(WEEKDAY(O$7)=1,TRUE)	text	YES
O5:AD28,O2:P2,R2:AD2	Expression	=(O$2+0>=Setup!$D$17)	text	YES
O8:AD28	Expression	=$N8=0	text	NO
N2,N50:N1048576,N8:N28	Expression	=$N2=0	text	NO

Cells with Data Validation
Cell	Allow	Criteria
O4:AC4	List	=Setup!$F$30:$F$33
O8:AC28	List	=Setup!$D$27:$D$38

 

[Peter_SSs]

You're welcome.

 

[ashk0812]

Could i reverse this formula to give me a list of names off on a given date?

 

[Fluff]

How about

=FILTER(Holidays!D8:D30,FILTER(Holidays!P8:AC30,Holidays!P7:AC7=A2)<>"","None")

where A2 holds the date you're interested in

 

[ashk0812]

Perfect, many thanks again.

 

[Fluff]

My pleasure.

 

[ashk0812]

Sorry I need to exclude any Denied values as i understand it I need to add <>"denied" in somehere is that right

 

[Fluff]

How about

=LET(F,FILTER(Holidays!P8:AC30,Holidays!P7:AC7=A2),FILTER(Holidays!D8:D30,(F<>"")*(F<>"Denied"),"None"))

 

[ashk0812]

Works a charm I am in your debt, many thanks again.

 

[Fluff]