Index/Match with multiple criteria, one being dynamic

Ubiquitous

New Member
Joined
Jan 20, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
Hi, I'm working on a rota based sheet that I need to identify all those people on duty on a particular day on a particular geographical area. My organisation is using Windows 2016 which is annoying as I believe the FILTER function would have worked.

I've attached a sample data base. I have employee ID numbers and names along with the sector they cover. On a separate worksheet I want three drop down lists that the user can choose DATE, SECTOR and SHIFT.

The sector information is locked to column D but to filter everyone that is also on Early (E) for that sector on a given date means that the Match (or Lookup) needs to switch to select the relevant column depending on the date.

I then want the form to list all those that match the criteria. The real dataset has about 250 employees on it and 80 could meet the criteria on a given day.

I've tried different variations of COUNTIF, ADDRESS, OFFSET, INDIRECT etc but I can't seem to get it to work. I've achieved this task before with AGGREGATE and only one matched criteria but the combination of a fixed and dynamic Match criteria is frying my brain!
 

Attachments

  • Screenshot 2021-02-17 at 21.04.24.png
    Screenshot 2021-02-17 at 21.04.24.png
    72.1 KB · Views: 6

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Toadstool

Well-known Member
Joined
Mar 5, 2018
Messages
1,384
Office Version
  1. 2016
Platform
  1. Windows
Hi Ubiquitous,

I'm not sure if I'm following completely but maybe this is something you could use to point in the right direction.

Ubiquitous.xlsx
BCDEFGHIJKLMNOPQ
1IdNameSector01-Apr-2102-Apr-2103-Apr-2104-Apr-2105-Apr-2106-Apr-21SelectionNamesSectorShift DateShift
211Jon VerleEastELEEEEJim JonesJim JonesNorth04-Apr-21E
322Sidney GrassSouthLEELEEChuck Bitt
433Jim JonesNorthOffLLEELIvor Engin
544Sue PouleSouthHolidayEEEEE 
655Nora CarotSouthELELEE 
766Chuck BittNorthLOffLELL 
877Ivor EnginNorthEHolidayOffELOff 
988Vera LefftWestHolidayHolidayEHolidayHoliday 
10 
Sheet1
Cell Formulas
RangeFormula
F1:J1F1=E1+1
N2:N10N2=IFERROR(INDEX($C$2:$C$9999,AGGREGATE(15,6,ROW($C$2:$C$9999)-ROW($C$1)/(($E$1:$J$1=$P$2)*($E$2:$J$9999=$Q$2)*($D$2:$D$9999=$O$2)),ROW()-ROW($N$1))),"")
Cells with Data Validation
CellAllowCriteria
L2List=OFFSET($N$2,,,COUNTIF($N$2:$N$80,"> "))
 
Solution

Ubiquitous

New Member
Joined
Jan 20, 2021
Messages
3
Office Version
  1. 2016
Platform
  1. Windows
P
Hi Ubiquitous,

I'm not sure if I'm following completely but maybe this is something you could use to point in the right direction.

Ubiquitous.xlsx
BCDEFGHIJKLMNOPQ
1IdNameSector01-Apr-2102-Apr-2103-Apr-2104-Apr-2105-Apr-2106-Apr-21SelectionNamesSectorShift DateShift
211Jon VerleEastELEEEEJim JonesJim JonesNorth04-Apr-21E
322Sidney GrassSouthLEELEEChuck Bitt
433Jim JonesNorthOffLLEELIvor Engin
544Sue PouleSouthHolidayEEEEE 
655Nora CarotSouthELELEE 
766Chuck BittNorthLOffLELL 
877Ivor EnginNorthEHolidayOffELOff 
988Vera LefftWestHolidayHolidayEHolidayHoliday 
10 
Sheet1
Cell Formulas
RangeFormula
F1:J1F1=E1+1
N2:N10N2=IFERROR(INDEX($C$2:$C$9999,AGGREGATE(15,6,ROW($C$2:$C$9999)-ROW($C$1)/(($E$1:$J$1=$P$2)*($E$2:$J$9999=$Q$2)*($D$2:$D$9999=$O$2)),ROW()-ROW($N$1))),"")
Cells with Data Validation
CellAllowCriteria
L2List=OFFSET($N$2,,,COUNTIF($N$2:$N$80,"> "))
perfect, thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,387
Messages
5,624,389
Members
416,026
Latest member
melvic69

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