Help with Lookup

[Dazzybeeguy]

Hi - I want to add a formula in F5 that looks at the name in E5 and also the date in E2 and extract the data for the dates from F4:L4 so therefore F5:L5 would populate with

WFH(M)

EDO

ES

RW

ES

SRD

WFH(E)

I'm stumped so would appreciate some help.

Thanks

Book2
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Start Date
2	07/01/2025	Tue	Wed	Thu	Fri	Sat	Sun	Mon
3	07-01-25
4	Person Drop Down	07-Jan	08-Jan	09-Jan	10-Jan	11-Jan	12-Jan	13-Jan
5	P 8
6	P 13	ES	RW	ES	SRD	ES	ES	RW
7
8	Teams	Staff	Grade	Sun 05 Jan	Mon 06 Jan	Tue-07-Jan	Wed-08-Jan	Thu-09-Jan	Fri-10-Jan	Sat-11-Jan	Sun-12-Jan	Mon-13-Jan	Tue-14-Jan	Wed-15-Jan	Thu-16-Jan	Fri-17-Jan	Sat-18-Jan	Sun-19-Jan	Mon-20-Jan	Tue-21-Jan	Wed-22-Jan	Thu-23-Jan
9	Team 1	P 1	A	ES	EDO	ES	RW	ES	SRD	ES	ES	EDO	ES	RW	ES	SRD	ES	RW	ES	SRD	ES	ES
10	Team 2	P 2	A	TOIL	WFH(E)	WFH(M)	EDO	ES	RW	ES	SRD	WFH(E)	WFH(M)	WFH(L)	SL	WI	WFH(M)	EDO	ES	RW	ES	SRD
11	Team 3	P 3	B	AL	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL	WI	RW	ES	SRD	ES	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL	WI
12	Team 4	P 4	B	ES	EDO	ES	RW	ES	SRD	ES	ES	EDO	ES	RW	ES	SRD	ES	RW	ES	SRD	ES	ES
13	Team 5	P 5	A	TOIL	WFH(E)	WFH(M)	EDO	ES	RW	ES	SRD	WFH(E)	WFH(M)	WFH(L)	SL	WI	ES	RW	ES	SRD	ES	ES
14	Team 6	P 6	A	AL	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL	WI	RW	ES	SRD	ES	ES	WFH(M)	EDO	ES	RW	ES	SRD
15	Team 7	P 7	B	ES	EDO	ES	RW	ES	SRD	XX	ES	EDO	ES	RW	ES	SRD	TOIL	WFH(E)	WFH(M)	WFH(L)	SL	WI
16	Team 8	P 8	B	TOIL	WFH(E)	WFH(M)	EDO	ES	RW	ES	SRD	WFH(E)	WFH(M)	WFH(L)	SL	WI	XX	RW	ES	SRD	ES	ES
17	Team 9	P 9	A	AL	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL	WI	RW	ES	SRD	ES	ES	WFH(M)	ES	RW	ES	SRD	ES
18	Team 10	P 10	A	ES	EDO	ES	RW	ES	SRD	ES	ES	EDO	ES	RW	ES	SRD	TOIL	WFH(M)	EDO	ES	RW	ES
19	Team 11	P 11	B	TOIL	WFH(E)	WFH(M)	EDO	ES	RW	ES	SRD	WFH(E)	WFH(M)	WFH(L)	SL	WI	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL
20	Team 12	P 12	B	AL	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL	WI	TOIL	XX	ES	RW	ES	WFH(M)	ES	RW	ES	SRD	ES
21	Team 13	P 13	A	ES	EDO	ES	RW	ES	SRD	ES	ES	RW	ES	SRD	ES	ES	ES	WFH(M)	EDO	ES	RW	ES
22	Team 14	P 14	A	TOIL	WFH(E)	WFH(M)	EDO	ES	RW	ES	SRD	ES	ES	ES	ES	ES	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL
23	Team 15	P 15	B	AL	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL	WI	ES	ES	ES	ES	ES	ES	ES	RW	ES	SRD	ES
24	Team 16	P 16	B	ES	ES	ES	ES	ES	ES	AL	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL	WI	WFH(M)	EDO	ES	RW	ES
Sheet1

