Matching on 3 Criteria Formula Troubles

[Pineapple_Crazy]

Hello,

I'm trying to match on 3 criteria to return a result, but no matter what I have tried it hasn't worked. In the example attached I am trying to match "Rms Cable Television", "Jan-18", and "ACTUAL" (all in bold below), to return a value. The formula I am attempting to use is =INDEX(C3:AB50,MATCH(B2,C3:C50,0),MATCH(B3,C3:AB3,0),MATCH(B4,C4:AB4,0)) (shown in #REF ! area below which would be in cell A2). The criteria "Rms Cable Television", "Jan-18", and "ACTUAL" start in cell B2. In the particular example provided I have in bold the criteria in the data set I am trying to match. I am trying to receive the value of 0.62. Would someone suggest a good formula for the way this data is set up in the workbook to achieve the desired result ? The formula seems to work for 2 criteria, but not for 3. Thanks so much!

PC

[TABLE="width: 1583"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD] Rooms Department - ACTUAL[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: center"]#REF ![/TD]
[TD]Rms Cable Television[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Jan-18[/TD]
[TD]Available Rooms[/TD]
[TD]Jan-17[/TD]
[TD]Feb-17[/TD]
[TD]Mar-17[/TD]
[TD]Apr-17[/TD]
[TD]May-17[/TD]
[TD]Jun-17[/TD]
[TD]Jul-17[/TD]
[TD]Aug-17[/TD]
[TD]Sep-17[/TD]
[TD]Oct-17[/TD]
[TD]Nov-17[/TD]
[TD]Dec-17[/TD]
[TD]Jan-18[/TD]
[TD]Feb-18[/TD]
[TD]Mar-18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]ACTUAL[/TD]
[TD]Occupied Rooms[/TD]
[TD]ACTUAL[/TD]
[TD]ACTUAL[/TD]
[TD]ACTUAL[/TD]
[TD]ACTUAL[/TD]
[TD]ACTUAL[/TD]
[TD]ACTUAL[/TD]
[TD]ACTUAL[/TD]
[TD]ACTUAL[/TD]
[TD]Budget[/TD]
[TD]Budget[/TD]
[TD]Budget[/TD]
[TD]Budget[/TD]
[TD]ACTUAL[/TD]
[TD]BUDGET[/TD]
[TD]BUDGET[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] Payroll[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] Rms Payroll[/TD]
[TD] 33.68[/TD]
[TD] 31.32[/TD]
[TD] 35.57[/TD]
[TD] 34.60[/TD]
[TD] 36.13[/TD]
[TD] 36.80[/TD]
[TD] 41.00[/TD]
[TD] 39.53[/TD]
[TD] 35.06[/TD]
[TD] 32.64[/TD]
[TD] 29.33[/TD]
[TD] 30.58[/TD]
[TD] 33.81[/TD]
[TD] 32.60[/TD]
[TD] 39.06[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] Rms Benefits[/TD]
[TD] 4.38[/TD]
[TD] 4.00[/TD]
[TD] 4.47[/TD]
[TD] 3.99[/TD]
[TD] 3.89[/TD]
[TD] 3.61[/TD]
[TD] 4.50[/TD]
[TD] 4.21[/TD]
[TD] 4.25[/TD]
[TD] 3.81[/TD]
[TD] 3.51[/TD]
[TD] 3.55[/TD]
[TD] 3.69[/TD]
[TD] 3.86[/TD]
[TD] 4.22[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] Rms Payroll Taxes[/TD]
[TD] 4.09[/TD]
[TD] 3.79[/TD]
[TD] 4.12[/TD]
[TD] 3.91[/TD]
[TD] 4.10[/TD]
[TD] 4.16[/TD]
[TD] 4.64[/TD]
[TD] 4.60[/TD]
[TD] 4.07[/TD]
[TD] 3.79[/TD]
[TD] 3.40[/TD]
[TD] 3.55[/TD]
[TD] 3.94[/TD]
[TD] 3.80[/TD]
[TD] 4.55[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] Rms Total Payroll & Related[/TD]
[TD] 42.15[/TD]
[TD] 39.11[/TD]
[TD] 44.17[/TD]
[TD] 42.50[/TD]
[TD] 44.12[/TD]
[TD] 44.57[/TD]
[TD] 50.14[/TD]
[TD] 48.34[/TD]
[TD] 43.38[/TD]
[TD] 40.24[/TD]
[TD] 36.25[/TD]
[TD] 37.68[/TD]
[TD] 41.44[/TD]
[TD] 40.26[/TD]
[TD] 47.83[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] Expenses[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] Rms Cable Television [/TD]
[TD] 0.77[/TD]
[TD] 0.80[/TD]
[TD] 0.72[/TD]
[TD] 0.70[/TD]
[TD] 0.76[/TD]
[TD] 0.77[/TD]
[TD] 0.76[/TD]
[TD] 0.75[/TD]
[TD] 0.85[/TD]
[TD] 0.81[/TD]
[TD] 0.71[/TD]
[TD] 0.76[/TD]
[TD] 0.62[/TD]
[TD] 0.64[/TD]
[TD] 0.83[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] Rms Cleaning Supplies[/TD]
[TD] 0.25[/TD]
[TD] 1.14[/TD]
[TD] 1.33[/TD]
[TD] 0.63[/TD]
[TD] 1.60[/TD]
[TD] 0.53[/TD]
[TD] 1.11[/TD]
[TD] 1.62[/TD]
[TD] 0.66[/TD]
[TD] 0.63[/TD]
[TD] 0.55[/TD]
[TD] 0.59[/TD]
[TD] 0.65[/TD]
[TD] 0.67[/TD]
[TD] 0.87[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD] Rms Commissions GDS[/TD]
[TD] 1.01[/TD]
[TD] 1.58[/TD]
[TD] (1.04)[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] 0.81[/TD]
[TD] 0.77[/TD]
[TD] 0.67[/TD]
[TD] 0.72[/TD]
[TD] -[/TD]
[TD] -[/TD]
[TD] -[/TD]
[/TR]
</tbody>[/TABLE]

 

[Scott T]

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1			Rooms Department - ACTUAL
2	0.62	Rms Cable Television
3		18-Jan	Available Rooms	17-Jan	17-Feb	17-Mar	17-Apr	17-May	17-Jun	17-Jul	17-Aug	17-Sep	17-Oct	17-Nov	17-Dec	18-Jan	18-Feb	18-Mar
4		ACTUAL	Occupied Rooms	ACTUAL	ACTUAL	ACTUAL	ACTUAL	ACTUAL	ACTUAL	ACTUAL	ACTUAL	Budget	Budget	Budget	Budget	ACTUAL	BUDGET	BUDGET
5			Payroll
6			Rms Payroll	33.68	31.32	35.57	34.6	36.13	36.8	41	39.53	35.06	32.64	29.33	30.58	33.81	32.6	39.06
7			Rms Benefits	4.38	4	4.47	3.99	3.89	3.61	4.5	4.21	4.25	3.81	3.51	3.55	3.69	3.86	4.22
8			Rms Payroll Taxes	4.09	3.79	4.12	3.91	4.1	4.16	4.64	4.6	4.07	3.79	3.4	3.55	3.94	3.8	4.55
9			Rms Total Payroll & Related	42.15	39.11	44.17	42.5	44.12	44.57	50.14	48.34	43.38	40.24	36.25	37.68	41.44	40.26	47.83
10
11
12
13
14
15			Expenses
16			Rms Cable Television	0.77	0.8	0.72	0.7	0.76	0.77	0.76	0.75	0.85	0.81	0.71	0.76	0.62	0.64	0.83
17			Rms Cleaning Supplies	0.25	1.14	1.33	0.63	1.6	0.53	1.11	1.62	0.66	0.63	0.55	0.59	0.65	0.67	0.87
18			Rms Commissions GDS	1.01	1.58	-1.04	-	-	-	-	-	0.81	0.77	0.67	0.72	-	-	-
Sheet4

Cell Formulas
Range	Formula
A2	{=INDEX(D5:R18,MATCH(B2,C5:C18,0),MATCH(B3&B4,D3:R3&D4:R4,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[TD]

N​

[/TD]
[TD]

O​

[/TD]
[TD]

P​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]CRITERIA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

0.62​

[/TD]
[TD]

Rms Cable Television​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD][/TD]
[TD]

18-Jan​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][/TD]
[TD]

ACTUAL​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD]Available Rooms[/TD]
[TD]

17-Jan​

[/TD]
[TD]

17-Feb​

[/TD]
[TD]

17-Mar​

[/TD]
[TD]

17-Apr​

[/TD]
[TD]

17-May​

[/TD]
[TD]

17-Jun​

[/TD]
[TD]

17-Jul​

[/TD]
[TD]

17-Aug​

[/TD]
[TD]

17-Sep​

[/TD]
[TD]

17-Oct​

[/TD]
[TD]

17-Nov​

[/TD]
[TD]

17-Dec​

[/TD]
[TD]

18-Jan​

[/TD]
[TD]

18-Feb​

[/TD]
[TD]

18-Mar​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD]Available Rooms[/TD]
[TD]

