INDEX MATCH

[Loper]

Perhaps I am missing a step. The database contains data from 1/1/2018 through 12/31/2029. In cell D8, I am trying to match a date from (column A) with a calculated maximum value, (cell C8). The current formula returns the incorrect date of 10/17/2022 and the matched value for that date is not 0.022. Should I be using a different formula?

WORKSHEETS.xlsm
	A	B	C	D	E	F	G	H
1
2
3		Enter a Well #					1
4		Enter a beginning date of a Month, (M/D/YYYY)					1/1/2018
5		Results for January, 2018
6		Well # 1				Distribution System
7		Total	0.077			Total	41.693
8		Maximum	0.022	on 10/17/2022		Maximum	1.685
9		Average	0.002			Average	1.345
10
11
12		WELL # 1, MGD	WELL # 2, MGD	WELL # 6, MGD	WELL # 7, MGD	WELL # 8, MGD	WELL # 9, MGD	Daily Demand
13	Date
14	1/1/2018	0.001	0.649	0.002	0.001	0.000	0.603	1.257
15	1/2/2018	0.001	0.628	0.001	0.002	0.000	0.558	1.190
16	1/3/2018	0.000	0.582	0.006	0.000	0.000	0.562	1.150
17	1/4/2018	0.008	0.621	0.007	0.000	0.000	0.616	1.252
18	1/5/2018	0.000	0.624	0.003	0.000	0.000	0.711	1.339
19	1/6/2018	0.000	0.590	0.000	0.000	0.000	0.777	1.366
20	1/7/2018	0.000	0.601	0.003	0.000	0.000	0.877	1.480
21	1/8/2018	0.000	0.625	0.006	0.000	0.000	0.959	1.590
22	1/9/2018	0.000	0.601	0.002	0.298	0.000	0.783	1.684
23	1/10/2018	0.002	0.605	0.002	0.256	0.000	0.820	1.685
24	1/11/2018	0.003	0.477	0.005	0.226	0.000	0.866	1.577
25	1/12/2018	0.002	0.542	0.001	0.788	0.000	0.225	1.558
26	1/13/2018	0.004	0.551	0.004	0.715	0.000	0.001	1.275
27	1/14/2018	0.002	0.637	0.003	0.570	0.000	0.430	1.642
28	1/15/2018	0.003	0.600	0.002	0.553	0.000	0.329	1.487
29	1/16/2018	0.004	0.636	0.177	0.000	0.000	0.664	1.481
30	1/17/2018	0.022	0.566	0.013	0.765	0.021	0.008	1.395
31	1/18/2018	0.001	0.606	0.001	0.737	0.000	0.003	1.348
32	1/19/2018	0.003	0.575	0.002	0.026	0.000	0.679	1.285
33	1/20/2018	0.000	0.592	0.000	0.001	0.000	0.683	1.276
34	1/21/2018	0.000	0.596	0.000	0.000	0.000	0.689	1.286
35	1/22/2018	0.001	0.617	0.001	0.003	0.000	0.676	1.299
36	1/23/2018	0.001	0.606	0.003	0.002	0.000	0.590	1.202
37	1/24/2018	0.003	0.588	0.002	0.002	0.000	0.527	1.123
38	1/25/2018	0.001	0.639	0.002	0.002	0.000	0.575	1.219
39	1/26/2018	0.002	0.614	0.222	0.362	0.000	0.003	1.203
40	1/27/2018	0.002	0.506	0.004	0.006	0.000	0.728	1.246
41	1/28/2018	0.003	0.598	0.002	0.002	0.000	0.666	1.270
42	1/29/2018	0.002	0.624	0.002	0.002	0.000	0.608	1.238
43	1/30/2018	0.004	0.004	0.005	0.347	0.000	0.874	1.233
44	1/31/2018	0.002	0.597	0.002	0.006	0.000	0.451	1.058
45	2/1/2018	0.002	0.607	0.003	0.002	0.000	0.580	1.194
46	2/2/2018	0.002	0.604	0.003	0.002	0.000	0.546	1.157
47	2/3/2018	0.002	0.639	0.002	0.003	0.000	0.564	1.210
48	2/4/2018	0.002	0.576	0.002	0.006	0.000	0.621	1.206
49	2/5/2018	0.002	0.594	0.002	0.002	0.000	0.632	1.231
50	2/6/2018	0.002	0.607	0.004	0.003	0.000	0.565	1.182
51	2/7/2018	0.003	0.616	0.003	0.004	0.000	0.411	1.037
52	2/8/2018	0.005	0.601	0.002	0.004	0.000	0.518	1.130
53	2/9/2018	0.003	0.611	0.001	0.001	0.000	0.514	1.130
54	2/10/2018	0.003	0.622	0.003	0.001	0.000	0.565	1.195
55	2/11/2018	0.001	0.611	0.004	0.002	0.000	0.613	1.232
56	2/12/2018	0.007	0.024	0.004	0.424	0.000	0.815	1.274
57	2/13/2018	0.003	0.601	0.003	0.003	0.000	0.572	1.182
58	2/14/2018	0.003	0.593	0.004	0.005	0.000	0.530	1.135
59	2/15/2018	0.000	0.649	0.004	0.003	0.000	0.529	1.185
60	2/16/2018	0.000	0.601	0.004	0.005	0.000	0.553	1.162
61	2/17/2018	0.002	0.574	0.003	0.002	0.000	0.552	1.133
62	2/18/2018	0.000	0.619	0.001	0.002	0.000	0.609	1.231
63	2/19/2018	0.000	0.617	0.002	0.002	0.000	0.641	1.262
64	2/20/2018	0.000	0.665	0.001	0.002	0.000	0.597	1.265
65	2/21/2018	0.000	0.620	0.003	0.005	0.000	0.508	1.135
66	2/22/2018	0.000	0.633	0.004	0.361	0.000	0.003	1.000
67	2/23/2018	0.000	0.003	0.305	0.711	0.000	0.003	1.023
68	2/24/2018	0.000	0.634	0.017	0.498	0.000	0.007	1.156
69	2/25/2018	0.000	0.596	0.006	0.465	0.000	0.005	1.072
70	2/26/2018	0.000	0.655	0.005	0.009	0.000	0.702	1.372
71	2/27/2018	0.000	0.583	0.008	0.040	0.009	0.256	0.895
72	2/28/2018	0.009	0.606	0.010	0.004	0.000	0.599	1.228
MGD DATA

