Find a specific column header, and list ALL values under that header

[k_mak]

Hello All,
How does one find a specific column header and then list ALL values under that header?

 

[Fluff]

Maybe

Book1
	A	B	C	D	E	F	G
1	Postcode	GridRef	County	District	Ward		County
2	CB6 2DR	TL484847	Cambridgeshire	East Cambridgeshire	Downham Villages		Cambridgeshire
3	PE34 3PA	TF555049	Norfolk	King's Lynn and West Norfolk	Upwell and Delph		Norfolk
4	HU7 3DF	TA093348	East Riding of Yorkshire	Kingston upon Hull, City of	Kings Park		East Riding of Yorkshire
5	HU8 9YA	TA118333	East Riding of Yorkshire	Kingston upon Hull, City of	Sutton		East Riding of Yorkshire
6	PE13 4QD	TF409125	Cambridgeshire	Fenland	Roman Bank		Cambridgeshire
7	TN26 2QL	TR010310	Kent	Shepway	Walland & Denge Marsh		Kent
8	BH15 1YE	SZ018905	Dorset	Poole	Poole Town		Dorset
9	HU6 9UB	TA076336	East Riding of Yorkshire	Kingston upon Hull, City of	Beverley		East Riding of Yorkshire
10	IG11 0XX	TQ480834	Greater London	Barking and Dagenham	Thames		Greater London
11	PE12 0JW	TF366170	Lincolnshire	South Holland	The Saints		Lincolnshire
12	PE12 9AP	TF425196	Lincolnshire	South Holland	Long Sutton		Lincolnshire
13	PE14 8PS	TF527044	Norfolk	King's Lynn and West Norfolk	Emneth with Outwell		Norfolk
14	PE26 2RD	TL285868	Cambridgeshire	Huntingdonshire	Ramsey		Cambridgeshire
15	RM18 8EP	TQ650768	Essex	Thurrock	Tilbury St Chads		Essex
Sheet4

Cell Formulas
Range	Formula
G2	=INDEX($A$2:$E$36,ROW(A1),MATCH($G$1,$A$1:$E$1,0))

 

[k_mak]

Hi Fluff,
Whoa, thank you for the quick response. Would it be possible in the area of MATCH, that "$G$1" be a list of words that is equivalent to the word "County"? Example..."County" or "Village" or "Town" or "City" etc...?

 

[Fluff]

Do you mean you want to put the names of multiple columns into one cell & retrieve the data from each column into 1 new column?

 

[k_mak]

Hi Fluff,
Sorry for the confusion...let me clarify...

Using your example: "County"...on a template, i have a column header called: County. The source data, depending on where or what i pulled from, instead of using the word County, they use the word: "Town" for example.
Other sources may use: "Village", or "City" etc... So what i would like to know is...can a formula be created to lookup a list of words that are "alias" to the word "County", and then list all corresponding "names" of the County as per your table.

 

[Fluff]

It's possible, but you would need a separate lookup table somewhere with all the aliases & the header they refer to.

 

[k_mak]

Noted, that is correct, I would indeed require a separate table to list the "aliases". As mentioned earlier, I attempted to input a list in place of $G$1, but the result returned #N/A. Any ideas would be greatly appreciated!

 

[Fluff]

No you need a separate table along the lines of

Book1
	A	B
1	Alias	Header
2	Town	County
3	City	County
4	Village	County
5	County	County
6	Postcode	Postcode
7	Zip	Postcode
8	Area code	Postcode
Lists

Then you would need to lookup the alias & return the header

 

[k_mak]

I understand that I require a separate table. As far as this separate table, I was hoping more like...

A B
1 Cat1 Cat2
2 City Area Code
3 County Postal Code
4 Town Zip Code
5 Village

Would you be able to assist?

 

[Fluff]

Can you please post a sample showing the layout of your data & expected outcome?
There are some add-ins available here to help you do that. https://www.mrexcel.com/forum/about-board/508133-attachments.html