Xlookup to with multiple conditions

[zinah]

Hi,

I use Xlookup to get the dates for my details tab. However, I need to refine the formula to get only today and any future dates, without getting old dates or even the dates that is formatted as 1/0/1900. Below is my sample data along with the formula that I'm using. Is there any suggestion to refine my formula to get the desired results?
Thanks!

Xlookup Help with dates criteria.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Current Result					Desired Results						IDs	Dates
2	IDs	Name	Dates			IDs	Name	Dates				AAA
3	AAA	John	10/3/2024			AAA	John	10/3/2024				BBB	3/3/2024
4	BBB	Will	9/3/2024			BBB	Will	9/3/2024				CCC	9/7/2024
5	CCC	Abbey	9/7/2024			CCC	Abbey	9/7/2024				DDD	########
6	DDD	Don	11/4/2021			HHH	Leo	5/1/2024				EEE
7	EEE	Louise	1/0/1900			NNN	Leon	3/8/2024				FFF
8	FFF	Sue	1/0/1900									GGG
9	GGG	Ron	1/0/1900									HHH	5/1/2024
10	HHH	Leo	5/1/2024									KKK
11	KKK	Frank	1/0/1900									OOO
12	OOO	Ronaldo	1/0/1900									NNN	3/8/2024
13	NNN	Leon	3/8/2024									BBB	9/3/2024
14	ZZZ	Shondha										AAA	########
15	LLL	Mona	1/0/1900									LLL
16
17
18
19
20
Details

Cell Formulas
Range	Formula
C3:C15	C3	=XLOOKUP(A3,$L:$L,$M:$M,"",,-1)

 

[JEC]

Try

=FILTER(A3:C15,(C3:C15>=TODAY())*(C3:C15<>""))

 

[zinah]

Try

=FILTER(A3:C15,(C3:C15>=TODAY())*(C3:C15<>""))

Thank you for the quick respond! I like your suggested formula, however, I don't want to filter the results. What I want is exactly the same desired results table, where I get only current date, and future dates, and blanks for the rest of rows (if they meet the set criteria).
Can you suggest a different formula pls?

 

[zinah]

Hi, can anyone help please?

 

[Fluff]

In what way doesn't the formula from jec work for you?
It gives the same result as you showed.

 

[zinah]

In what way doesn't the formula from jec work for you?
It gives the same result as you showed.

Hi @Fluff ! I don't need a filtered summary, what I need is to the desired results, where it brings the most recent date for duplicate IDs with more than one date, ignore the old dates and show blank for any date that is older than today, and show the dates that has the format 1/0/1900 as blank, these are the conditions that I want to incorporate to my current formula.

Xlookup Help with dates criteria.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Current Result					Desired Results						IDs	Dates
2	IDs	Name	Dates			IDs	Name	Dates				AAA
3	AAA	John	10/3/2024			AAA	John	10/3/2024				BBB	3/3/2024
4	BBB	Will	9/3/2024			BBB	Will	9/3/2024				CCC	9/7/2024
5	CCC	Abbey	9/7/2024			CCC	Abbey	9/7/2024				DDD	11/4/2021
6	DDD	Don	11/4/2021			DDD	Don					EEE
7	EEE	Louise	1/0/1900			EEE	Louise					FFF
8	FFF	Sue	1/0/1900			FFF	Sue					GGG
9	GGG	Ron	1/0/1900			GGG	Ron					HHH	5/1/2024
10	HHH	Leo	5/1/2024			HHH	Leo	5/1/2024				KKK
11	KKK	Frank	1/0/1900			KKK	Frank					OOO
12	OOO	Ronaldo	1/0/1900			OOO	Ronaldo					NNN	3/18/2024
13	NNN	Leon	3/18/2024			NNN	Leon	3/18/2024				BBB	9/3/2024
14	ZZZ	Shondha				ZZZ	Shondha					AAA	10/3/2024
15	LLL	Mona	1/0/1900			LLL	Mona					LLL
16
17
18
Details

Cell Formulas
Range	Formula
C3:C15	C3	=XLOOKUP(A3,$L:$L,$M:$M,"",,-1)

 

[Fluff]

That is completely different from what you originally showed as the expected result.
How do you expect to get something that works when you do that?

 

[zinah]

That is completely different from what you originally showed as the expected result.
How do you expect to get something that works when you do that?

You're right, I accidentally copy the incorrect table, sorry for the confusion. I updated the desired table and edited the dates to match the criteria that I need.

 

[Fluff]

Ok, how about

=TAKE(FILTER($M$2:$M$15,($M$2:$M$15>=TODAY())*($L$2:$L$15=A3),""),1)

 

[zinah]

Ok, how about

=TAKE(FILTER($M$2:$M$15,($M$2:$M$15>=TODAY())*($L$2:$L$15=A3),""),1)

If anyone asked me how do you know sophisticated formulas, I would say you're name! THANKS a MILLION! The formula is exactly what I needed.