Get column letter from column number derived from a match formula

[Rowland Hamilton]

Folks: How can I get the column letter corresponding to the column number derived from a match formula?

I used the formula:

=MATCH(F7,'Headcount'!A1:Z1,0)

Note: F7 is the column header name. The cost I'm pulling is under that header in the lookup array.

I want to use the column letter to create a sumif formula with the Indirect formula.

Thank you, Rowland

Get column letter from column number derived from a match formula

 

[Scott Huish]

Why not just use INDEX instead then you don't need to get the column letter?

 

[Rowland Hamilton]

=ADDRESS(1,MATCH(F2,'Headcount'!A1:Z1,0))

I get my cell address as

$P$1

but I want no "$" (use abs somehow?) and just the column letter (can put Column() around it if I loose $'s).

Once I get this done I'll probably figure out a simpler way to perform the sumif.

Thank you, Rowland

 

[Rowland Hamilton]

Hotpepper:

Thanks for the respoonse. I need sumif not vlookup or index(match,match) result.

Note: I used used the single match result in a vlookup with indirect formula but then I realized my results are wrong because I need to sumif. I didn't go straight to index(array,match for row, matchf or column) because I'm expermenting to use less memory (made a file too big a few weeks ago).

Thank you, Rowland

 

[Scott Huish]

Why would you need to match for row and column? INDIRECT is also volatile while INDEX is not.

=INDEX(A:Z,,MATCH(F2,'Headcount'!A1:Z1,0))

This returns a range that you could use in your SUMIF formula.

For example:

=SUMIF(INDEX(Headcount!A:Z,,MATCH(F2,Headcount!A1:Z1,0)),"Test",Headcount!B:B)

 

[Rowland Hamilton]

But, Index(array, match, match) is sumif? testing...

 

[Rowland Hamilton]

Okay, its not sumif you're saying. I was trying to make a drag formula that refers to different worksheets using the indirect formula (that's how we get into trouble with file size, probably). For certain rows the array will be on 'headcount'! but for others it could be 'hardware'!, hence the indirect.

 

[Rowland Hamilton]

I'll worry about indirect later, but here is the Sumif I need for Salary cost:

=SUMIF('Headcount Master'!E:E,'Native Costs'!A2,'Headcount Master'!P:P)

Headcount sheet Column E has cost center, destination sheet cell A2 has cost center, headcount sheet Column P has Salary, destination sheet cell F2 has column header name. Need to be able to change P:P depending on match info derived from cell F2.

 

[Rowland Hamilton]

Thank you for your help, modified your formula this way:.

=SUMIF('Headcount Master'!E:E,$A2,INDEX('Headcount Master'!$A:$Z,,MATCH($F2,'Headcount Master'!$A$1:$Z$1,0)))

Will fo other adjustments for N/A's:

If(isna(formula),0,formula)