XLOOKUP and ignoring blanks

F

franklin82

New Member
Joined
Jan 30, 2024
Messages
3
Office Version
  1. 365
Platform
  1. MacOS
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
ABCDEF
1NameFuture DateDateNameNext webinar
2Name16-Jul-23Name100-Jan-00
3Name16-Jul-23Name200-Jan-00
4Name110-Jan-24Name300-Jan-00
5Name110-Jan-24Name419-Sep-24
6Name2Name500-Jan-00
7Name319-Sep-23Name600-Jan-00
8Name419-Sep-24Name700-Jan-00
9Name5Name800-Jan-00
10Name61-Jun-23Name900-Jan-00
11Name614-Nov-23Name1000-Jan-00
12Name66-Feb-24Name1100-Jan-00
13Name6
14Name730-Mar-23
15Name812-Oct-23
16Name97-Nov-23
17Name914-Dec-23
18Name1030-Mar-22
19Name1020-Oct-23
20Name107-Dec-23
21Name1014-Dec-23
22Name1029-Feb-24
23Name10
24Name10
25Name10
26Name1122-Jun-23
27Name1113-Jul-23
28Name115-Sep-23
29Name1119-Oct-23
30Name1116-Nov-23
31Name1113-Dec-23
32Name1114-Feb-24
33Name117-Mar-24
34Name1111-Apr-24
Webinars - Live
Cell Formulas
RangeFormula
F2:F12F2=XLOOKUP(E2,KP,FDATE,,0,1)
Named Ranges
NameRefers ToCells
FDATE='Webinars - Live'!$B:$BF2:F12
KP='Webinars - Live'!$A:$AF2:F12
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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
ABCDEF
1NameFuture DateDateNameNext webinar
2Name145113Name1 
3Name145113Name2 
4Name145301Name3 
5Name145301Name49/19/24
6Name2Name5 
7Name345188name62/6/24
8Name49/19/24Name8 
9Name5Name9 
10name645078Name102/29/24
11name645244Name114/11/24
12name62/6/24 
13name6
14name645015
15Name845211
16Name945237
17Name945274
18Name1044650
19Name1045219
20Name1045267
21Name1045274
22Name102/29/24
23Name10
24Name10
25Name10
26Name1145099
27Name1145120
28Name1145174
29Name1145218
30Name1145246
31Name1145273
32Name112/14/24
33Name113/7/24
34Name114/11/24
Sheet1
Cell Formulas
RangeFormula
E2:E11E2=UNIQUE(A2:A34)
F2:F11F2=IF(MAXIFS($B$2:$B$34,$A$2:$A$34,E2)=0,"",MAXIFS($B$2:$B$34,$A$2:$A$34,E2))
F12F12=IF(MAXIFS(B:B,A:A,E12)=0,"",MAXIFS(B:B,A:A,E12))
Dynamic array formulas.
 
Upvote 0
Check this and revert -

Book1
ABCDEF
1NameFuture DateDateNameNext Webinar
2Name17/6/23Name1 
3Name17/6/23Name2 
4Name11/10/24Name3 
5Name11/10/24Name49/19/24
6Name2Name5 
7Name39/19/23Name62/6/24
8Name49/19/24Name7 
9Name5Name8 
10Name66/1/23Name9 
11Name611/14/23Name102/29/24
12Name62/6/24Name112/14/24
13Name6
14Name73/30/23
15Name810/12/23
16Name911/7/23
17Name912/14/23
18Name103/30/22
19Name1010/20/23
20Name1012/7/23
21Name1012/14/23
22Name102/29/24
23Name10
24Name10
25Name10
26Name116/22/23
27Name117/13/23
28Name119/5/23
29Name1110/19/23
30Name1111/16/23
31Name1112/13/23
32Name112/14/24
33Name113/7/24
34Name114/11/24
Sheet1
Cell Formulas
RangeFormula
F2:F12F2=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))
 
Upvote 0
Solution
Another option
Fluff.xlsm
ABCDEF
1NameFuture DateDateNameNext webinar
2Name106/07/2023Name1None
3Name106/07/2023Name2None
4Name110/01/2024Name3None
5Name110/01/2024Name419/09/2024
6Name2Name5None
7Name319/09/2023Name606/02/2024
8Name419/09/2024Name7None
9Name5Name8None
10Name601/06/2023Name9None
11Name614/11/2023Name1029/02/2024
12Name606/02/2024Name1114/02/2024
13Name6
14Name730/03/2023
15Name812/10/2023
16Name907/11/2023
17Name914/12/2023
18Name1030/03/2022
19Name1020/10/2023
20Name1007/12/2023
21Name1014/12/2023
22Name1029/02/2024
23Name10
24Name10
25Name10
26Name1122/06/2023
27Name1113/07/2023
28Name1105/09/2023
29Name1119/10/2023
30Name1116/11/2023
31Name1113/12/2023
32Name1114/02/2024
33Name1107/03/2024
34Name1111/04/2024
Sheet6
Cell Formulas
RangeFormula
F2:F12F2=BYROW(E2:E12,LAMBDA(br,TAKE(FILTER(B2:B100,(A2:A100=br)*(B2:B100<>""),"None"),1)))
Dynamic array formulas.
 
Upvote 0
just noticed for Name11 - i show the latest date - did you want all 3 dates or earliest or ????
 
Upvote 0
Thanks all for the quick responses! I'll have a look through
etaf said:
just noticed for Name11 - i show the latest date - did you want all 3 dates or earliest or ????
Click to expand...
Just the earliest date is fine
 
Upvote 0
SanjayGMusafir said:
Check this and revert -

Book1
ABCDEF
1NameFuture DateDateNameNext Webinar
2Name17/6/23Name1 
3Name17/6/23Name2 
4Name11/10/24Name3 
5Name11/10/24Name49/19/24
6Name2Name5 
7Name39/19/23Name62/6/24
8Name49/19/24Name7 
9Name5Name8 
10Name66/1/23Name9 
11Name611/14/23Name102/29/24
12Name62/6/24Name112/14/24
13Name6
14Name73/30/23
15Name810/12/23
16Name911/7/23
17Name912/14/23
18Name103/30/22
19Name1010/20/23
20Name1012/7/23
21Name1012/14/23
22Name102/29/24
23Name10
24Name10
25Name10
26Name116/22/23
27Name117/13/23
28Name119/5/23
29Name1110/19/23
30Name1111/16/23
31Name1112/13/23
32Name112/14/24
33Name113/7/24
34Name114/11/24
Sheet1
Cell Formulas
RangeFormula
F2:F12F2=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))
Click to expand...
This is working great, many thanks!
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
SUM XLOOKUP
Replies
5
Views
253
StephenCrump
S
S
  • Question Question
AVERAGEIF TROUBLES
Replies
3
Views
58
AhoyNC
A
P
  • Question Question
If XLOOKUP value is blank, return blank
Replies
1
Views
482
DanteAmor
DanteAmor
G
  • Question Question
Copy some specific columns whose header is merge
2 3
Replies
26
Views
659
Joe4
Joe4
D
  • Locked
  • Question Question
Could you please write a code for the following scenario?
Replies
1
Views
176
Fluff
Fluff

Forum statistics

Threads
1,223,886
Messages
6,175,198
Members
452,616
Latest member
intern444

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top