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

 

[murrolems]

I didn't mean for ID 3 to have the same date, this is just an example table. The actual data won't have the same dates duplicated for an ID

 

[Cubist]

Try:

Book1
	A	B	C	D	E	F	G
1	ID	Date	Code		ID	random date	returned code
2	1	5/23/24	F		1	5/7/24	C
3	1	5/8/24	E		1	5/8/24	E
4	1	4/29/24	C		1	5/9/24	E
5	3	5/23/24	S
6	3	5/23/24	H
7	2	5/8/24	E
8	2	4/23/24	K
9	2	5/23/24	X
10	4	5/8/24	P
Sheet1

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

 

[murrolems]

Try:

Book1
	A	B	C	D	E	F	G
1	ID	Date	Code		ID	random date	returned code
2	1	5/23/24	F		1	5/7/24	C
3	1	5/8/24	E		1	5/8/24	E
4	1	4/29/24	C		1	5/9/24	E
5	3	5/23/24	S
6	3	5/23/24	H
7	2	5/8/24	E
8	2	4/23/24	K
9	2	5/23/24	X
10	4	5/8/24	P
Sheet1

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

Thank you for the reply! Can I use that formula for Google sheets? I didn't think it had an xlookup function.

 

[Cubist]

This is the wrong sub-forum for Sheets, but it does have XLOOKUP. However, I don't know how arrays behave in Sheets.

 

[murrolems]

This is the wrong sub-forum for Sheets, but it does have XLOOKUP. However, I don't know how arrays behave in Sheets.

Thanks again, this formula fails for any dates that come before the furthest out date. For example, if I'm looking for 4/7/24, I get an error because it is before the date range. Any ideas?

ID	given date	returned code
1​	4/7/2024​	#N/A​
1​	5/8/2024​	E
1​	5/9/2024​	E

 

[Cubist]

As I mentioned, I don't know how arrays behave in Sheets so can't help.

 

[murrolems]

As I mentioned, I don't know how arrays behave in Sheets so can't help.

I can make it work in excel as well if that helps.

 

[Cubist]

In Exccel, I am not getting #N/A.

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	E
4	1	4/29/2024	C		1	5/9/2024	E
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)

 

[murrolems]

In Exccel, I am not getting #N/A.

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	E
4	1	4/29/2024	C		1	5/9/2024	E
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)

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.