Index/Match with Multiple Criteria Sets

[DShack]

Hello All,

I am attempting to write a statement that will take 4 different criteria and match it with a data set in another sheet. I am trying to match on name, date, reg, ot to bring back the hours and overtime worked on various dates. I have tried using this formula but continue to get REF errors. =INDEX('5.11'!$C$7:$D$25,MATCH(1,(B11='5.11'!$B$7:$B$25)*(Master!C6='5.11'!$C$6)*(Master!D6='5.11'!D6)*(C5='5.11'!C5),0))

The example below i should be able to pull 3 and 1.5 hours of REG and OT for B, Roger

 

[KRice]

Your example isn't clear. You have the follow four conditions:

(B11='5.11'!$B$7:$B$25)*
(Master!C6='5.11'!$C$6)*
(Master!D6='5.11'!D6)*
(C5='5.11'!C5)

What is the sheet name in your example? How are the data arranged in sheets 5.11 and Master? As written, your last three conditions each compare a single value to another single value, resulting in either a TRUE or FALSE, which is then multiplied by an array of TRUE's and FALSE's. A single FALSE in any of the last three conditions renders 0's in the entire resulting array, and the MATCH function will never find a "1". What are the ranges on the Master and 5.11 sheets that you need to consider?

 

[DShack]

Hi Kirk,

Here is the full view from both sheets that i am working with. I am trying to pull the information from 5.11 into the Master. I have several other dates that i need to pull over but this provides an example. Does that help?

 

[KRice]

Given this in your 5.11 sheet:

1650658826268.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	WEEKLY CALCULATION
3			SATURDAY		SUNDAY		MONDAY		TUESDAY		WEDNESDAY		THURSDAY		FRIDAY
4			5/5/2018	5/5/2018	5/6/2018	5/6/2018	5/7/2018	5/7/2018	5/8/2018	5/8/2018	5/9/2018	5/9/2018	5/10/2018	5/10/2018	5/11/2018	5/11/2018
5			REG	OT	REG	OT	REG	OT	REG	OT	REG	OT	REG	OT	REG	OT
6	1	xxxB, Maryxxx	0	0	0	0	0	0	3	0.5	3	0.5	3	1	4	0
7	2	xxxM, Richardxxx	0	0	0	0	0	0	3	0.5	0	0	3	1	0	0
8	3	xxxC, Helenxxx	0	0	0	0	0	0	3	0.5	3	1.5	4	0	4	0
9	4	xxxA, Rogerxxx	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	5	xxxB, Rogerxxx	3	1.5	0	0	0	0	3	0.5	0	0	4	0	7	0.5
11	6	xxxM, Jeffreyxxx	3	1.5	0	0	0	0	3	0.5	0	0	4	0	0	0
12	7	xxxL, Osiexxx	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	8	xxxP, Fxxx	7	5	0	0	0	0	3	1	0	0	4	0.5	7	0.5
14	9	xxxL, Jeffreyxxx	7	5	0	0	0	0	3	0.5	0	0	4	0	7	0.5
15	10	xxxC, Jxxx	4	3.5	0	0	0	0	0	0	0	0	4	0	4	0
16	11	xxxS, Anaxxx	7	5.5	0	0	0	0	3	0.5	0	0	4	0	7	0.5
17	12	xxxLin, Lxxx	7	5.5	0	0	0	0	3	1	0	0	0	0	7	1.5
18	13	xxxL, Sucrexxx	7	5.5	0	0	0	0	4	0	0	0	4	0	3	1.5
19	14	xxxA, Edwardxxx	0	0	0	0	0	0	4	0	0	0	3	1.5	7	0.5
20	15	xxxZ, Josexxx	4	3.5	0	0	0	0	4	0	0	0	0	0	7	0.5
21	16	xxxW, Basilioxxx	4	3.5	0	0	0	0	0	0	0	0	7	1.5	0	0
22	17	xxxS,Jeremyxxx	4	3.5	0	0	0	0	0	0	0	0	4	0	3	0.5
23	18	xxxG, Gabrielxxx	4	3.5	4	0.5	0	0	0	0	0	0	0	0	3	1
24	19	xxxW, Marioxxx	4	3.5	4	0.5	0	0	0	0	0	0	4	0	3	0.5
5.11

...try this in your Master sheet:

1650658826268.xlsx
	A	B	C	D	E	F	G	H	I
1
2
3			SATURDAY		SUNDAY		MONDAY		TUESDAY
4			5/5/2018	5/5/2018	5/6/2018	5/6/2018	5/7/2018	5/7/2018	5/8/2018
5			REG	OT	REG	OT	REG	OT	REG
6	1	xxxB, Maryxxx	0	0	0	0	0	0	3
7	2	xxxM, Richardxxx	0	0	0	0	0	0	3
8	3	xxxC, Helenxxx	0	0	0	0	0	0	3
9	4	xxxA, Rogerxxx	0	0	0	0	0	0	0
10	5	xxxB, Rogerxxx	3	1.5	0	0	0	0	3
11	6	xxxM, Jeffreyxxx	3	1.5	0	0	0	0	3
Master

