franklin82
New Member
- Joined
- Jan 30, 2024
- Messages
- 3
- Office Version
- 365
- Platform
- 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!
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 |