XLOOKUP and ignoring blanks

[franklin82]

I'm trying to bring back results that will show the next date someone has booked an event. In the sheet, I have multiple entries for a person and this shows when they've next booked an event and in the column next to this, it shows when their last event took place.

I've used an XLOOKUP (=XLOOKUP(E2,KP,FDATE,,0,1)) but if a person has a blank cell above a cell that has value it returns the date as 00-Jan-00 and then ignores the cell that has a value. I've put a sample of the data below. You'll see that in Column E and F is where I'd like the results to show. In E I have the list of names and then F returns the date. You'll see that Name1 to Name3 all return 00-Jan-00 and then Name4 returns the correct date. Name 6 is also showing 00-Jan-00 but it should return the value 6-Feb-24 as that is the next date but as the 2 cells above are blank it brings in the data 00-Jan-00. The same issue then happens for Name10 and Name11.

I suspect that because I have blank cells that is why the 00-Jan-00 is being returned, but I can't figure out how to ignore these blank cells and get the XLOOKUP to search for the correct entry.

Hope the above makes sense!

Book1
	A	B	C	D	E	F
1	Name	Future Date	Date		Name	Next webinar
2	Name1		6-Jul-23		Name1	00-Jan-00
3	Name1		6-Jul-23		Name2	00-Jan-00
4	Name1		10-Jan-24		Name3	00-Jan-00
5	Name1		10-Jan-24		Name4	19-Sep-24
6	Name2				Name5	00-Jan-00
7	Name3		19-Sep-23		Name6	00-Jan-00
8	Name4	19-Sep-24			Name7	00-Jan-00
9	Name5				Name8	00-Jan-00
10	Name6		1-Jun-23		Name9	00-Jan-00
11	Name6		14-Nov-23		Name10	00-Jan-00
12	Name6	6-Feb-24			Name11	00-Jan-00
13	Name6
14	Name7		30-Mar-23
15	Name8		12-Oct-23
16	Name9		7-Nov-23
17	Name9		14-Dec-23
18	Name10		30-Mar-22
19	Name10		20-Oct-23
20	Name10		7-Dec-23
21	Name10		14-Dec-23
22	Name10	29-Feb-24
23	Name10
24	Name10
25	Name10
26	Name11		22-Jun-23
27	Name11		13-Jul-23
28	Name11		5-Sep-23
29	Name11		19-Oct-23
30	Name11		16-Nov-23
31	Name11		13-Dec-23
32	Name11	14-Feb-24
33	Name11	7-Mar-24
34	Name11	11-Apr-24
Webinars - Live

Cell Formulas
Range	Formula
F2:F12	F2	=XLOOKUP(E2,KP,FDATE,,0,1)

Named Ranges
Name	Refers To	Cells
FDATE	='Webinars - Live'!$B:$B	F2:F12
KP	='Webinars - Live'!$A:$A	F2:F12

 

[etaf]

how about

=IF(MAXIFS($B$2:$B$34,$A$2:$A$34,E2)=0,"",MAXIFS($B$2:$B$34,$A$2:$A$34,E2))

but full column references are not great

Book4
	A	B	C	D	E	F
1	Name	Future Date	Date		Name	Next webinar
2	Name1		45113		Name1
3	Name1		45113		Name2
4	Name1		45301		Name3
5	Name1		45301		Name4	9/19/24
6	Name2				Name5
7	Name3		45188		name6	2/6/24
8	Name4	9/19/24			Name8
9	Name5				Name9
10	name6		45078		Name10	2/29/24
11	name6		45244		Name11	4/11/24
12	name6	2/6/24
13	name6
14	name6		45015
15	Name8		45211
16	Name9		45237
17	Name9		45274
18	Name10		44650
19	Name10		45219
20	Name10		45267
21	Name10		45274
22	Name10	2/29/24
23	Name10
24	Name10
25	Name10
26	Name11		45099
27	Name11		45120
28	Name11		45174
29	Name11		45218
30	Name11		45246
31	Name11		45273
32	Name11	2/14/24
33	Name11	3/7/24
34	Name11	4/11/24
Sheet1

Cell Formulas
Range	Formula
E2:E11	E2	=UNIQUE(A2:A34)
F2:F11	F2	=IF(MAXIFS($B$2:$B$34,$A$2:$A$34,E2)=0,"",MAXIFS($B$2:$B$34,$A$2:$A$34,E2))
F12	F12	=IF(MAXIFS(B:B,A:A,E12)=0,"",MAXIFS(B:B,A:A,E12))
Dynamic array formulas.

 

[SanjayGMusafir]

Check this and revert -

Book1
	A	B	C	D	E	F
