You can use the ROW and COLUMN functions to do this. If you omit the argument for those formulas, the current cell is used. These can be directly used with the OFFSET function, or any other function where you can specify both the row and column as numerical values.
For example, if you enter =ROW[] in cell D8, the value returned is 8. If you enter =COLUMN[] in the same cell, the value
returned is 4.
If you want the column letter, you can use the CHAR function. I do not recommend the use of letters to represent the column, as things get tricky when passing into double-letter column names [where just using numbers is more logical anyways].
Regardless, if you should still want to get the column letter, you can simply add 64 to the column number [64 being one character less then A], so in the previous example, if you set the cell's value to =CHAR[COLUMN[]+64], the
value returned would be D. If you wanted a cell's value to be the cell location itself, the complete formula would be =CHAR[COLUMN[]+64] & ROW[].
Just an FYI, I got 64 from an ASCII table. You could also use the CODE formula, so the updated formula using this would be =CHAR[COLUMN[] + CODE["A"] - 1]. You have to subtract 1 since the minimum value of COLUMN is always 1, and then the minimum return value of the entire formula would be B.
However, this will not work with two-letter columns. In that case, you need the following formula to properly parse two-letter columns:
=IF[COLUMN[]>26,IF[RIGHT[CHAR[IF[MOD[COLUMN[]-1,26]=0,1,MOD[COLUMN[]-1,26]]+64],1]="Y",CHAR[INT[[COLUMN[]-1]/26]+64] & "Z",CHAR[INT[[COLUMN[]-1]/26]+64] & CHAR[IF[MOD[COLUMN[],26]=0,1,MOD[COLUMN[],26]]+64]],CHAR[COLUMN[]+64]]&ROW[]
I'm not sure if there is an easier way to do it or not, but I know that works from cell A1 to ZZ99 with no problems. However, this illustrates why it's best to avoid the use of letter-based column identifiers, and stick with pure number-based formulas [e.g. using the column number instead of letter with OFFSET].
- Login
- Cart
- Training
- Videos
- Functions
- Formulas
- Shortcuts
- Blog
Generic formula
=INDEX[Table[#Headers],index]
Summary
To get the name of a column in an Excel Table from its numeric index, you can use the INDEX function with a structured reference. In the example shown, the formula in I4 is:
=INDEX[Table1[#Headers],H5]
When the formula is copied down, it returns an name for each column, based on index values in column H.
Explanation
This is a standard INDEX formula. The only trick to the formula is the use of a structured reference to return a range for the table headers:
This range goes into INDEX for the array argument, with the index value supplied from column H:
=INDEX[Table1[#Headers],H5]
The result is the name of the first item in the header, which is "ID".
Although the headers are in a horizontal array, with values in columns, INDEX will use the row number as a generic INDEX for one-dimensional arrays like this and correctly return the value at that position.
Related formulas
This is a standard MATCH formula where the lookup values come from column H, the array is the headers in Table1, and match type is zero, to force an exact match. The only trick to the formula is the use of a structured reference to return a range...
Related functions
The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column...
Related videos
In this video, we look at how to use VLOOKUP to lookup values in an Excel Table.
Excel Formula Training
Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.
Download 100+ Important Excel Functions
Get over 100 Excel Functions you should know in one handy PDF.
Excel video training
Quick, clean, and to the point.
Learn more