I need a combination of XLOOKUP and FILTER for an HR Dashboard to show monthly leavers. Formula can be updated using dropdown.

maxim642

New Member
Joined
Feb 4, 2021
Messages
27
Office Version
  1. 365
Platform
  1. MacOS
1.3.21 HR Dashboard ML.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1Full NameEmployee NumberJob TitleJob LevelSalaryCompanyDivisionDepartmentStatusEmployee TypeManager Full NameStart DateContinuous Start DateLeaving DateLocationGenderEmailContract End DateStandard HoursDisabledCar AllowanceActual FTECost CentreEthnicityLoS DateFull Time Y/NStart YearStart MonthLeave YearLeave MonthLoS (Days)Los (Years)Actual Start DateFormatted Leave Date
2Worker 11JT1JL150kXTest 1Dep 1CurrentEmployeeMan 11/13/11 5/13/15LondonMaletest1 37.5No01cc1White - Any other background5/13/15Yes2011Jan2015May15814.31/13/115/13/15
3Worker 22JT2JL250kXTest 2Dep 2CurrentEmployeeMan 23/1/19 LondonMaletest2 No01cc2White - English, Welsh, Scottish, Northern Irish, British3/1/21Yes2019Mar 7312.03/1/19
4Worker 33JT3JL350kXTest 3Dep 3CurrentEmployeeMan 31/11/21 LondonMaletest3 No01cc3White - English, Welsh, Scottish, Northern Irish, British3/1/21Yes2021Jan 490.11/11/21
5Worker 44JT4JL450kXTest 4Dep 4FormerEmployeeMan 41/1/12 7/15/20LondonMaletest4 37.5No01cc4White - English, Welsh, Scottish, Northern Irish, British7/15/20Yes2012Jan2020Jul31188.51/1/127/15/20
6Worker 55JT5JL550kXTest 5Dep 5CurrentEmployeeMan 51/4/21 LondonFemaletest5 No01cc5White - English, Welsh, Scottish, Northern Irish, British3/1/21Yes2021Jan 560.21/4/21
7Worker 66JT6JL650kXTest 6Dep 6CurrentEmployeeMan 69/30/19 Milton KeynesMaletest6 No01cc6 3/1/21Yes2019Sep 5181.49/30/19
8Worker 77JT7JL750kXTest 7Dep 7CurrentEmployeeMan 71/16/14 LondonMaletest7 37.5No01cc7White - English, Welsh, Scottish, Northern Irish, British3/1/21Yes2014Jan 26017.11/16/14
9Worker 88JT8JL850kXTest 8Dep 8FormerEmployeeMan 83/3/86 9/30/20HomeMaletest8 No01cc8 9/30/20Yes1986Mar2020Sep1263034.63/3/869/30/20
10Worker 99JT9JL950kXTest 9Dep 9FormerEmployeeMan 99/1/209/30/199/25/20Home BasedMaletest9 No01cc9White - English, Welsh, Scottish, Northern Irish, British9/25/20Yes2019Sep2020Sep3611.09/30/199/25/20
11Worker 1010JT10JL1050kXTest 10Dep 10CurrentEmployeeMan 108/6/12 LondonMaletest10 37.5No01cc10White - English, Welsh, Scottish, Northern Irish, British3/1/21Yes2012Aug 31298.68/6/12
12Worker 1111JT11JL1150kXTest 11Dep 11CurrentEmployeeMan 119/9/19 2/29/16LondonMaletest11 37.5No01cc11White - English, Welsh, Scottish, Northern Irish, British2/29/16Yes2019Sep2016Feb-1288-9/9/192/29/16
13Worker 1212JT12JL1250kXTest 12Dep 12CurrentEmployeeMan 125/21/18 8/20/18LondonFemaletest12 No01cc12 8/20/18Yes2018May2018Aug910.25/21/188/20/18
14Worker 1313JT13JL1350kXTest 13Dep 13CurrentEmployeeMan 1312/3/18 LondonMaletest13 37.5No01cc13White - English, Welsh, Scottish, Northern Irish, British3/1/21Yes2018Dec 8192.212/3/18
15Worker 1414JT14JL1450kXTest 14Dep 14CurrentEmployeeMan 144/1/034/1/03 TelfordFemaletest14 37.5No01cc14Chinese - British3/1/21Yes2003Apr 654417.94/1/03
16Worker 1515JT15JL1550kXTest 15Dep 15FormerEmployeeMan 155/1/12 7/1/19LondonMaletest15 37.5No01cc15White - English, Welsh, Scottish, Northern Irish, British7/1/19Yes2012May2019Jul26177.25/1/127/1/19
17Worker 1616JT16JL1650kXTest 16Dep 16CurrentEmployeeMan 164/2/194/2/19 LondonMaletest16 37.5No01cc16White - English, Welsh, Scottish, Northern Irish, British3/1/21Yes2019Apr 6991.94/2/19
18Worker 1717JT17JL1750kXTest 17Dep 17CurrentEmployeeMan 179/7/20 LondonMaletest17 No01cc17White - English, Welsh, Scottish, Northern Irish, British3/1/21Yes2020Sep 1750.59/7/20
19Worker 1818JT18JL1850kXTest 18Dep 18CurrentEmployeeMan 1812/1/13 7/8/21LondonMaletest18 37.5No01cc18White - English, Welsh, Scottish, Northern Irish, British7/8/21Yes2013Dec2021Jul27767.612/1/137/8/21
20
212015Jan
22MonthFull NameCompanyDepartmentDivisionLocationJob TitleStart DateLeaving DateLoS YearsEmployee Type2015Jan
232016Feb
242017Mar
252018Apr
262019May
272020Jun
282021Jul
292022Aug
30Sep
31Oct
32Nov
33Dec
34
35
36
37
38
Sheet2
Cell Formulas
RangeFormula
Y2:Y19Y2=IF(ISERROR(DATE(DAY(N2),MONTH(N2),YEAR(N2))),TODAY(),N2)
Z2:Z19Z2=IF(V2>=1,"Yes","No")
AA2:AA19AA2=TEXT(AG2,"YYYY")
AB2:AB19AB2=TEXT(AG2,"MMM")
AC2:AC19AC2=TEXT(N2,"YYYY")
AD2:AD19AD2=TEXT(N2,"MMM")
AE2:AE19AE2=Y2-AG2
AF2:AF19AF2=IF(AE2/365<0,"-",AE2/365)
AG2:AG19AG2=IF(ISERROR(DATE(DAY(M2),MONTH(M2),YEAR(M2))),L2,M2)
AH2:AH19AH2=IF(ISERROR(DATE(DAY(N2),MONTH(N2),YEAR(N2)))," ",N2)
Cells with Data Validation
CellAllowCriteria
A21List=$N$22:$N$29
B21List=$O$22:$O$33
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

