Formula to Copy Named Range to column if text matches

[dlmoore99]

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

 

[jasonb75]

I don't see any named ranges in your mini-sheet, just column headers. If they are named then you would need to use INDIRECT, which is less desirable than the formula to do what you want without using named ranges.

Enter this into H2, then fill down as needed.

=INDEX($A$2:$E$28,,MATCH($G$1,$A$1:$E$1,0))

 

[dlmoore99]

Sorry for the late reply as things took me away from this project, this works great, i had tried an index/match but was trying to use it with the names ranges that i didn't add to the sample, Thank you