ACTUAL​

[/TD]
[TD]

ACTUAL​

[/TD]
[TD]

ACTUAL​

[/TD]
[TD]

ACTUAL​

[/TD]
[TD]

ACTUAL​

[/TD]
[TD]

ACTUAL​

[/TD]
[TD]

ACTUAL​

[/TD]
[TD]

ACTUAL​

[/TD]
[TD]

Budget​

[/TD]
[TD]

Budget​

[/TD]
[TD]

Budget​

[/TD]
[TD]

Budget​

[/TD]
[TD]

ACTUAL​

[/TD]
[TD]

BUDGET​

[/TD]
[TD]

BUDGET​

[/TD]
[/TR]
[TR]
[TD]

8​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

9​

[/TD]
[TD]Payroll[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD]Rms Payroll[/TD]
[TD]

33.68​

[/TD]
[TD]

31.32​

[/TD]
[TD]

35.57​

[/TD]
[TD]

34.6​

[/TD]
[TD]

36.13​

[/TD]
[TD]

36.8​

[/TD]
[TD]

41​

[/TD]
[TD]

39.53​

[/TD]
[TD]

35.06​

[/TD]
[TD]

32.64​

[/TD]
[TD]

29.33​

[/TD]
[TD]

30.58​

[/TD]
[TD]