Cell Formulas
Range	Formula
B5	B5	=G4
B6	B6	=G3
D8	D8	=INDEX(A14:A1048576,MATCH(C8,B14:B1048576))
C7	C7	=IF(G3=1,SUMIFS(B12:B1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),IF(G3=2,SUMIFS(C12:C1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),IF(G3=6,SUMIFS(D12:D1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),IF(G3=7,SUMIFS(E12:E1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),IF(G3=8,SUMIFS(F12:F1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),IF(G3=9,SUMIFS(G12:G1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),""))))))
C8	C8	=IF(G3=1,MAXIFS(B12:B1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),IF(G3=2,MAXIFS(C12:C1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),IF(G3=6,MAXIFS(D12:D1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),IF(G3=7,MAXIFS(E12:E1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),IF(G3=8,MAXIFS(F12:F1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),IF(G3=9,MAXIFS(G12:G1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),""))))))
C9	C9	=IF(G3=1,AVERAGEIFS(B12:B1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),IF(G3=2,AVERAGEIFS(C12:C1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),IF(G3=6,AVERAGEIFS(D12:D1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),IF(G3=7,AVERAGEIFS(E12:E1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),IF(G3=8,AVERAGEIFS(F12:F1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),IF(G3=9,AVERAGEIFS(G12:G1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0)),""))))))
G7	G7	=SUMIFS(H12:H1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0))
G8	G8	=MAXIFS(H12:H1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0))
G9	G9	=AVERAGEIFS(H12:H1048576,A12:A1048576,">="&G4,A12:A1048576,"<="&EOMONTH(G4,0))
B14:B72	B14	='G:\MUA Server Documents\WATER OPERATIONS\ARCHIVE\2018\[2018.xlsm]WELL # 1'!$F4
C14:C72	C14	='G:\MUA Server Documents\WATER OPERATIONS\ARCHIVE\2018\[2018.xlsm]WELL # 2'!$F4
D14:D72	D14	='G:\MUA Server Documents\WATER OPERATIONS\ARCHIVE\2018\[2018.xlsm]WELL # 6'!$F4
E14:E72	E14	='G:\MUA Server Documents\WATER OPERATIONS\ARCHIVE\2018\[2018.xlsm]WELL # 7'!$F4
F14:F72	F14	='G:\MUA Server Documents\WATER OPERATIONS\ARCHIVE\2018\[2018.xlsm]WELL # 8'!$F4
G14:G72	G14	='G:\MUA Server Documents\WATER OPERATIONS\ARCHIVE\2018\[2018.xlsm]WELL # 9'!$F4
H14:H72	H14	=SUM(B14:G14)

 

[Fluff]

Hi & welcome to MrExcel.
Try using XMATCH instead of MATCH