I'm trying to copy a named range to a new column when the named range matches cell data entry. So columns B-F are names ranges (C-01 thru C-06 respectively), what I'm trying to do is that when H1 matches any of the named ranges in B1-F1, that named range data is copied below. I've tried Index/Match, Countif, and many other formula variations and nothing seems to work. I can't find anything that will work on this forum or google. Thanks for your assistance.
Calendar working.xlsx | |||||||||
---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | |||
1 | C-01 | C-02 | C-04 | C-05 | C-06 | C-05 | |||
2 | 1/7/21 | 1/4/21 | 1/4/21 | 1/1/21 | 1/8/21 | 1/1/21 | |||
3 | 1/15/21 | 1/12/21 | 1/12/21 | 1/2/21 | 1/9/21 | 1/2/21 | |||
4 | 1/16/21 | 1/20/21 | 1/20/21 | 1/11/21 | 1/18/21 | 1/11/21 | |||
5 | 1/25/21 | 1/28/21 | 1/28/21 | 1/19/21 | 1/26/21 | 1/19/21 | |||
6 | 2/2/21 | 2/5/21 | 2/5/21 | 1/27/21 | 2/3/21 | 1/27/21 | |||
7 | 2/10/21 | 2/6/21 | 2/6/21 | 2/4/21 | 2/11/21 | 2/4/21 | |||
8 | 2/18/21 | 2/15/21 | 2/15/21 | 2/12/21 | 2/19/21 | 2/12/21 | |||
9 | 2/26/21 | 2/23/21 | 2/23/21 | 2/13/21 | 2/20/21 | 2/13/21 | |||
10 | 2/27/21 | 3/3/21 | 3/3/21 | 2/22/21 | 3/1/21 | 2/22/21 | |||
11 | 3/8/21 | 3/11/21 | 3/11/21 | 3/2/21 | 3/9/21 | 3/2/21 | |||
12 | 3/16/21 | 3/19/21 | 3/19/21 | 3/10/21 | 3/17/21 | 3/10/21 | |||
13 | 3/24/21 | 3/20/21 | 3/20/21 | 3/18/21 | 3/25/21 | 3/18/21 | |||
14 | 4/1/21 | 3/29/21 | 3/29/21 | 3/26/21 | 4/2/21 | 3/26/21 | |||
15 | 4/9/21 | 4/6/21 | 4/6/21 | 3/27/21 | 4/3/21 | 3/27/21 | |||
16 | 4/10/21 | 4/14/21 | 4/14/21 | 4/5/21 | 4/12/21 | 4/5/21 | |||
17 | 4/19/21 | 4/22/21 | 4/22/21 | 4/13/21 | 4/20/21 | 4/13/21 | |||
18 | 4/27/21 | 4/30/21 | 4/30/21 | 4/21/21 | 4/28/21 | 4/21/21 | |||
19 | 5/5/21 | 5/1/21 | 5/1/21 | 4/29/21 | 5/6/21 | 4/29/21 | |||
20 | 5/13/21 | 5/10/21 | 5/10/21 | 5/7/21 | 5/14/21 | 5/7/21 | |||
21 | 5/21/21 | 5/18/21 | 5/18/21 | 5/8/21 | 5/15/21 | 5/8/21 | |||
22 | 5/22/21 | 5/26/21 | 5/26/21 | 5/17/21 | 5/24/21 | 5/17/21 | |||
23 | 5/31/21 | 6/3/21 | 6/3/21 | 5/25/21 | 6/1/21 | 5/25/21 | |||
24 | 6/8/21 | 6/11/21 | 6/11/21 | 6/2/21 | 6/9/21 | 6/2/21 | |||
25 | 6/16/21 | 6/12/21 | 6/12/21 | 6/10/21 | 6/17/21 | 6/10/21 | |||
26 | 6/24/21 | 6/21/21 | 6/21/21 | 6/18/21 | 6/25/21 | 6/18/21 | |||
27 | 7/2/21 | 6/29/21 | 6/29/21 | 6/19/21 | 6/26/21 | 6/19/21 | |||
28 | 7/3/21 | 7/7/21 | 7/7/21 | 6/28/21 | 7/5/21 | 6/28/21 | |||
Sheet3 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H1 | H1 | =Setup!G4 |