1	Name	Future Date	Date		Name	Next Webinar
2	Name1		7/6/23		Name1
3	Name1		7/6/23		Name2
4	Name1		1/10/24		Name3
5	Name1		1/10/24		Name4	9/19/24
6	Name2				Name5
7	Name3		9/19/23		Name6	2/6/24
8	Name4	9/19/24			Name7
9	Name5				Name8
10	Name6		6/1/23		Name9
11	Name6		11/14/23		Name10	2/29/24
12	Name6	2/6/24			Name11	2/14/24
13	Name6
14	Name7		3/30/23
15	Name8		10/12/23
16	Name9		11/7/23
17	Name9		12/14/23
18	Name10		3/30/22
19	Name10		10/20/23
20	Name10		12/7/23
21	Name10		12/14/23
22	Name10	2/29/24
23	Name10
24	Name10
25	Name10
26	Name11		6/22/23
27	Name11		7/13/23
28	Name11		9/5/23
29	Name11		10/19/23
30	Name11		11/16/23
31	Name11		12/13/23
32	Name11	2/14/24
33	Name11	3/7/24
34	Name11	4/11/24
Sheet1

Cell Formulas
Range	Formula
F2:F12	F2	=LET(MX,MAXIFS(B:B,A:A,E2), MN,MINIFS(B:B,A:A,E2), CNT,COUNTIFS(A:A,E2,B:B,">="&TODAY()), IFS(CNT>1,MN,MX=0,"",TRUE,MX))

 

[Fluff]

Another option

Fluff.xlsm
	A	B	C	D	E	F
1	Name	Future Date	Date		Name	Next webinar
2	Name1		06/07/2023		Name1	None
3	Name1		06/07/2023		Name2	None
4	Name1		10/01/2024		Name3	None
5	Name1		10/01/2024		Name4	19/09/2024
6	Name2				Name5	None
7	Name3		19/09/2023		Name6	06/02/2024
8	Name4	19/09/2024			Name7	None
9	Name5				Name8	None
10	Name6		01/06/2023		Name9	None
11	Name6		14/11/2023		Name10	29/02/2024
12	Name6	06/02/2024			Name11	14/02/2024
13	Name6
14	Name7		30/03/2023
15	Name8		12/10/2023
16	Name9		07/11/2023
17	Name9		14/12/2023
18	Name10		30/03/2022
19	Name10		20/10/2023
20	Name10		07/12/2023
21	Name10		14/12/2023
22	Name10	29/02/2024
23	Name10
24	Name10
25	Name10
26	Name11		22/06/2023
27	Name11		13/07/2023
28	Name11		05/09/2023
29	Name11		19/10/2023
30	Name11		16/11/2023
31	Name11		13/12/2023
32	Name11	14/02/2024
33	Name11	07/03/2024
34	Name11	11/04/2024
Sheet6

Cell Formulas
Range	Formula
F2:F12	F2	=BYROW(E2:E12,LAMBDA(br,TAKE(FILTER(B2:B100,(A2:A100=br)*(B2:B100<>""),"None"),1)))
Dynamic array formulas.

 

[etaf]

just noticed for Name11 - i show the latest date - did you want all 3 dates or earliest or ????

 

[franklin82]

Thanks all for the quick responses! I'll have a look through

just noticed for Name11 - i show the latest date - did you want all 3 dates or earliest or ????

Just the earliest date is fine

 

[etaf]

Just the earliest date is fine

Ignore my solution as that will give the latest date not the earlist

 

[franklin82]

Check this and revert -

Book1
	A	B	C	D	E	F
1	Name	Future Date	Date		Name	Next Webinar
2	Name1		7/6/23		Name1
3	Name1		7/6/23		Name2
4	Name1		1/10/24		Name3
5	Name1		1/10/24		Name4	9/19/24
6	Name2				Name5
7	Name3		9/19/23		Name6	2/6/24
8	Name4	9/19/24			Name7
9	Name5				Name8
10	Name6		6/1/23		Name9
11	Name6		11/14/23		Name10	2/29/24
12	Name6	2/6/24			Name11	2/14/24
13	Name6
14	Name7		3/30/23
15	Name8		10/12/23
16	Name9		11/7/23
17	Name9		12/14/23
18	Name10		3/30/22
19	Name10		10/20/23
20	Name10		12/7/23
21	Name10		12/14/23
22	Name10	2/29/24
23	Name10
24	Name10
25	Name10
26	Name11		6/22/23
27	Name11		7/13/23
28	Name11		9/5/23
29	Name11		10/19/23
30	Name11		11/16/23
31	Name11		12/13/23
32	Name11	2/14/24
33	Name11	3/7/24
34	Name11	4/11/24
Sheet1

Cell Formulas
Range	Formula
F2:F12	F2	=LET(MX,MAXIFS(B:B,A:A,E2), MN,MINIFS(B:B,A:A,E2), CNT,COUNTIFS(A:A,E2,B:B,">="&TODAY()), IFS(CNT>1,MN,MX=0,"",TRUE,MX))

This is working great, many thanks!

 

[SanjayGMusafir]

This is working great, many thanks!

Glad to help you and thanks for the feedback.