XLookup with dates and text

gavdpol

New Member
Joined
May 6, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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

1620310293207.png
 

Attachments

  • 1620310431234.png
    1620310431234.png
    11.6 KB · Views: 2

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows
Are all the dates real dates, or are they text?
If real dates are the destination headers the 1st of the month?
 

gavdpol

New Member
Joined
May 6, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
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​
20202020
Month​
45
AdamReintegrated
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows
Are the destination headers real dates, or just text?
 

gavdpol

New Member
Joined
May 6, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows
If you make them dates (formatted to just show mmm-yy) you can use
+Fluff 1.xlsm
ABCDE
1SourceExit dateExit Reason
2Adam05/06/2020Left
3Adam08/04/2020reintegrated
4Frank15/05/2020left
5Frank13/06/2020overnight
6Frank13/01/2021deceased
7
8
901/04/202001/05/2020Jun-20Jan-21
10Adamreintegrated Left 
11Frank leftovernightdeceased
Main
Cell Formulas
RangeFormula
B10:E11B10=FILTER(Table1[[Exit Reason]:[Exit Reason]],(Table1[[Source]:[Source]]=$A10)*(TEXT(Table1[[Exit date]:[Exit date]],"mmyyyy")=TEXT(B$9,"mmyyyy")),"")
 
Solution

gavdpol

New Member
Joined
May 6, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows
Yes it's possible
Excel Formula:
=XLOOKUP($A10&TEXT(B$9,"mmyyyy"),Table1[[Source]:[Source]]&TEXT(Table1[[Exit date]:[Exit date]],"mmyyyy"),Table1[[Exit Reason]:[Exit Reason]],"")
 

gavdpol

New Member
Joined
May 6, 2021
Messages
5
Office Version
  1. 365
Platform
  1. Windows
If you make them dates (formatted to just show mmm-yy) you can use
+Fluff 1.xlsm
ABCDE
1SourceExit dateExit Reason
2Adam05/06/2020Left
3Adam08/04/2020reintegrated
4Frank15/05/2020left
5Frank13/06/2020overnight
6Frank13/01/2021deceased
7
8
901/04/202001/05/2020Jun-20Jan-21
10Adamreintegrated Left 
11Frank leftovernightdeceased
Main
Cell Formulas
RangeFormula
B10:E11B10=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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,914
Messages
5,655,923
Members
418,253
Latest member
TheJackal26

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
Top