XLookup with dates and text

[gavdpol]

Hi, for our night shelter we are looking for a system that searches for double entries and also their exit reasons based on the date of exit. So the source has some recurring clients, an exit date and an exit reason.
The destination has a list of all recurring clients (double or more). Next to each client we would like to see the EXIT REASON based on the month and year. With this we can find out how their journey was within our system.

I thought it was possible to use Xlookup but can't work it out properly. Hope some one has the answer. Kind regards, Art

 

[Fluff]

Are all the dates real dates, or are they text?
If real dates are the destination headers the 1st of the month?

 

[gavdpol]

Hi!

The dates are real dates (filtering per year and month is possible).
The destination headers only have to be the month of that year. So even splitting that would be an option for me. For example:

Year​	2020	2020
Month​	4	5
Adam	Reintegrated

 

[Fluff]

Are the destination headers real dates, or just text?

 

[gavdpol]

Are the destination headers real dates, or just text?

Hi, for now they are text, but for us it doesn't matter what we type there as long as it matches with the exit dates (month and year) in the source tab. So if it must be text we can use that, if dates, that's also ok. Or does that sound weard? Regards Art

 

[Fluff]

If you make them dates (formatted to just show mmm-yy) you can use

+Fluff 1.xlsm
	A	B	C	D	E
1	Source	Exit date	Exit Reason
2	Adam	05/06/2020	Left
3	Adam	08/04/2020	reintegrated
4	Frank	15/05/2020	left
5	Frank	13/06/2020	overnight
6	Frank	13/01/2021	deceased
7
8
9		01/04/2020	01/05/2020	Jun-20	Jan-21
10	Adam	reintegrated		Left
11	Frank		left	overnight	deceased
Main

Cell Formulas
Range	Formula
B10:E11	B10	=FILTER(Table1[[Exit Reason]:[Exit Reason]],(Table1[[Source]:[Source]]=$A10)*(TEXT(Table1[[Exit date]:[Exit date]],"mmyyyy")=TEXT(B$9,"mmyyyy")),"")

 

[gavdpol]

Thanks! Will try that later today and let you know! So Xlookup is not the option

 

[Fluff]

Yes it's possible

=XLOOKUP($A10&TEXT(B$9,"mmyyyy"),Table1[[Source]:[Source]]&TEXT(Table1[[Exit date]:[Exit date]],"mmyyyy"),Table1[[Exit Reason]:[Exit Reason]],"")

 

[gavdpol]

If you make them dates (formatted to just show mmm-yy) you can use

+Fluff 1.xlsm
	A	B	C	D	E
1	Source	Exit date	Exit Reason
2	Adam	05/06/2020	Left
3	Adam	08/04/2020	reintegrated
4	Frank	15/05/2020	left
5	Frank	13/06/2020	overnight
6	Frank	13/01/2021	deceased
7
8
9		01/04/2020	01/05/2020	Jun-20	Jan-21
10	Adam	reintegrated		Left
11	Frank		left	overnight	deceased
Main

Cell Formulas
Range	Formula
B10:E11	B10	=FILTER(Table1[[Exit Reason]:[Exit Reason]],(Table1[[Source]:[Source]]=$A10)*(TEXT(Table1[[Exit date]:[Exit date]],"mmyyyy")=TEXT(B$9,"mmyyyy")),"")

this one worked! the xlookup not yet. But will work with this one. Thank you so much!

 

[Fluff]

You're welcome & thanks for the feedback.