Examples of uses for the COLUMN function
Why would anyone want to use the COLUMN function? The examples below tell why.
The image below shows the grades of a few students (to the left).
To the right side, we want to fetch out the grades for Henry. Easy solution = VLOOKUP.
1. Write the VLOOKUP function as follows.
= VLOOKUP (E1,
The lookup_value is referred to as Cell E1 because it contains the name of the student to look the result for.
2. Refer to the table array where the lookup and the return values are.
= VLOOKUP (E1, A1:B4,
3. For the col_index num argument, nest in the COLUMN function as follows:
= VLOOKUP (F1, A1:B4, COLUMN(B1))
The col_index num argument refers to the column from where the value is to be returned.
And this has to be the number of the column starting from the first column of the table_array.
We want the Grades of Henry to be returned. Grades are listed in column B, so we have referred to Cell B1 (any cell from Column B).
Instead of manually counting the columns, let the COLUMN function do the job.
The VLOOKUP function needs the column number starting from the table array. If the table array starts from any column other than the first column, the above function might not work.
For example, what if the table array above started from Column B and not A?
1. Write the VLOOKUP function as above, and it’d fail to function.
=VLOOKUP (G2, B1:C4, (COLUMN (C1))
It will take the col_index num argument as 3 (Column number of Column C).
Whereas, our table range has only two columns.
2. Deal with such a situation by changing the COLUMN function as follows.
= COLUMN(C1) – COLUMN(A1)
Subtract the number of the column before the table array (Column A) from the return value column (Column C).
3. Rewrite the VLOOKUP function as below.
= VLOOKUP (G1, B1:C4, COLUMN(C1) – COLUMN(A1))
Here are the results!
The COLUMN function is not only meant to find the number of a single column. You can also use it to find the number of multiple columns at once.
The data below shows the monthly utility bill of a household.
To find the accumulated utility bill for each month, let’s apply the COLUMN function.
1. Write the COLUMN function as follows.
The range A2:F2 tells the number of months for which we need the accumulated bill.
2. Multiply it with the particular cell of the monthly bill.
= H3 * COLUMN(A2:F2)
The cell reference is set to H3 as it contains the monthly bill.
It only takes a single click for Excel to compute the bills for all the months.
When the reference argument of the column function is defined as a range, the output is an array.
Caution! The SPILL Error:
If the cells of the array are not vacant before the array function operates, Excel gives the #SPILL! Error.