Index Match 3 criteria... works until the third changes

[Ronnie12345]

Hi all,

My first question here - long time reader!

I'm struggling to get an index match formula with three criteria to work. It works fine, matching the three variables until the third one varies.


The formula I'm using is

INDEX($C$5:$N$16,MATCH($B20,$B$5:$B$16,0),MATCH(D$19,$C$3:$N$3,0),MATCH(D$18,$C$4:$N$4,0))

The array C5:N16 is the first table below
The three matches are looking up using cells in the second table
Match 1: B20 is the date
Match 2: D19 is the country
Match 3: D18 is the fruit

All is well until the fruit changes.


[TABLE="width: 832"]
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]TABLE[/TD]
[TD="width: 64"]France[/TD]
[TD="width: 64"]France[/TD]
[TD="width: 64"]France[/TD]
[TD="width: 64"]France[/TD]
[TD="width: 64"]Spain[/TD]
[TD="width: 64"]Spain[/TD]
[TD="width: 64"]Spain[/TD]
[TD="width: 64"]Spain[/TD]
[TD="width: 64"]Italy[/TD]
[TD="width: 64"]Italy[/TD]
[TD="width: 64"]Italy[/TD]
[TD="width: 64"]Italy[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Apples[/TD]
[TD]Pears[/TD]
[TD]Bananas[/TD]
[TD]Kiwis[/TD]
[TD]Apples[/TD]
[TD]Pears[/TD]
[TD]Bananas[/TD]
[TD]Kiwis[/TD]
[TD]Apples[/TD]
[TD]Pears[/TD]
[TD]Bananas[/TD]
[TD]Kiwis[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Jan-17[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]1013[/TD]
[TD="align: right"]1025[/TD]
[TD="align: right"]1037[/TD]
[TD="align: right"]1049[/TD]
[TD="align: right"]1061[/TD]
[TD="align: right"]1073[/TD]
[TD="align: right"]1085[/TD]
[TD="align: right"]1097[/TD]
[TD="align: right"]1109[/TD]
[TD="align: right"]1121[/TD]
[TD="align: right"]1133[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Feb-17[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]1014[/TD]
[TD="align: right"]1026[/TD]
[TD="align: right"]1038[/TD]
[TD="align: right"]1050[/TD]
[TD="align: right"]1062[/TD]
[TD="align: right"]1074[/TD]
[TD="align: right"]1086[/TD]
[TD="align: right"]1098[/TD]
[TD="align: right"]1110[/TD]
[TD="align: right"]1122[/TD]
[TD="align: right"]1134[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Mar-17[/TD]
[TD="align: right"]1003[/TD]
[TD="align: right"]1015[/TD]
[TD="align: right"]1027[/TD]
[TD="align: right"]1039[/TD]
[TD="align: right"]1051[/TD]
[TD="align: right"]1063[/TD]
[TD="align: right"]1075[/TD]
[TD="align: right"]1087[/TD]
[TD="align: right"]1099[/TD]
[TD="align: right"]1111[/TD]
[TD="align: right"]1123[/TD]
[TD="align: right"]1135[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Apr-17[/TD]
[TD="align: right"]1004[/TD]
[TD="align: right"]1016[/TD]
[TD="align: right"]1028[/TD]
[TD="align: right"]1040[/TD]
[TD="align: right"]1052[/TD]
[TD="align: right"]1064[/TD]
[TD="align: right"]1076[/TD]
[TD="align: right"]1088[/TD]
[TD="align: right"]1100[/TD]
[TD="align: right"]1112[/TD]
[TD="align: right"]1124[/TD]
[TD="align: right"]1136[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]May-17[/TD]
[TD="align: right"]1005[/TD]
[TD="align: right"]1017[/TD]
[TD="align: right"]1029[/TD]
[TD="align: right"]1041[/TD]
[TD="align: right"]1053[/TD]
[TD="align: right"]1065[/TD]
[TD="align: right"]1077[/TD]
[TD="align: right"]1089[/TD]
[TD="align: right"]1101[/TD]
[TD="align: right"]1113[/TD]
[TD="align: right"]1125[/TD]
[TD="align: right"]1137[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Jun-17[/TD]
[TD="align: right"]1006[/TD]
[TD="align: right"]1018[/TD]
[TD="align: right"]1030[/TD]
[TD="align: right"]1042[/TD]
[TD="align: right"]1054[/TD]
[TD="align: right"]1066[/TD]
[TD="align: right"]1078[/TD]
[TD="align: right"]1090[/TD]
[TD="align: right"]1102[/TD]
[TD="align: right"]1114[/TD]
[TD="align: right"]1126[/TD]
[TD="align: right"]1138[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Jul-17[/TD]
[TD="align: right"]1007[/TD]
[TD="align: right"]1019[/TD]
[TD="align: right"]1031[/TD]
[TD="align: right"]1043[/TD]
[TD="align: right"]1055[/TD]
[TD="align: right"]1067[/TD]
[TD="align: right"]1079[/TD]
[TD="align: right"]1091[/TD]
[TD="align: right"]1103[/TD]
[TD="align: right"]1115[/TD]
[TD="align: right"]1127[/TD]
[TD="align: right"]1139[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Aug-17[/TD]
[TD="align: right"]1008[/TD]
[TD="align: right"]1020[/TD]
[TD="align: right"]1032[/TD]
[TD="align: right"]1044[/TD]
[TD="align: right"]1056[/TD]
[TD="align: right"]1068[/TD]
[TD="align: right"]1080[/TD]
[TD="align: right"]1092[/TD]
[TD="align: right"]1104[/TD]
[TD="align: right"]1116[/TD]
[TD="align: right"]1128[/TD]
[TD="align: right"]1140[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Sep-17[/TD]
[TD="align: right"]1009[/TD]
[TD="align: right"]1021[/TD]
[TD="align: right"]1033[/TD]
[TD="align: right"]1045[/TD]
[TD="align: right"]1057[/TD]
[TD="align: right"]1069[/TD]
[TD="align: right"]1081[/TD]
[TD="align: right"]1093[/TD]
[TD="align: right"]1105[/TD]
[TD="align: right"]1117[/TD]
[TD="align: right"]1129[/TD]
[TD="align: right"]1141[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Oct-17[/TD]
[TD="align: right"]1010[/TD]
[TD="align: right"]1022[/TD]
[TD="align: right"]1034[/TD]
[TD="align: right"]1046[/TD]
[TD="align: right"]1058[/TD]
[TD="align: right"]1070[/TD]
[TD="align: right"]1082[/TD]
[TD="align: right"]1094[/TD]
[TD="align: right"]1106[/TD]
[TD="align: right"]1118[/TD]
[TD="align: right"]1130[/TD]
[TD="align: right"]1142[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Nov-17[/TD]
[TD="align: right"]1011[/TD]
[TD="align: right"]1023[/TD]
[TD="align: right"]1035[/TD]
[TD="align: right"]1047[/TD]
[TD="align: right"]1059[/TD]
[TD="align: right"]1071[/TD]
[TD="align: right"]1083[/TD]
[TD="align: right"]1095[/TD]
[TD="align: right"]1107[/TD]
[TD="align: right"]1119[/TD]
[TD="align: right"]1131[/TD]
[TD="align: right"]1143[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]Dec-17[/TD]
[TD="align: right"]1012[/TD]
[TD="align: right"]1024[/TD]
[TD="align: right"]1036[/TD]
[TD="align: right"]1048[/TD]
[TD="align: right"]1060[/TD]
[TD="align: right"]1072[/TD]
[TD="align: right"]1084[/TD]
[TD="align: right"]1096[/TD]
[TD="align: right"]1108[/TD]
[TD="align: right"]1120[/TD]
[TD="align: right"]1132[/TD]
[TD="align: right"]1144[/TD]
[/TR]
</tbody>[/TABLE]

TABLE 2

[TABLE="width: 832"]
<colgroup><col width="64" span="13" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"]Apples[/TD]
[TD="width: 64"]Apples[/TD]
[TD="width: 64"]Apples[/TD]
[TD="width: 64"]Pears[/TD]
[TD="width: 64"]Pears[/TD]
[TD="width: 64"]Pears[/TD]
[TD="width: 64"]Bananas[/TD]
[TD="width: 64"]Bananas[/TD]
[TD="width: 64"]Bananas[/TD]
[TD="width: 64"]Kiwis[/TD]
[TD="width: 64"]Kiwis[/TD]
[TD="width: 64"]Kiwis[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]France[/TD]
[TD]Spain[/TD]
[TD]Italy[/TD]
[TD]France[/TD]
[TD]Spain[/TD]
[TD]Italy[/TD]
[TD]France[/TD]
[TD]Spain[/TD]
[TD]Italy[/TD]
[TD]France[/TD]
[TD]Spain[/TD]
[TD]Italy[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jan-17[/TD]
[TD="align: right"]1001[/TD]
[TD="align: right"]1049[/TD]
[TD="align: right"]1097[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Feb-17[/TD]
[TD="align: right"]1002[/TD]
[TD="align: right"]1050[/TD]
[TD="align: right"]1098[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Mar-17[/TD]
[TD="align: right"]1003[/TD]
[TD="align: right"]1051[/TD]
[TD="align: right"]1099[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Apr-17[/TD]
[TD="align: right"]1004[/TD]
[TD="align: right"]1052[/TD]
[TD="align: right"]1100[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]May-17[/TD]
[TD="align: right"]1005[/TD]
[TD="align: right"]1053[/TD]
[TD="align: right"]1101[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jun-17[/TD]
[TD="align: right"]1006[/TD]
[TD="align: right"]1054[/TD]
[TD="align: right"]1102[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Jul-17[/TD]
[TD="align: right"]1007[/TD]
[TD="align: right"]1055[/TD]
[TD="align: right"]1103[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Aug-17[/TD]
[TD="align: right"]1008[/TD]
[TD="align: right"]1056[/TD]
[TD="align: right"]1104[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Sep-17[/TD]
[TD="align: right"]1009[/TD]
[TD="align: right"]1057[/TD]
[TD="align: right"]1105[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Oct-17[/TD]
[TD="align: right"]1010[/TD]
[TD="align: right"]1058[/TD]
[TD="align: right"]1106[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Nov-17[/TD]
[TD="align: right"]1011[/TD]
[TD="align: right"]1059[/TD]
[TD="align: right"]1107[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]Dec-17[/TD]
[TD="align: right"]1012[/TD]
[TD="align: right"]1060[/TD]
[TD="align: right"]1108[/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[TD="align: center"]#REF![/TD]
[/TR]
</tbody>[/TABLE]


I've had a hunt around and can't find this exact question being asked before so apologies if it has.
Can anyone help, please?

Cheers,
Ronnie

 

[63falcondude]

I would use SUMPRODUCT here

Excel 2010
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	TABLE	France	France	France	France	Spain	Spain	Spain	Spain	Italy	Italy	Italy	Italy
2		Apples	Pears	Bananas	Kiwis	Apples	Pears	Bananas	Kiwis	Apples	Pears	Bananas	Kiwis
3	Jan-17	1001	1013	1025	1037	1049	1061	1073	1085	1097	1109	1121	1133
4	Feb-17	1002	1014	1026	1038	1050	1062	1074	1086	1098	1110	1122	1134
5	Mar-17	1003	1015	1027	1039	1051	1063	1075	1087	1099	1111	1123	1135
6	Apr-17	1004	1016	1028	1040	1052	1064	1076	1088	1100	1112	1124	1136
7	May-17	1005	1017	1029	1041	1053	1065	1077	1089	1101	1113	1125	1137
8	Jun-17	1006	1018	1030	1042	1054	1066	1078	1090	1102	1114	1126	1138
9	Jul-17	1007	1019	1031	1043	1055	1067	1079	1091	1103	1115	1127	1139
10	Aug-17	1008	1020	1032	1044	1056	1068	1080	1092	1104	1116	1128	1140
11	Sep-17	1009	1021	1033	1045	1057	1069	1081	1093	1105	1117	1129	1141
12	Oct-17	1010	1022	1034	1046	1058	1070	1082	1094	1106	1118	1130	1142
13	Nov-17	1011	1023	1035	1047	1059	1071	1083	1095	1107	1119	1131	1143
14	Dec-17	1012	1024	1036	1048	1060	1072	1084	1096	1108	1120	1132	1144
15
16
17	TABLE 2
18		Apples	Apples	Apples	Pears	Pears	Pears	Bananas	Bananas	Bananas	Kiwis	Kiwis	Kiwis
19		France	Spain	Italy	France	Spain	Italy	France	Spain	Italy	France	Spain	Italy
20	Jan-17	1001	1049	1097	1013	1061	1109	1025	1073	1121	1037	1085	1133
21	Feb-17	1002	1050	1098	1014	1062	1110	1026	1074	1122	1038	1086	1134
22	Mar-17	1003	1051	1099	1015	1063	1111	1027	1075	1123	1039	1087	1135
23	Apr-17	1004	1052	1100	1016	1064	1112	1028	1076	1124	1040	1088	1136
24	May-17	1005	1053	1101	1017	1065	1113	1029	1077	1125	1041	1089	1137
25	Jun-17	1006	1054	1102	1018	1066	1114	1030	1078	1126	1042	1090	1138
26	Jul-17	1007	1055	1103	1019	1067	1115	1031	1079	1127	1043	1091	1139
27	Aug-17	1008	1056	1104	1020	1068	1116	1032	1080	1128	1044	1092	1140
28	Sep-17	1009	1057	1105	1021	1069	1117	1033	1081	1129	1045	1093	1141
29	Oct-17	1010	1058	1106	1022	1070	1118	1034	1082	1130	1046	1094	1142
30	Nov-17	1011	1059	1107	1023	1071	1119	1035	1083	1131	1047	1095	1143
31	Dec-17	1012	1060	1108	1024	1072	1120	1036	1084	1132	1048	1096	1144
Sheet1

Cell Formulas
Range	Formula
B20	=SUMPRODUCT(($B$2:$M$2=B$18)*($B$1:$M$1=B$19)*($A$3:$A$14=$A20)*($B$3:$M$14))

 

[63falcondude]

If you wanted to use an INDEX MATCH formula, one working solution looks like this:

=INDEX($B$3:$M$14,MATCH($A20,$A$3:$A$14,0),MATCH(B$18&B$19,$B$2:$M$2&$B$1:$M$1,0)) Ctrl Shift Enter

The parameters of the INDEX function are: "array", "row number", "column number"

Note that the references in this formula are based off of post #2.

 

[snjpverma]

Enter the formula and press Ctrl+Shift+Enter. Not just enter.

=INDEX($C$5:$N$16,MATCH($B20,$B$5:$B$16,0),MATCH(C$18&C$19,$C$4:$N$4&$C$3:$N$3,0))

 

[Ronnie12345]

Thanks so - multiple solutions! Much appreciated.

 

[63falcondude]

Thanks so - multiple solutions! Much appreciated.

Glad to help!