To convert a column letter to an regular number (e.g. 1, 10, 26, etc.) you can use a formula based on the INDIRECT and COLUMN functions. See example below: Formula =COLUMN(INDIRECT(letter&”1″)) Explanation In the example shown, the formula in C5 is: =COLUMN(INDIRECT(B5&”1″)) How this formula works The first step is to construct a standard “A1” style reference using the column…
Tag: COLUMN function
How to get last column number in range in Excel
You can get the last column in a range with a formula based on the COLUMN function. See example below: Formula =MIN(COLUMN(range))+COLUMNS(range)-1 Note: When given a single cell reference, the COLUMN function returns the column number for that reference. However, when given a range that contains multiple columns, the COLUMN function will return an array that contains all column numbers…
How to get relative column numbers in a range in Excel
This tutorial shows how to get a full set of relative column numbers in a range using an array formula based on the COLUMN function. Formula {=COLUMN(range)-COLUMN(range.firstcell)+1} On the worksheet, this must be entered as multi-cell array formula using Control + Shift + Enter This is a robust formula that will continue to generate relative numbers even when columns are inserted…
How to get address of last cell in range in Excel
To get the address of the first cell in a named range, you can use the ADDRESS function together with ROW and COLUMN functions. Formula =ADDRESS(ROW(range)+ROWS(range)-1,COLUMN(range)+COLUMNS(range)-1) Explanation In the example shown, the formula in G6 is: =ADDRESS(ROW(data)+ROWS(data)-1,COLUMN(data)+COLUMNS(data)-1) How this formula works The ADDRESS function builds an address based on a row and column number. Working from the inside out, we…
How to get address of named range in Excel
To get the full address of a named range with an Excel formula, you can use the ADDRESS function together with the ROW and COLUMN functions. Formula =ADDRESS(ROW(nr),COLUMN(nr))&”: “&ADDRESS(ROW(nr)+ROWS(nr)-1, COLUMN(nr)+COLUMNS(nr)-1) Explanation In the example shown, the formula in G5 is: =ADDRESS(ROW(data),COLUMN(data),4) &”:”&ADDRESS(ROW(data)+ROWS (data)-1,COLUMN(data)+COLUMNS(data)-1,4) where “data” is the named range B5:D10 How this formula works The core of this formula is the ADDRESS…
How to get first column number in range in Excel
You can get the first column (i.e. the starting column number) in a range with a formula based on the COLUMN function. Formula =MIN(COLUMN(range)) Explanation In the example shown, the formula in cell F5 is: =MIN(COLUMN(data)) where data is a named range for B5:D10 How this formula works When given a single cell reference, the COLUMN function returns the column number for…
How to get address of first cell in range in Excel
To get the address of the first cell in a named range, you can use the ADDRESS function together with the ROW and COLUMN functions. See example below; Note: The ADDRESS function builds an address based on a row and column number. Formula =ADDRESS(ROW(range),COLUMN(range)) Explanation In the example shown, the formula in G5 is: =ADDRESS(ROW(data),COLUMN(data)) How this formula works Working…
Merge tables with VLOOKUP in Excel
This tutorial shows how to Merge tables with VLOOKUP in Excel using the example below; Formula =VLOOKUP($A1,table,COLUMN()-x,0) Explanation To merge tables, you can use the VLOOKUP function to lookup and retrieve data from one table to the other. To use VLOOKUP this way, both tables must share a common id or key. This article explains how join tables using VLOOKUP and a calculated column…
Index and match on multiple columns in Excel
This tutorial shows how to calculate Index and match on multiple columns in Excel using the example below; Formula {=INDEX(range1,MATCH(1,MMULT(–(range2=critera), TRANSPOSE(COLUMN(range2)^0)),0))} Explanation To lookup a value by matching across multiple columns, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and INDEX. In the example shown, the formula in H4 is: {=INDEX(groups,MATCH(1,MMULT(–(names=G4), TRANSPOSE(COLUMN(names)^0)),0))} where “names” is the named range C4:E7, and…
Get location of value in 2D array in Excel
This tutorial shows how to Get location of value in 2D array in Excel using the example below; Formula =SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1 Explanation To locate the position of a value in a 2D array, you can use the SUMPRODUCT function. In the example shown, the formulas used to locate the row and column numbers of the max value in the array are: =SUMPRODUCT((data=MAX(data))*ROW(data))-ROW(data)+1…
Max of every nth column in Excel
This tutorial shows how to calculate Max of every nth column in Excel using the example below; Formula {=MAX(IF(MOD(COLUMN(range)-COLUMN(range.first)+1,n)=0,range))} Explanation To get the max of every nth column, you can use an array formula based on the MAX, MOD, and COLUMN functions. In the example shown, the formula in M5 is: =MAX(IF(MOD(COLUMN(B5:K5)-COLUMN(B5)+1,L5)=0,B5:K5)) Note: this is an array formula and must be entered with control…
Count rows that contain specific values in Excel
This tutorial shows how to Count rows that contain specific values in Excel using the example below; Formula =SUM(–(MMULT(–(criteria),TRANSPOSE(COLUMN(data)))>0)) Explanation To count rows that contain specific values, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and SUM functions. In the example shown, the formula in G5 is: {=SUM(–(MMULT(–(data=90),TRANSPOSE(COLUMN(data)))>0))} where data is the named range B4:B12. Note: this is an array formula and must be…
Count rows with at least n matching values
This tutorial shows how to Count rows with at least n matching values using the example below; Formula {=SUM(–(MMULT(–(criteria),TRANSPOSE(COLUMN(data)^0))>=N))} Explanation To count rows that contain specific values, you can use an array formula based on the MMULT, TRANSPOSE, COLUMN, and SUM functions. In the example shown, the formula in K6 is: {=SUM(–(MMULT(–((data)<70),TRANSPOSE(COLUMN(data)^0))>=2))} where data is the named range C5:I14. Note this is an array formula and must be…
Sum every nth column in Excel
This tutorial shows how to Sum every nth column in Excel using the example below; Formula =SUMPRODUCT(–(MOD(COLUMN(range)-COLUMN(range.first)+1,n)=0),range) Explanation To sum every nth column, you can use a formula based on the SUMPRODUCT, MOD, and COLUMN functions. In the example shown, the formula in L5 is: =SUMPRODUCT(–(MOD(COLUMN(B5:J5)-COLUMN(B5)+1,K5)=0),B5:J5) How this formula works At the core, uses SUMPRODUCT to sum values in a…
How to use Excel COLUMN Function
This Excel tutorial explains how to use the COLUMN function with syntax and examples. Excel COLUMN function Description The Microsoft Excel COLUMN function returns the column number of a cell reference. The COLUMN function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function in Excel. As a worksheet function, the COLUMN…