Return Code for a certain ID in a date Range

[murrolems]

I have the following data in 3 columns: ID, date and code. I need a formula that will return the code for a given ID and a random date that falls within a range. The date field could be sorted by ID first then date if necessary.

Table:

ID	Date	Code
1​	5/23/2024​	F
1​	5/8/2024​	E
1​	4/29/2024​	C
3​	5/23/2024​	S
3​	5/23/2024​	H
2​	5/8/2024​	E
2​	4/23/2024​	K
2​	5/23/2024​	X
4​	5/8/2024​	P

Here's an example of the desired output:

ID	random date	returned code
1​	5/7/2024​	C
1​	5/8/2024​	E
1​	5/9/2024​	E

 

[Cubist]

That means your columns A and E are not the same but they appear to be the same.
Try =E2=A2 I suspect this will return FALSE.

 

[murrolems]

Hmm, that's weird. I do get #N/A in excel, here is a screenshot from my excel. I included the evaluate where you can see it's doing a lookup that fails.
View attachment 112380

Also, I just changed the match mode to 1 from -1 and it doesn't give an error...

 

[Cubist]

Changing match mode to 1 will give you wrong results for the others.

Book1 (version 1).xlsb
	A	B	C	D	E	F	G
1	ID	Date	Code		ID	random date	returned code
2	1	5/23/2024	F		1	4/7/2024	C
3	1	5/8/2024	E		1	5/8/2024	C
4	1	4/29/2024	C		1	5/9/2024	C
5	3	5/23/2024	S
6	3	5/23/2024	H
7	2	5/8/2024	E
8	2	4/23/2024	K
9	2	5/23/2024	X
10	4	5/8/2024	P
Sheet2

Cell Formulas
Range	Formula
G2:G4	G2	=XLOOKUP(F2,IF($A$2:$A$10=E2,$B$2:$B$10),$C$2:$C$10,,1)