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