Cell Formulas
Range	Formula
C6:I11	C6	=INDEX('5.11'!$C$6:$P$24,MATCH($B6,'5.11'!$B$6:$B$24,0),MATCH(1,(C$4='5.11'!$C$4:$P$4)*(C$5='5.11'!$C$5:$P$5),0))

Adjust the ranges as necessary and be mindful of which references need to be "fixed".

 

[DShack]

Given this in your 5.11 sheet:

1650658826268.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2	WEEKLY CALCULATION
3			SATURDAY		SUNDAY		MONDAY		TUESDAY		WEDNESDAY		THURSDAY		FRIDAY
4			5/5/2018	5/5/2018	5/6/2018	5/6/2018	5/7/2018	5/7/2018	5/8/2018	5/8/2018	5/9/2018	5/9/2018	5/10/2018	5/10/2018	5/11/2018	5/11/2018
5			REG	OT	REG	OT	REG	OT	REG	OT	REG	OT	REG	OT	REG	OT
6	1	xxxB, Maryxxx	0	0	0	0	0	0	3	0.5	3	0.5	3	1	4	0
7	2	xxxM, Richardxxx	0	0	0	0	0	0	3	0.5	0	0	3	1	0	0
8	3	xxxC, Helenxxx	0	0	0	0	0	0	3	0.5	3	1.5	4	0	4	0
9	4	xxxA, Rogerxxx	0	0	0	0	0	0	0	0	0	0	0	0	0	0
10	5	xxxB, Rogerxxx	3	1.5	0	0	0	0	3	0.5	0	0	4	0	7	0.5
11	6	xxxM, Jeffreyxxx	3	1.5	0	0	0	0	3	0.5	0	0	4	0	0	0
12	7	xxxL, Osiexxx	0	0	0	0	0	0	0	0	0	0	0	0	0	0
13	8	xxxP, Fxxx	7	5	0	0	0	0	3	1	0	0	4	0.5	7	0.5
14	9	xxxL, Jeffreyxxx	7	5	0	0	0	0	3	0.5	0	0	4	0	7	0.5
15	10	xxxC, Jxxx	4	3.5	0	0	0	0	0	0	0	0	4	0	4	0
16	11	xxxS, Anaxxx	7	5.5	0	0	0	0	3	0.5	0	0	4	0	7	0.5
17	12	xxxLin, Lxxx	7	5.5	0	0	0	0	3	1	0	0	0	0	7	1.5
18	13	xxxL, Sucrexxx	7	5.5	0	0	0	0	4	0	0	0	4	0	3	1.5
19	14	xxxA, Edwardxxx	0	0	0	0	0	0	4	0	0	0	3	1.5	7	0.5
20	15	xxxZ, Josexxx	4	3.5	0	0	0	0	4	0	0	0	0	0	7	0.5
21	16	xxxW, Basilioxxx	4	3.5	0	0	0	0	0	0	0	0	7	1.5	0	0
22	17	xxxS,Jeremyxxx	4	3.5	0	0	0	0	0	0	0	0	4	0	3	0.5
23	18	xxxG, Gabrielxxx	4	3.5	4	0.5	0	0	0	0	0	0	0	0	3	1
24	19	xxxW, Marioxxx	4	3.5	4	0.5	0	0	0	0	0	0	4	0	3	0.5
5.11

...try this in your Master sheet:

1650658826268.xlsx
	A	B	C	D	E	F	G	H	I
1
2
3			SATURDAY		SUNDAY		MONDAY		TUESDAY
4			5/5/2018	5/5/2018	5/6/2018	5/6/2018	5/7/2018	5/7/2018	5/8/2018
5			REG	OT	REG	OT	REG	OT	REG
6	1	xxxB, Maryxxx	0	0	0	0	0	0	3
7	2	xxxM, Richardxxx	0	0	0	0	0	0	3
8	3	xxxC, Helenxxx	0	0	0	0	0	0	3
9	4	xxxA, Rogerxxx	0	0	0	0	0	0	0
10	5	xxxB, Rogerxxx	3	1.5	0	0	0	0	3
11	6	xxxM, Jeffreyxxx	3	1.5	0	0	0	0	3
Master

Cell Formulas
Range	Formula
C6:I11	C6	=INDEX('5.11'!$C$6:$P$24,MATCH($B6,'5.11'!$B$6:$B$24,0),MATCH(1,(C$4='5.11'!$C$4:$P$4)*(C$5='5.11'!$C$5:$P$5),0))

Adjust the ranges as necessary and be mindful of which references need to be "fixed".

Thank you so much! This worked and I understand what i did wrong.

 

[KRice]

You're welcome...I'm happy to help. In your example, because you need a two-way lookup, the INDEX(array,row,column) function incorporates separate MATCH functions for the row and column positions.