33.81​

[/TD]
[TD]

32.6​

[/TD]
[TD]

39.06​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD]Rms Benefits[/TD]
[TD]

4.38​

[/TD]
[TD]

4​

[/TD]
[TD]

4.47​

[/TD]
[TD]

3.99​

[/TD]
[TD]

3.89​

[/TD]
[TD]

3.61​

[/TD]
[TD]

4.5​

[/TD]
[TD]

4.21​

[/TD]
[TD]

4.25​

[/TD]
[TD]

3.81​

[/TD]
[TD]

3.51​

[/TD]
[TD]

3.55​

[/TD]
[TD]

3.69​

[/TD]
[TD]

3.86​

[/TD]
[TD]

4.22​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD]Rms Payroll Taxes[/TD]
[TD]

4.09​

[/TD]
[TD]

3.79​

[/TD]
[TD]

4.12​

[/TD]
[TD]

3.91​

[/TD]
[TD]

4.1​

[/TD]
[TD]

4.16​

[/TD]
[TD]

4.64​

[/TD]
[TD]

4.6​

[/TD]
[TD]

4.07​

[/TD]
[TD]

3.79​

[/TD]
[TD]

3.4​

[/TD]
[TD]

3.55​

[/TD]
[TD]

3.94​

[/TD]
[TD]

3.8​

[/TD]
[TD]

4.55​

[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD]Rms Total Payroll & Related[/TD]
[TD]

42.15​

[/TD]
[TD]

39.11​

[/TD]
[TD]

44.17​

[/TD]
[TD]

42.5​

[/TD]
[TD]

44.12​

[/TD]
[TD]

44.57​

[/TD]
[TD]

50.14​

[/TD]
[TD]

48.34​

[/TD]
[TD]

43.38​

[/TD]
[TD]

40.24​

[/TD]
[TD]

36.25​

[/TD]
[TD]

37.68​

[/TD]
[TD]

41.44​

[/TD]
[TD]

40.26​

[/TD]
[TD]

47.83​

[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD]Expenses[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD]Rms Cable Television[/TD]
[TD]

0.77​

[/TD]
[TD]

0.8​

[/TD]
[TD]

0.72​

[/TD]
[TD]

0.7​

[/TD]
[TD]

0.76​

[/TD]
[TD]

0.77​

[/TD]
[TD]

0.76​

[/TD]
[TD]

0.75​

[/TD]
[TD]

0.85​

[/TD]
[TD]

0.81​

[/TD]
[TD]

0.71​

[/TD]
[TD]

0.76​

[/TD]
[TD]

0.62​

[/TD]
[TD]

0.64​

[/TD]
[TD]

0.83​

[/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD]Rms Cleaning Supplies[/TD]
[TD]

0.25​

[/TD]
[TD]

1.14​

[/TD]
[TD]

1.33​

[/TD]
[TD]

0.63​

[/TD]
[TD]

1.6​

[/TD]
[TD]

0.53​

[/TD]
[TD]

1.11​

[/TD]
[TD]

1.62​

[/TD]
[TD]

0.66​

[/TD]
[TD]

0.63​

[/TD]
[TD]

0.55​

[/TD]
[TD]

0.59​

[/TD]
[TD]

0.65​

[/TD]
[TD]

0.67​

[/TD]
[TD]

0.87​

[/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD]Rms Commissions GDS[/TD]
[TD]

1.01​

[/TD]
[TD]

1.58​

[/TD]
[TD]

-1.04​

[/TD]
[TD]

-​

[/TD]
[TD]

-​

[/TD]
[TD]

-​

[/TD]
[TD]

-​

[/TD]
[TD]

-​

[/TD]
[TD]

0.81​

[/TD]
[TD]

0.77​

[/TD]
[TD]

0.67​

[/TD]
[TD]

0.72​

[/TD]
[TD]

-​

[/TD]
[TD]

-​

[/TD]
[TD]

-​

[/TD]
[/TR]
</tbody>[/TABLE]

A2=SUMPRODUCT((A10:A17=B2)*(B6:P6=B3)*(B7:P7=B4)*(ISNUMBER(B10:P17)),B10:P17)

 

[Pineapple_Crazy]

Thank you all!! Very much appreciated!!