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 | |||||||||
