I am trying to write an Index Match formula which will return the date of a service into the cells in column C in Worksheet 2. The data with the dates is in the worksheet titled "Paste Here". I have a list of clients in column B of Sheet 2. The same clients have a list of several services in "Paste Here". I know an Index Match formula will return the date of the January service for each client in the cells in column C, but I can't quite get it. I've tried three formulas, but can't get it right. The formula in cells C4 to C9 returns the date of the first service listed in Column G in "Paste Here". The formula in C3 is the closest to what I need, but I need some assistance. Can anyone help with this formula??
Transitons_UA_Tracker_111422.xlsx | |||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | O | P | Q | R | S | ||||||||||||||
1 | MR# | Patient Name | January-22 | 1/1/22 | 1/31/22 | ||||||||||||||||
2 | 1 | A, I | 2/1/22 | 2/28/22 | |||||||||||||||||
3 | 2 | A, L | January 0, 1900 | 3/1/22 | 3/31/22 | ||||||||||||||||
4 | 3 | A, S | 9/6/2022 | 4/1/22 | 4/30/22 | ||||||||||||||||
5 | 4 | A, R | 5/13/2022 | 5/1/22 | 5/31/22 | ||||||||||||||||
6 | 5 | A, St | 8/19/2022 | 6/1/22 | 6/30/22 | ||||||||||||||||
7 | 6 | A, D | 8/26/2022 | 7/1/22 | 7/31/22 | ||||||||||||||||
8 | 7 | A, Str | 9/7/2022 | 8/1/22 | 8/31/22 | ||||||||||||||||
9 | 8 | A, C | 9/20/2022 | 9/1/22 | 9/30/22 | ||||||||||||||||
10 | 9 | A, J | 10/1/22 | 10/31/22 | |||||||||||||||||
11 | 10 | B, S | 11/1/22 | 11/30/22 | |||||||||||||||||
12 | 12/1/22 | 12/31/22 | |||||||||||||||||||
13 | |||||||||||||||||||||
14 | |||||||||||||||||||||
Sheet2 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
C2 | C2 | =IFERROR(INDEX('Paste Here'!$G$2:$G$2169,AGGREGATE(15,6*(ROW('Paste Here'!$B$2:$B$2169)-ROW($B$2)+1)/(('Paste Here'!$B$2:$B$2169=Sheet2!B2)*('Paste Here'!$G$2:$G$2169<=S1)*('Paste Here'!$G$2:$G$2169>=R1)),1)),"") |
C3 | C3 | =INDEX('Paste Here'!$G$2:$G$2169,MATCH(1,INDEX(('Paste Here'!$B$2:$B$2169=Sheet2!B3)*('Paste Here'!$G$2:$G$2169<=Sheet2!S1)*('Paste Here'!$G$2:$G$2169>=Sheet2!R1),0)*0)) |
C4:C9 | C4 | =INDEX('Paste Here'!$G$2:$G$2169,MATCH(Sheet2!B4,'Paste Here'!$B$2:$B$2189,0)) |
Transitons_UA_Tracker_111422.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | |||
1 | # | Name | Date | ||||||
2 | 1 | A, I | 9/6/2022 | ||||||
3 | 1 | A, I | 3/22/2022 | ||||||
4 | 1 | A, I | 4/19/2022 | ||||||
5 | 1 | A, I | 10/13/2022 | ||||||
6 | 1 | A, I | 5/17/2022 | ||||||
7 | 1 | A, I | 5/17/2022 | ||||||
8 | 1 | A, I | 6/1/2022 | ||||||
9 | 1 | A, I | 1/25/2022 | ||||||
10 | 1 | A, I | 8/9/2022 | ||||||
11 | 2 | A, L | 1/6/2022 | ||||||
12 | 2 | A, L | 4/20/2022 | ||||||
13 | 2 | A, L | 9/21/2022 | ||||||
14 | 2 | A, L | 10/12/2022 | ||||||
15 | 2 | A, L | 5/11/2022 | ||||||
16 | 2 | A, L | 6/1/2022 | ||||||
17 | 2 | A, L | 7/21/2022 | ||||||
18 | 2 | A, L | 8/10/2022 | ||||||
19 | 2 | A, L | 2/16/2022 | ||||||
20 | 3 | A, S | 9/6/2022 | ||||||
21 | 3 | A, S | 3/21/2022 | ||||||
22 | 3 | A, S | 1/18/2022 | ||||||
23 | 3 | A, S | 10/17/2022 | ||||||
24 | 3 | A, S | 1/18/2022 | ||||||
25 | 3 | A, S | 2/7/2022 | ||||||
26 | 3 | A, S | 6/13/2022 | ||||||
27 | 3 | A, S | 7/25/2022 | ||||||
28 | 3 | A, S | 8/15/2022 | ||||||
29 | 4 | A, R | 5/13/2022 | ||||||
30 | 4 | A, R | 6/1/2022 | ||||||
31 | 4 | A, R | 7/6/2022 | ||||||
32 | 5 | A, St | 8/19/2022 | ||||||
33 | 5 | A, St | 8/29/2022 | ||||||
34 | 5 | A, St | 9/14/2022 | ||||||
35 | 5 | A, St | 3/21/2022 | ||||||
36 | 5 | A, St | 1/4/2022 | ||||||
37 | 5 | A, St | 4/8/2022 | ||||||
38 | 5 | A, St | 10/18/2022 | ||||||
39 | 5 | A, St | 4/20/2022 | ||||||
40 | 5 | A, St | 5/4/2022 | ||||||
41 | 5 | A, St | 5/23/2022 | ||||||
42 | 5 | A, St | 6/15/2022 | ||||||
43 | 5 | A, St | 7/19/2022 | ||||||
44 | 5 | A, St | 8/19/2022 | ||||||
45 | 5 | A, St | 2/18/2022 | ||||||
46 | 6 | A, D | 8/26/2022 | ||||||
47 | 6 | A, D | 9/13/2022 | ||||||
48 | 6 | A, D | 1/14/2022 | ||||||
49 | 6 | A, D | 4/8/2022 | ||||||
50 | 6 | A, D | 5/6/2022 | ||||||
51 | 6 | A, D | 6/3/2022 | ||||||
52 | 6 | A, D | 7/15/2022 | ||||||
53 | 6 | A, D | 2/11/2022 | ||||||
54 | 6 | A, Str | 9/7/2022 | ||||||
55 | 7 | A, Str | 4/5/2022 | ||||||
56 | 7 | A, Str | 5/5/2022 | ||||||
57 | 7 | A, Str | 6/2/2022 | ||||||
58 | 7 | A, Str | 7/22/2022 | ||||||
59 | 7 | A, Str | 8/10/2022 | ||||||
60 | 7 | A, Str | 6/30/2022 | ||||||
61 | 8 | A, C | 9/20/2022 | ||||||
62 | 8 | A, C | 10/20/2022 | ||||||
63 | 8 | A, C | 3/25/2022 | ||||||
64 | 8 | A, C | 4/5/2022 | ||||||
65 | 8 | A, C | 5/12/2022 | ||||||
66 | 8 | A, C | 2/7/2022 | ||||||
67 | 8 | A, C | 6/21/2022 | ||||||
68 | 8 | A, C | 7/8/2022 | ||||||
69 | 8 | A, C | 8/8/2022 | ||||||
70 | 9 | A, J | 9/7/2022 | ||||||
71 | 9 | A, J | 10/20/2022 | ||||||
72 | 9 | A, J | 8/1/2022 | ||||||
73 | 10 | B, S | 8/18/2022 | ||||||
74 | 10 | B, S | 9/1/2022 | ||||||
75 | 10 | B, S | 9/15/2022 | ||||||
76 | 10 | B, S | 3/16/2022 | ||||||
77 | 10 | B, S | 1/10/2022 | ||||||
78 | 10 | B, S | 10/27/2022 | ||||||
79 | 10 | B, S | 4/28/2022 | ||||||
80 | 10 | B, S | 5/26/2022 | ||||||
81 | 10 | B, S | 2/4/2022 | ||||||
82 | 10 | B, S | 6/16/2022 | ||||||
83 | 10 | B, S | 7/14/2022 | ||||||
Paste Here |