Returning whole column depending on column header

[joand]

Guys, I need your help in returning the whole column containing the header "Company" (row1). I have several columns in a sheet (A-BS) and that the header "Company" is not fixed to a particular column number. Sometimes it is in Column Z, sometimes in Column U, sometimes in Column BS. so it could vary. How do i write an Excel formula for this?

 

[dreid1011]

Guys, I need your help in returning the whole column containing the header "Company" (row1). I have several columns in a sheet (A-BS) and that the header "Company" is not fixed to a particular column number. Sometimes it is in Column Z, sometimes in Column U, sometimes in Column BS. so it could vary. How do i write an Excel formula for this?

Perhaps this to start off (If you are using Office 365 or Excel 2021):

Book1
	A	B	C	D	E	F	G
1	One	Two	Three	Four	Five		Five
2	1	2	3	4	5		5
3	1	2	3	4	5		5
4	1	2	3	4	5		5
5	1	2	3	4	5		5
6	1	2	3	4	5		5
7	1	2	3	4	5		5
8	1	2	3	4	5		5
9	1	2	3	4	5		5
10	1	2	3	4	5		5
Sheet1

Cell Formulas
Range	Formula
G2:G10	G2	=FILTER(A2:E10,A1:E1=G1)
Dynamic array formulas.

 

[joand]

it works on the same sheet but when i place the formula on sheet2 for example, i get a spill error

 

[joand]

sorry. didnt realize that there was another formula beneath it that is causing the spill error. all good now

 

[dreid1011]

sorry. didnt realize that there was another formula beneath it that is causing the spill error. all good now

Glad to know it works.

 

[Joe4]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)