Average Ifs based on column header and column value

[chimneytop]

I have the table located below. The column headers are standardized but do not always appear in the same location. Sometimes Gender is first, sometimes Salary is second etc. I want to be able to Average the salary column for all class 1 people that have a unique Employee ID. How do I reference standardized column header names that appear in dynamic locations? (essentially I want to do where row 1 says salary, average that column if unique employee id)

Employee ID	Class	Salary	Gender
115489422478	1	50000	M
216577849874	1	75000	F
548786330225	3	25000	M
364789512687	5	50000	F

<tbody>
</tbody>

 

[Comfy]

If you are using Excel 2007 use an Excel table and Structured References.

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	Employee ID	Class	Salary	Gender			Gender	Employee ID	Class	Salary
2	1.15489E+11	1	50000	M			M	1.15489E+11	1	50000
3	2.16578E+11	1	75000	F			F	2.16578E+11	1	75000
4	5.48786E+11	3	25000	M			M	5.48786E+11	3	25000
5	3.6479E+11	5	50000	F			F	3.6479E+11	5	50000
6
7
8				62500						62500
Sheet1

Cell Formulas
Range	Formula
D8	=AVERAGEIF(Table1[Class],1,Table1[Salary])
J8	=AVERAGEIF(Table2[Class],1,Table2[Salary])

 

[chimneytop]

That option doesn't work. I have a system using pivot tables that does this for me, but my co-workers are afraid of excel. I've made it where they paste an excel file and then select the standard headers. I want that informaiton to then pull to another page without them having to do anything. I'm guessing I can do this somehow with an array and hlookup, I just don't know how.

 

[Comfy]

Here's another way:

Excel 2010
	A	B	C	D	E	F	G	H	I
1	Employee ID	Class	Salary	Gender		Gender	Employee ID	Class	Salary
2	1.15489E+11	1	50000	M		M	1.2E+11	1	50000
3	2.16578E+11	1	75000	F		F	2.2E+11	1	75000
4	5.48786E+11	3	25000	M		M	5.5E+11	3	25000
5	3.6479E+11	5	50000	F		F	3.6E+11	5	50000
6
7
8
9				62500					62500
Sheet2

Cell Formulas
Range	Formula
D9	=AVERAGEIF(INDEX($A$1:$D$5,,MATCH("Class",$A$1:$D$1,0)),1,INDEX($A$1:$D$5,,MATCH("Salary",$A$1:$D$1,0)))
I9	=AVERAGEIF(INDEX($F$1:$I$5,,MATCH("Class",$F$1:$I$1,0)),1,INDEX($F$1:$I$5,,MATCH("Salary",$F$1:$I$1,0)))

 

[chimneytop]

Awesome. This should get me started. I appreciate it.