Formula to retrieve a value based on multiple criteria

[Sandman1985]

Have been trying to figure out how to look up and retrieve values in an array with multiple criteria.
Have attempted an INDEX and MATCH formula with no success.
I have a Resource plan which has all resources, skill levels and whether or not the are engaged on a particular date.
On a separate sheet, I am trying to retrieve the all of the resources on a given date that are not engaged.
Not sure if the formula I have been using is the right one but any help would be appreciated.
The resource schedule image is the underlying dataset. The circled stuff in the available image is what I am trying to get my formula to retrieve. I managed to build the below where BNG is the Resource plan, column B being the resources, scheduler view being the sheet for available resources, then the first match being date and second match being empty to signify available.
=INDEX(BNG'!B13:B69,MATCH(1,('Scheduler View'!C7='BNG'!G9)*('Scheduler View'!B1='BNG'!G13:G69),0))

 

[KRice]

Using this as a data source:

MrExcel_20220619.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Resource	Skill	1-Jul	2-Jul	3-Jul	4-Jul	5-Jul	6-Jul	7-Jul	8-Jul
2	Resource 1	1		Engaged
3	Resource 2	2	Engaged	Engaged
4	Resource 3	1	Engaged	Engaged					Engaged
5	Resource 4	3	Engaged	Engaged					Engaged
6	Resource 5	2	Engaged	Engaged					Engaged
7	Resource 6	1	Engaged						Engaged
8	Resource 7	2							Engaged
9	Resource 8	2
10	Resource 9	2		Engaged
11	Resource 10	1	Engaged	Engaged
12	Resource 11	1	Engaged	Engaged					Engaged
13	Resource 12	1	Engaged	Engaged					Engaged
14	Resource 13	3	Engaged						Engaged
15	Resource 14	2							Engaged
16	Resource 15	3
17	Resource 16	3		Engaged
18	Resource 17	2	Engaged	Engaged
19	Resource 18	1	Engaged	Engaged					Engaged
20	Resource 19	2	Engaged						Engaged
21	Resource 20	1							Engaged
22	Resource 21	1
Sheet9

...then the nested FILTER formula pasted into the yellow cells will produce your desired output. In this case, paste into cells B3, B12, B19 and drag across the row...the results will spill down the columns.

MrExcel_20220619.xlsx
	A	B	C	D
1
2	Skill	7/1/2022	7/2/2022	7/7/2022
3	1	Resource 3	Resource 1	Resource 3
4		Resource 6	Resource 3	Resource 6
5		Resource 10	Resource 10	Resource 11
6		Resource 11	Resource 11	Resource 12
7		Resource 12	Resource 12	Resource 18
8		Resource 18	Resource 18	Resource 20
9
10
11
12	2	Resource 2	Resource 2	Resource 5
13		Resource 5	Resource 5	Resource 7
14		Resource 17	Resource 9	Resource 14
15		Resource 19	Resource 17	Resource 19
16
17
18
19	3	Resource 4	Resource 4	Resource 4
20		Resource 13	Resource 16	Resource 13
21
22
Sheet10

Cell Formulas
Range	Formula
B19:D20,B12:D15,B3:D8	B3	=FILTER(Sheet9!$A$2:$A$22,(FILTER(Sheet9!$C$2:$J$22,Sheet9!$C$1:$J$1=B$2)="Engaged")*(Sheet9!$B$2:$B$22=Sheet10!$A3))
Dynamic array formulas.

If you are unfamiliar with the XL2BB add-in, you can install it from the link in my signature line. The add-in allows you to post snippets from your spreadsheet, including the formulas. And to generate what has been posted, you can simply click on the clipboard icon in the upper corner of the posted mini-sheet and paste into your own worksheet.

 

[Sandman1985]

Hi KRice,

This is fantastic. Only issue is, it keeps only giving me the first result rather than the subsequent results i.e. in Sheet 10 I keep getting the same resource. Is this because I need the add-in?
Will download the add-in now.

Thanks for your help. Appreciate it.

S

 

[Sandman1985]

Hi KRice,

Realised I'm an idiot. I was entering the array formula rather than just for the first one.
Thanks so much for your help.

Cheers,

S

 

[KRice]

Glad to hear you got it working...I'm happy to help.