Cell Formulas
Range	Formula
F2:L2	F2	=F4
E3	E3	=TEXT(E2,"dd-mm-yy")
F4	F4	=StartDate
G4:L4	G4	=F4+1

Named Ranges
Name	Refers To	Cells
StartDate	=Sheet1!$E$2	F4, E3

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O8,V8,AC8,AJ8	Dates Occurring	today	text	NO
E5:E6	Cell Value	contains "vacancy"	text	NO
F2:L2	Expression	=WEEKDAY(F2,2)>5	text	NO
F4:L4	Expression	=WEEKDAY(F4,2)>5	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E5:E6	List	=$F$9:$F$24

 

[Kaper]

In F5:

=INDEX($H$9:$Z$24,MATCH($E5,$F$9:$F$24,0),MATCH(F$4,$H$8:$Z$8,0))

and copy cell right
Will work both in Excel 2010 and 365

 

[Kaper]

PS. In Microsoft 365 delete F5:L5 and in f5 write (new possibilities with old functions):

=INDEX($H$9:$Z$24,MATCH($E5,$F$9:$F$24,0),MATCH(F$4:L$4,$H$8:$Z$8,0))

or using new functions

=CHOOSECOLS(FILTER($H$9:$Z$24,$E5=$F$9:$F$24,""),XMATCH(F$4:L$4,$H$8:$Z$8))

This formulas (use only one of them) will spill to cells on the right

 

[Dazzybeeguy]

PS. In Microsoft 365 delete F5:L5 and in f5 write (new possibilities with old functions):

=INDEX($H$9:$Z$24,MATCH($E5,$F$9:$F$24,0),MATCH(F$4:L$4,$H$8:$Z$8,0))

or using new functions

=CHOOSECOLS(FILTER($H$9:$Z$24,$E5=$F$9:$F$24,""),XMATCH(F$4:L$4,$H$8:$Z$8))

This formulas (use only one of them) will spill to cells on the right

I am probably doing something wrong but cant get these to work.

