Two way vertical and horizontal lookup including a date match

[Mitchell_butler]

I want a formula to lookup a date, establish whats it falls onto using the week ending date, match that against the employee name and return the intersecting value.

For example, I want to see James' potential hours for 8/1/21, which should return 32 (B16). If the date was 04/04/21 then it should return 25.6 for James

The dates in my dataset are by week ending e.g. Sunday 10/1/21 so a date lookup would need to know that 8/1/21 falls under that week and returns the intersecting value against the employee name

I have tried the following xlookup to no avail

=XLOOKUP(B3,A13:A16,XLOOKUP(B4,B12:AW12,B16:AW16,"not found",1,1))

Sample data XLOOKUP problem.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	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV	AW	AX
1
2
3	Name	James
4	Date	10/01/2021
5		#VALUE!
6
7																																																		Grand Total
8
9		Q1												Q2													Q3													Q4
10		January				February								April				May					June				July				August					September				October					November				December
11	Name	10/01/2021	17/01/2021	24/01/2021	31/01/2021	07/02/2021	14/02/2021	21/02/2021	28/02/2021	07/03/2021	14/03/2021	21/03/2021	28/03/2021	04/04/2021	11/04/2021	18/04/2021	25/04/2021	02/05/2021	09/05/2021	16/05/2021	23/05/2021	30/05/2021	06/06/2021	13/06/2021	20/06/2021	27/06/2021	04/07/2021	11/07/2021	18/07/2021	25/07/2021	01/08/2021	08/08/2021	15/08/2021	22/08/2021	29/08/2021	05/09/2021	12/09/2021	19/09/2021	26/09/2021	03/10/2021	10/10/2021	17/10/2021	24/10/2021	31/10/2021	07/11/2021	14/11/2021	21/11/2021	28/11/2021	05/12/2021
12	Week ending	10/01/2021	17/01/2021	24/01/2021	31/01/2021	07/02/2021	14/02/2021	21/02/2021	28/02/2021	07/03/2021	14/03/2021	21/03/2021	28/03/2021	04/04/2021	11/04/2021	18/04/2021	25/04/2021	02/05/2021	09/05/2021	16/05/2021	23/05/2021	30/05/2021	06/06/2021	13/06/2021	20/06/2021	27/06/2021	04/07/2021	11/07/2021	18/07/2021	25/07/2021	01/08/2021	08/08/2021	15/08/2021	22/08/2021	29/08/2021	05/09/2021	12/09/2021	19/09/2021	26/09/2021	03/10/2021	10/10/2021	17/10/2021	24/10/2021	31/10/2021	07/11/2021	14/11/2021	21/11/2021	28/11/2021	05/12/2021
13	Gary	32	32	32	32	32	32	32	32	32	32	32	32	25.6	25.6	32	32	32	25.6	32	32	32	25.6	32	32	32	32	32	32	32	32	32	32	32	32	25.6	32	32	32	32	32	32	32	32	32	32	32	32	32	1504
14	Bob	32	32	32	32	32	32	32	32	32	32	32	32	25.6	25.6	32	32	32	25.6	32	32	32	25.6	32	32	32	32	32	32	32	32	32	32	32	32	25.6	32	32	32	32	32	32	32	32	32	32	32	32	32	1504
15	Liz	32	32	32	32	32	32	32	32	32	32	32	32	25.6	25.6	32	32	32	25.6	32	32	32	25.6	32	32	32	32	32	32	32	32	32	32	32	32	25.6	32	32	32	32	32	32	32	32	32	32	32	32	32	1504
16	James	32	32	32	32	32	32	32	32	32	32	32	32	25.6	25.6	32	32	32	25.6	32	32	32	25.6	32	32	32	32	32	32	32	32	32	32	32	32	25.6	32	32	32	32	32	32	32	32	32	32	32	32	32	1504
Potential hours

Cell Formulas
Range	Formula
B5	B5	=XLOOKUP(B3,A13:A16,XLOOKUP(B4,B12:AW12,B16:AW16,"not found",1,1))

 

[Eric W]

Welcome to the MrExcel forum!

Try in B5:

=INDEX($B$13:$AW$16,MATCH(B3,$A$13:$A$16,0),MATCH(B4+CHOOSE(WEEKDAY(B4),0,6,5,4,3,2,1),$B$12:$AW$12,0))