Lookup with multiple criteria in list with duplicate entries

[Steven Wood]

Hi guys

I'm looking to pull data from the below sample. The user IDs can repeat throughout the column, as can the dates.

I want to extract the data from column AB, when looking up Column B & Column Z combined and return it into a list of User IDs by date - for example, lookup for user 4094 on 01/10/2023 should return 15:13:03.

Any help would be greatly appreciated!

 

[StephenCrump]

Welcome to the Forum!

Like this perhaps?

	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
1	INPUT	UserID																								Date
2		4094					1	2	3																	1 Oct 2023
3		5034									4	5	6													1 Oct 2023
4		3223				7	8	9																		1 Oct 2023
5		9306							10	11	12															1 Oct 2023
6		7777														13	14	15								1 Oct 2023
7		3223									16															2 Oct 2023
8		9306		17	18																					2 Oct 2023
9		7777					19	20	21																	2 Oct 2023
10
11
12
13
14	OUTPUT	UserID	1 Oct 2023	2 Oct 2023
15		3223	7	16
16		4094	1	N/A
17		5034	4	N/A
18		7777	13	19
19		9306	10	17
20
Sheet1

Cell Formulas
Range	Formula
C14:D14	C14	=TRANSPOSE(UNIQUE(Z2:Z9))
B15:B19	B15	=SORT(UNIQUE(B2:B9))
C15:D19	C15	=LET(d,FILTER($C$2:$Y$9,($B$2:$B$9=$B15)*($Z$2:$Z$9=C$14)),IFERROR(TAKE(FILTER(d,d<>0),,1),"N/A"))
Dynamic array formulas.