maxim642

New Member
Joined
Feb 4, 2021
Messages
27
Office Version
  1. 365
Platform
  1. MacOS
Cells A21 & B21 are the selectable dropdowns.

The intention is for the user to be able to select a time period using the dropdowns and then rows 23 onwards will become populated with the leavers for that month/year.
 

maxim642

New Member
Joined
Feb 4, 2021
Messages
27
Office Version
  1. 365
Platform
  1. MacOS
For example, if the user inputs "2020" in A21 & "Sep" in B21, x2 records would pull through (column N determines when the employee has left).
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,648
Office Version
  1. 365
Platform
  1. Windows
What should go in the Month column?
 

maxim642

New Member
Joined
Feb 4, 2021
Messages
27
Office Version
  1. 365
Platform
  1. MacOS

ADVERTISEMENT

It could either be the month from column AD or B21.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,648
Office Version
  1. 365
Platform
  1. Windows
Ok how about
The headers in A22:K22 must be an exact match with the relevat header in row 1
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1Full NameEmployee NumberJob TitleJob LevelSalaryCompanyDivisionDepartmentStatusEmployee TypeManager Full NameStart DateContinuous Start DateLeaving DateLocationGenderEmailContract End DateStandard HoursDisabledCar AllowanceActual FTECost CentreEthnicityLoS DateFull Time Y/NStart YearStart MonthLeave YearLeave MonthLoS (Days)Los (Years)Actual Start DateFormatted Leave Date
2Worker 11JT1JL150kXTest 1Dep 1CurrentEmployeeMan 113/01/2011 13/05/2015LondonMaletest1 37.5No01cc1White - Any other background13/05/2015Yes2011Jan2015May15814.34055642137
3Worker 22JT2JL250kXTest 2Dep 2CurrentEmployeeMan 201/03/2019 LondonMaletest2 No01cc2White - English, Welsh, Scottish, Northern Irish, British01/03/2021Yes2019Mar 7312.043525
4Worker 33JT3JL350kXTest 3Dep 3CurrentEmployeeMan 311/01/2021 LondonMaletest3 No01cc3White - English, Welsh, Scottish, Northern Irish, British01/03/2021Yes2021Jan 490.144207
5Worker 44JT4JL450kXTest 4Dep 4FormerEmployeeMan 401/01/2012 15/07/2020LondonMaletest4 37.5No01cc4White - English, Welsh, Scottish, Northern Irish, British15/07/2020Yes2012Jan2020Jul31188.54090944027
6Worker 55JT5JL550kXTest 5Dep 5CurrentEmployeeMan 504/01/2021 LondonFemaletest5 No01cc5White - English, Welsh, Scottish, Northern Irish, British01/03/2021Yes2021Jan 560.244200
7Worker 66JT6JL650kXTest 6Dep 6CurrentEmployeeMan 630/09/2019 Milton KeynesMaletest6 No01cc6 01/03/2021Yes2019Sep 5181.443738
8Worker 77JT7JL750kXTest 7Dep 7CurrentEmployeeMan 716/01/2014 LondonMaletest7 37.5No01cc7White - English, Welsh, Scottish, Northern Irish, British01/03/2021Yes2014Jan 26017.141655
9Worker 88JT8JL850kXTest 8Dep 8FormerEmployeeMan 803/03/1986 30/09/2020HomeMaletest8 No01cc8 30/09/2020Yes1986Mar2020Sep1263034.63147444104
10Worker 99JT9JL950kXTest 9Dep 9FormerEmployeeMan 901/09/202030/09/201925/09/2020Home BasedMaletest9 No01cc9White - English, Welsh, Scottish, Northern Irish, British25/09/2020Yes2019Sep2020Sep3611.04373844099
11Worker 1010JT10JL1050kXTest 10Dep 10CurrentEmployeeMan 1006/08/2012 LondonMaletest10 37.5No01cc10White - English, Welsh, Scottish, Northern Irish, British01/03/2021Yes2012Aug 31298.641127
12Worker 1111JT11JL1150kXTest 11Dep 11CurrentEmployeeMan 1109/09/2019 29/02/2016LondonMaletest11 37.5No01cc11White - English, Welsh, Scottish, Northern Irish, British29/02/2016Yes2019Sep2016Feb-1288-4371742429
13Worker 1212JT12JL1250kXTest 12Dep 12CurrentEmployeeMan 1221/05/2018 20/08/2018LondonFemaletest12 No01cc12 20/08/2018Yes2018May2018Aug910.24324143332
14Worker 1313JT13JL1350kXTest 13Dep 13CurrentEmployeeMan 1303/12/2018 LondonMaletest13 37.5No01cc13White - English, Welsh, Scottish, Northern Irish, British01/03/2021Yes2018Dec 8192.243437
15Worker 1414JT14JL1450kXTest 14Dep 14CurrentEmployeeMan 1401/04/200301/04/2003 TelfordFemaletest14 37.5No01cc14Chinese - British01/03/2021Yes2003Apr 654417.937712
16Worker 1515JT15JL1550kXTest 15Dep 15FormerEmployeeMan 1501/05/2012 01/07/2019LondonMaletest15 37.5No01cc15White - English, Welsh, Scottish, Northern Irish, British01/07/2019Yes2012May2019Jul26177.24103043647
17Worker 1616JT16JL1650kXTest 16Dep 16CurrentEmployeeMan 1602/04/201902/04/2019 LondonMaletest16 37.5No01cc16White - English, Welsh, Scottish, Northern Irish, British01/03/2021Yes2019Apr 6991.943557
18Worker 1717JT17JL1750kXTest 17Dep 17CurrentEmployeeMan 1707/09/2020 LondonMaletest17 No01cc17White - English, Welsh, Scottish, Northern Irish, British01/03/2021Yes2020Sep 1750.544081
19Worker 1818JT18JL1850kXTest 18Dep 18CurrentEmployeeMan 1801/12/2013 08/07/2021LondonMaletest18 37.5No01cc18White - English, Welsh, Scottish, Northern Irish, British08/07/2021Yes2013Dec2021Jul27767.64160944385
20
212020Sep
22Leave MonthFull NameCompanyDepartmentDivisionLocationJob TitleStart DateLeaving DateLoS (Years)Employee Type2015Jan
23SepWorker 8XDep 8Test 8HomeJT803/03/198630/09/202034.6Employee2016Feb
24SepWorker 9XDep 9Test 9Home BasedJT901/09/202025/09/20201.0Employee2017Mar
252018Apr
262019May
272020Jun
282021Jul
292022Aug
30Sep
31Oct
32Nov
33Dec
List
Cell Formulas
RangeFormula
Y2:Y19Y2=IF(ISERROR(DATE(DAY(N2),MONTH(N2),YEAR(N2))),TODAY(),N2)
Z2:Z19Z2=IF(V2>=1,"Yes","No")
AA2:AA19AA2=TEXT(AG2,"YYYY")
AB2:AB19AB2=TEXT(AG2,"MMM")
AC2:AC19AC2=TEXT(N2,"YYYY")
AD2:AD19AD2=TEXT(N2,"MMM")
AE2:AE19AE2=Y2-AG2
AF2:AF19AF2=IF(AE2/365<0,"-",AE2/365)
AG2:AG19AG2=IF(ISERROR(DATE(DAY(M2),MONTH(M2),YEAR(M2))),L2,M2)
AH2:AH19AH2=IF(ISERROR(DATE(DAY(N2),MONTH(N2),YEAR(N2)))," ",N2)
A23:K24A23=SORTBY(FILTER(FILTER(A2:AH19,(N2:N19>=DATE(A21,MONTH(B21&1),1))*(N2:N19<DATE(A21,MONTH(B21&1)+1,1))),ISNUMBER(MATCH(A1:AH1,A22:K22,0))),MATCH(COLUMN(A22:K22),{10,1,3,5,4,9,2,7,8,11,6},0),1)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
A21List=$N$22:$N$29
B21List=$O$22:$O$33
 