Book2
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	Start Date
2	07/01/2025	Tue	Wed	Thu	Fri	Sat	Sun	Mon
3	07-01-25
4	Person Drop Down	07-Jan	08-Jan	09-Jan	10-Jan	11-Jan	12-Jan	13-Jan
5	P 7	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A	#N/A
6	P 13	#N/A
7
8	Teams	Staff	Grade	Sun 05 Jan	Mon 06 Jan	Tue-07-Jan	Wed-08-Jan	Thu-09-Jan	Fri-10-Jan	Sat-11-Jan	Sun-12-Jan	Mon-13-Jan	Tue-14-Jan	Wed-15-Jan	Thu-16-Jan	Fri-17-Jan	Sat-18-Jan	Sun-19-Jan	Mon-20-Jan	Tue-21-Jan	Wed-22-Jan	Thu-23-Jan
9	Team 1	P 1	A	ES	EDO	ES	RW	ES	SRD	ES	ES	EDO	ES	RW	ES	SRD	ES	RW	ES	SRD	ES	ES
10	Team 2	P 2	A	TOIL	WFH(E)	WFH(M)	EDO	ES	RW	ES	SRD	WFH(E)	WFH(M)	WFH(L)	SL	WI	WFH(M)	EDO	ES	RW	ES	SRD
11	Team 3	P 3	B	AL	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL	WI	RW	ES	SRD	ES	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL	WI
12	Team 4	P 4	B	ES	EDO	ES	RW	ES	SRD	ES	ES	EDO	ES	RW	ES	SRD	ES	RW	ES	SRD	ES	ES
13	Team 5	P 5	A	TOIL	WFH(E)	WFH(M)	EDO	ES	RW	ES	SRD	WFH(E)	WFH(M)	WFH(L)	SL	WI	ES	RW	ES	SRD	ES	ES
14	Team 6	P 6	A	AL	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL	WI	RW	ES	SRD	ES	ES	WFH(M)	EDO	ES	RW	ES	SRD
15	Team 7	P 7	B	ES	EDO	ES	RW	ES	SRD	XX	ES	EDO	ES	RW	ES	SRD	TOIL	WFH(E)	WFH(M)	WFH(L)	SL	WI
16	Team 8	P 8	B	TOIL	WFH(E)	WFH(M)	EDO	ES	RW	ES	SRD	WFH(E)	WFH(M)	WFH(L)	SL	WI	XX	RW	ES	SRD	ES	ES
17	Team 9	P 9	A	AL	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL	WI	RW	ES	SRD	ES	ES	WFH(M)	ES	RW	ES	SRD	ES
18	Team 10	P 10	A	ES	EDO	ES	RW	ES	SRD	ES	ES	EDO	ES	RW	ES	SRD	TOIL	WFH(M)	EDO	ES	RW	ES
19	Team 11	P 11	B	TOIL	WFH(E)	WFH(M)	EDO	ES	RW	ES	SRD	WFH(E)	WFH(M)	WFH(L)	SL	WI	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL
20	Team 12	P 12	B	AL	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL	WI	TOIL	XX	ES	RW	ES	WFH(M)	ES	RW	ES	SRD	ES
21	Team 13	P 13	A	ES	EDO	ES	RW	ES	SRD	ES	ES	RW	ES	SRD	ES	ES	ES	WFH(M)	EDO	ES	RW	ES
22	Team 14	P 14	A	TOIL	WFH(E)	WFH(M)	EDO	ES	RW	ES	SRD	ES	ES	ES	ES	ES	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL
23	Team 15	P 15	B	AL	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL	WI	ES	ES	ES	ES	ES	ES	ES	RW	ES	SRD	ES
24	Team 16	P 16	B	ES	ES	ES	ES	ES	ES	AL	ES	TOIL	WFH(E)	WFH(M)	WFH(L)	SL	WI	WFH(M)	EDO	ES	RW	ES
Sheet1

Cell Formulas
Range	Formula
F2:L2	F2	=F4
E3	E3	=TEXT(E2,"dd-mm-yy")
G4:L4	G4	=F4+1
F4	F4	=StartDate
F5:L5	F5	=INDEX(table,MATCH($E5,staff,0),MATCH(F$4:L$4,$H$8:$Z$8,0))
F6	F6	=CHOOSECOLS(FILTER($H$9:$Z$24,$E6=$F$9:$F$24,""),XMATCH(F$4:L$4,$H$8:$Z$8))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
CoreData	=Sheet1!$H$9:$AK$24	F5:F6
DataTable	=Sheet1!$H$8:$AK$24	F5:F6
staff	=Sheet1!$F$9:$F$24	F5:F6
StartDate	=Sheet1!$E$2	F4, E3
table	=Sheet1!$H$9:$Z$24	F5:F6

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
O8,V8,AC8,AJ8	Dates Occurring	today	text	NO
E5:E6	Cell Value	contains "vacancy"	text	NO
F2:L2	Expression	=WEEKDAY(F2,2)>5	text	NO
F4:L4	Expression	=WEEKDAY(F4,2)>5	text	NO

Cells with Data Validation
Cell	Allow	Criteria
E5:E6	List	=$F$9:$F$24

 

[Sergius]

Hi! You have dates in cells H8:Z8 as text, that's why the formula doesn't work. Write dates in these cells as 01/05/2025, etc. And then set the custom format for them as "ddd dd mmm". As a result, the dates will be in the form of "Sun 05 Jan", and the formulas will calculate correctly.