Hello,
I can't figure out why I am receiving this error, I want to index and match based of either my data valuation or the concatenated version.
Results table
my data range
Any help would be apprecaited.
Thanks,
I can't figure out why I am receiving this error, I want to index and match based of either my data valuation or the concatenated version.
Results table
DATA.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | |||
2 | 06/09/2021 | <- Data validation drop down | ||||||||
3 | UK | <- Data validation drop down | ||||||||
4 | English | <- Data validation drop down | ||||||||
5 | ||||||||||
6 | ||||||||||
7 | UKEnglish44445 | UKEnglish44446 | UKEnglish44447 | UKEnglish44448 | UKEnglish44449 | UKEnglish44450 | UKEnglish44451 | |||
8 | Mon | Tue | Wed | Thu | Fri | Sat | Sun | |||
9 | 06/09/2021 | 07/09/2021 | 08/09/2021 | 09/09/2021 | 10/09/2021 | 11/09/2021 | ######## | |||
10 | 00:00 | #N/A | ||||||||
11 | 01:00 | |||||||||
12 | 02:00 | |||||||||
13 | 03:00 | |||||||||
14 | 04:00 | |||||||||
15 | 05:00 | |||||||||
16 | 06:00 | |||||||||
17 | 07:00 | |||||||||
18 | 08:00 | |||||||||
19 | 09:00 | |||||||||
20 | 10:00 | |||||||||
21 | 11:00 | |||||||||
22 | 12:00 | |||||||||
23 | 13:00 | |||||||||
24 | 14:00 | |||||||||
25 | 15:00 | |||||||||
26 | 16:00 | |||||||||
27 | 17:00 | |||||||||
28 | 18:00 | |||||||||
29 | 19:00 | |||||||||
30 | 20:00 | |||||||||
31 | 21:00 | |||||||||
32 | 22:00 | |||||||||
33 | 23:00 | |||||||||
DASH |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C7:I7 | C7 | =CONCAT($B$3,$B$4,C9) |
D9:I9 | D9 | =C9+1 |
C9 | C9 | =B2 |
C10 | C10 | =INDEX(Sheet1!H:H,MATCH(1*(Sheet1!A:A=C7)*(Sheet1!G:G=B10),0)) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Cells with Data Validation | ||
---|---|---|
Cell | Allow | Criteria |
B2 | List | =VALIDATION!$A$1:$A$28 |
B3 | List | =VALIDATION!$C$1:$C$4 |
B4 | List | =VALIDATION!$E$1:$E$11 |
my data range
DATA.xlsx | ||||||||||
---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | |||
1 | CONCAT | Location | Language | Lock | Actual | Date | Interval | Interval % | ||
122 | UKEnglish44445 | UK | English | 68.44886 | 59.68333 | 06/09/2021 | 00:00 | 0.87 | ||
123 | UKEnglish44445 | UK | English | 58.54595 | 63.56667 | 06/09/2021 | 01:00 | 1.09 | ||
124 | UKEnglish44445 | UK | English | 49.34755 | 54.13333 | 06/09/2021 | 02:00 | 1.10 | ||
125 | UKEnglish44445 | UK | English | 45.07392 | 63.61667 | 06/09/2021 | 03:00 | 1.41 | ||
126 | UKEnglish44445 | UK | English | 41.95215 | 61.23333 | 06/09/2021 | 04:00 | 1.46 | ||
127 | UKEnglish44445 | UK | English | 50.7751 | 51.13333 | 06/09/2021 | 05:00 | 1.01 | ||
128 | UKEnglish44445 | UK | English | 78.32746 | 91.21667 | 06/09/2021 | 06:00 | 1.16 | ||
129 | UKEnglish44445 | UK | English | 114.5167 | 121.6333 | 06/09/2021 | 07:00 | 1.06 | ||
130 | UKEnglish44445 | UK | English | 147.159 | 156.2667 | 06/09/2021 | 08:00 | 1.06 | ||
131 | UKEnglish44445 | UK | English | 185.1958 | 184.5167 | 06/09/2021 | 09:00 | 1.00 | ||
132 | UKEnglish44445 | UK | English | 206.3495 | 196.5833 | 06/09/2021 | 10:00 | 0.95 | ||
133 | UKEnglish44445 | UK | English | 205.4149 | 212.4167 | 06/09/2021 | 11:00 | 1.03 | ||
134 | UKEnglish44445 | UK | English | 206.6805 | 205.1333 | 06/09/2021 | 12:00 | 0.99 | ||
135 | UKEnglish44445 | UK | English | 215.5678 | 218.5667 | 06/09/2021 | 13:00 | 1.01 | ||
136 | UKEnglish44445 | UK | English | 218.2568 | 237.6167 | 06/09/2021 | 14:00 | 1.09 | ||
137 | UKEnglish44445 | UK | English | 225.6341 | 245.25 | 06/09/2021 | 15:00 | 1.09 | ||
138 | UKEnglish44445 | UK | English | 227.2672 | 256.8667 | 06/09/2021 | 16:00 | 1.13 | ||
139 | UKEnglish44445 | UK | English | 209.9705 | 228.05 | 06/09/2021 | 17:00 | 1.09 | ||
140 | UKEnglish44445 | UK | English | 197.7419 | 189.7333 | 06/09/2021 | 18:00 | 0.96 | ||
141 | UKEnglish44445 | UK | English | 172.2393 | 179.8667 | 06/09/2021 | 19:00 | 1.04 | ||
142 | UKEnglish44445 | UK | English | 159.291 | 145.4333 | 06/09/2021 | 20:00 | 0.91 | ||
143 | UKEnglish44445 | UK | English | 138.3583 | 136.25 | 06/09/2021 | 21:00 | 0.98 | ||
144 | UKEnglish44445 | UK | English | 128.1515 | 124.35 | 06/09/2021 | 22:00 | 0.97 | ||
145 | UKEnglish44445 | UK | English | 94.20422 | 98.21667 | 06/09/2021 | 23:00 | 1.04 | ||
146 | UKEnglish44446 | UK | English | 61.65222 | 84.73333 | 07/09/2021 | 00:00 | 1.37 | ||
147 | UKEnglish44446 | UK | English | 52.73262 | 51.45 | 07/09/2021 | 01:00 | 0.98 | ||
148 | UKEnglish44446 | UK | English | 44.44757 | 50.35 | 07/09/2021 | 02:00 | 1.13 | ||
149 | UKEnglish44446 | UK | English | 40.5983 | 61.2 | 07/09/2021 | 03:00 | 1.51 | ||
150 | UKEnglish44446 | UK | English | 37.7865 | 48.9 | 07/09/2021 | 04:00 | 1.29 | ||
151 | UKEnglish44446 | UK | English | 45.73338 | 63.68333 | 07/09/2021 | 05:00 | 1.39 | ||
152 | UKEnglish44446 | UK | English | 74.13603 | 90.51667 | 07/09/2021 | 06:00 | 1.22 | ||
153 | UKEnglish44446 | UK | English | 106.3691 | 126.7667 | 07/09/2021 | 07:00 | 1.19 | ||
154 | UKEnglish44446 | UK | English | 134.5131 | 156.05 | 07/09/2021 | 08:00 | 1.16 | ||
155 | UKEnglish44446 | UK | English | 172.6784 | 184.2833 | 07/09/2021 | 09:00 | 1.07 | ||
156 | UKEnglish44446 | UK | English | 182.5414 | 180.4667 | 07/09/2021 | 10:00 | 0.99 | ||
157 | UKEnglish44446 | UK | English | 189.121 | 194.0833 | 07/09/2021 | 11:00 | 1.03 | ||
158 | UKEnglish44446 | UK | English | 189.7231 | 191.05 | 07/09/2021 | 12:00 | 1.01 | ||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
A122:A158 | A122 | =CONCATENATE(B122,C122,F122) |
Any help would be apprecaited.
Thanks,