Solution

maxim642

New Member
Joined
Feb 4, 2021
Messages
27
Office Version
  1. 365
Platform
  1. MacOS
Ok how about
The headers in A22:K22 must be an exact match with the relevat header in row 1
+Fluff 1.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAH
1Full NameEmployee NumberJob TitleJob LevelSalaryCompanyDivisionDepartmentStatusEmployee TypeManager Full NameStart DateContinuous Start DateLeaving DateLocationGenderEmailContract End DateStandard HoursDisabledCar AllowanceActual FTECost CentreEthnicityLoS DateFull Time Y/NStart YearStart MonthLeave YearLeave MonthLoS (Days)Los (Years)Actual Start DateFormatted Leave Date
2Worker 11JT1JL150kXTest 1Dep 1CurrentEmployeeMan 113/01/2011 13/05/2015LondonMaletest1 37.5No01cc1White - Any other background13/05/2015Yes2011Jan2015May15814.34055642137
3Worker 22JT2JL250kXTest 2Dep 2CurrentEmployeeMan 201/03/2019 LondonMaletest2 No01cc2White - English, Welsh, Scottish, Northern Irish, British01/03/2021Yes2019Mar 7312.043525
4Worker 33JT3JL350kXTest 3Dep 3CurrentEmployeeMan 311/01/2021 LondonMaletest3 No01cc3White - English, Welsh, Scottish, Northern Irish, British01/03/2021Yes2021Jan 490.144207
5Worker 44JT4JL450kXTest 4Dep 4FormerEmployeeMan 401/01/2012 15/07/2020LondonMaletest4 37.5No01cc4White - English, Welsh, Scottish, Northern Irish, British15/07/2020Yes2012Jan2020Jul31188.54090944027
6Worker 55JT5JL550kXTest 5Dep 5CurrentEmployeeMan 504/01/2021 LondonFemaletest5 No01cc5White - English, Welsh, Scottish, Northern Irish, British01/03/2021Yes2021Jan 560.244200
7Worker 66JT6JL650kXTest 6Dep 6CurrentEmployeeMan 630/09/2019 Milton KeynesMaletest6 No01cc6 01/03/2021Yes2019Sep 5181.443738
8Worker 77JT7JL750kXTest 7Dep 7CurrentEmployeeMan 716/01/2014 LondonMaletest7 37.5No01cc7White - English, Welsh, Scottish, Northern Irish, British01/03/2021Yes2014Jan 26017.141655
9Worker 88JT8JL850kXTest 8Dep 8FormerEmployeeMan 803/03/1986 30/09/2020HomeMaletest8 No01cc8 30/09/2020Yes1986Mar2020Sep1263034.63147444104
10Worker 99JT9JL950kXTest 9Dep 9FormerEmployeeMan 901/09/202030/09/201925/09/2020Home BasedMaletest9 No01cc9White - English, Welsh, Scottish, Northern Irish, British25/09/2020Yes2019Sep2020Sep3611.04373844099
11Worker 1010JT10JL1050kXTest 10Dep 10CurrentEmployeeMan 1006/08/2012 LondonMaletest10 37.5No01cc10White - English, Welsh, Scottish, Northern Irish, British01/03/2021Yes2012Aug 31298.641127
12Worker 1111JT11JL1150kXTest 11Dep 11CurrentEmployeeMan 1109/09/2019 29/02/2016LondonMaletest11 37.5No01cc11White - English, Welsh, Scottish, Northern Irish, British29/02/2016Yes2019Sep2016Feb-1288-4371742429
13Worker 1212JT12JL1250kXTest 12Dep 12CurrentEmployeeMan 1221/05/2018 20/08/2018LondonFemaletest12 No01cc12 20/08/2018Yes2018May2018Aug910.24324143332
14Worker 1313JT13JL1350kXTest 13Dep 13CurrentEmployeeMan 1303/12/2018 LondonMaletest13 37.5No01cc13White - English, Welsh, Scottish, Northern Irish, British01/03/2021Yes2018Dec 8192.243437
15Worker 1414JT14JL1450kXTest 14Dep 14CurrentEmployeeMan 1401/04/200301/04/2003 TelfordFemaletest14 37.5No01cc14Chinese - British01/03/2021Yes2003Apr 654417.937712
16Worker 1515JT15JL1550kXTest 15Dep 15FormerEmployeeMan 1501/05/2012 01/07/2019LondonMaletest15 37.5No01cc15White - English, Welsh, Scottish, Northern Irish, British01/07/2019Yes2012May2019Jul26177.24103043647
17Worker 1616JT16JL1650kXTest 16Dep 16CurrentEmployeeMan 1602/04/201902/04/2019 LondonMaletest16 37.5No01cc16White - English, Welsh, Scottish, Northern Irish, British01/03/2021Yes2019Apr 6991.943557
18Worker 1717JT17JL1750kXTest 17Dep 17CurrentEmployeeMan 1707/09/2020 LondonMaletest17 No01cc17White - English, Welsh, Scottish, Northern Irish, British01/03/2021Yes2020Sep 1750.544081
19Worker 1818JT18JL1850kXTest 18Dep 18CurrentEmployeeMan 1801/12/2013 08/07/2021LondonMaletest18 37.5No01cc18White - English, Welsh, Scottish, Northern Irish, British08/07/2021Yes2013Dec2021Jul27767.64160944385
20
212020Sep
22Leave MonthFull NameCompanyDepartmentDivisionLocationJob TitleStart DateLeaving DateLoS (Years)Employee Type2015Jan
23SepWorker 8XDep 8Test 8HomeJT803/03/198630/09/202034.6Employee2016Feb
24SepWorker 9XDep 9Test 9Home BasedJT901/09/202025/09/20201.0Employee2017Mar
252018Apr
262019May
272020Jun
282021Jul
292022Aug
30Sep
31Oct
32Nov
33Dec
List
Cell Formulas
RangeFormula
Y2:Y19Y2=IF(ISERROR(DATE(DAY(N2),MONTH(N2),YEAR(N2))),TODAY(),N2)
Z2:Z19Z2=IF(V2>=1,"Yes","No")
AA2:AA19AA2=TEXT(AG2,"YYYY")
AB2:AB19AB2=TEXT(AG2,"MMM")
AC2:AC19AC2=TEXT(N2,"YYYY")
AD2:AD19AD2=TEXT(N2,"MMM")
AE2:AE19AE2=Y2-AG2
AF2:AF19AF2=IF(AE2/365<0,"-",AE2/365)
AG2:AG19AG2=IF(ISERROR(DATE(DAY(M2),MONTH(M2),YEAR(M2))),L2,M2)
AH2:AH19AH2=IF(ISERROR(DATE(DAY(N2),MONTH(N2),YEAR(N2)))," ",N2)
A23:K24A23=SORTBY(FILTER(FILTER(A2:AH19,(N2:N19>=DATE(A21,MONTH(B21&1),1))*(N2:N19<DATE(A21,MONTH(B21&1)+1,1))),ISNUMBER(MATCH(A1:AH1,A22:K22,0))),MATCH(COLUMN(A22:K22),{10,1,3,5,4,9,2,7,8,11,6},0),1)
Dynamic array formulas.
Cells with Data Validation
CellAllowCriteria
A21List=$N$22:$N$29
B21List=$O$22:$O$33
Great job! This is working perfectly. Thank you so much. Have a great evening!
 

Fluff

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

Watch MrExcel Video

Forum statistics

Threads
1,129,754
Messages
5,638,170
Members
417,011
Latest member
Amaden95

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