Index/Match with multiple criteria, one being dynamic

[Ubiquitous]

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!

 

[Toadstool]

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
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Id	Name	Sector	01-Apr-21	02-Apr-21	03-Apr-21	04-Apr-21	05-Apr-21	06-Apr-21		Selection		Names	Sector	Shift Date	Shift
2	11	Jon Verle	East	E	L	E	E	E	E		Jim Jones		Jim Jones	North	04-Apr-21	E
3	22	Sidney Grass	South	L	E	E	L	E	E				Chuck Bitt
4	33	Jim Jones	North	Off	L	L	E	E	L				Ivor Engin
5	44	Sue Poule	South	Holiday	E	E	E	E	E
6	55	Nora Carot	South	E	L	E	L	E	E
7	66	Chuck Bitt	North	L	Off	L	E	L	L
8	77	Ivor Engin	North	E	Holiday	Off	E	L	Off
9	88	Vera Lefft	West	Holiday		Holiday	E	Holiday	Holiday
10
Sheet1

Cell Formulas
Range	Formula
F1:J1	F1	=E1+1
N2:N10	N2	=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
Cell	Allow	Criteria
L2	List	=OFFSET($N$2,,,COUNTIF($N$2:$N$80,"> "))

 

[Ubiquitous]

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
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Id	Name	Sector	01-Apr-21	02-Apr-21	03-Apr-21	04-Apr-21	05-Apr-21	06-Apr-21		Selection		Names	Sector	Shift Date	Shift
2	11	Jon Verle	East	E	L	E	E	E	E		Jim Jones		Jim Jones	North	04-Apr-21	E
3	22	Sidney Grass	South	L	E	E	L	E	E				Chuck Bitt
4	33	Jim Jones	North	Off	L	L	E	E	L				Ivor Engin
5	44	Sue Poule	South	Holiday	E	E	E	E	E
6	55	Nora Carot	South	E	L	E	L	E	E
7	66	Chuck Bitt	North	L	Off	L	E	L	L
8	77	Ivor Engin	North	E	Holiday	Off	E	L	Off
9	88	Vera Lefft	West	Holiday		Holiday	E	Holiday	Holiday
10
Sheet1

Cell Formulas
Range	Formula
F1:J1	F1	=E1+1
N2:N10	N2	=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
Cell	Allow	Criteria
L2	List	=OFFSET($N$2,,,COUNTIF($N$2:$N$80,"> "))

perfect, thank you!