While working in Excel you may need to find the column number of the matches. With this in mind, this article explains how to return column number of match in Excel.

**Table of Contents**Expand

**How to Return Column Number of Match in Excel: ****5 Useful Ways**

Let’s consider the following dataset shown in **B4:C14** cells. Here, the first column shows the** Employee Name**, while the second column displays the **Sales **amount in **USD**.

We have used *Microsoft Excel 365* version here, you can use any other versions according to your convenience.

__Method-1__: Using MATCH Function to Return Column Number of Match

__Method-1__: Using MATCH Function to Return Column Number of Match

If you’re one of those people who enjoy using Excel formulas then our first method is your answer. In this method, we’ll utilize the** MATCH **function to get the **Column Number**. So, follow these simple steps.

** Steps**:

- Firstly, go to the
**F5**cell and insert the expression given below.

`=MATCH(E5,$4:$4,0)`

Here, the **E5** cell refers to the **Sales**, and the **$4:$4** indicates **Row Number 4**. Moreover, **0 **(** match_type **argument) represents the

**Exact match**criteria.

**:**

*Note**Please make sure to provide an*

**Absolute Cell Reference**for the first**C5**cell using the**F4**key.- Likewise, use the
**Fill Handle**Tool to copy the formula into the cell below.

Eventually, you should get the results as shown in the picture below.

**Read More: **Column Letter to Number Converter in Excel

__Method-2__: Return Matched Column Number with COLUMN Function

__Method-2__: Return Matched Column Number with COLUMN Function

Our second method is remarkably similar to the first method. However, we’ll use the **COLUMN **function to obtain the column number of the cell. So, let’s see it in action.

** Steps**:

- At the very beginning, move to the
**F5**cell and enter the expression given below.

`=COLUMN(C4)`

Here, the **COLUMN **function takes only the ** reference** argument and returns the column number of a reference cell. In this case, the

**C4**cell represents the

*reference*argument which is the column heading

**Sales**.

- In a similar fashion, type in the
**COLUMN**function and enter the**B4**cell reference for the**Name**as shown below.

The result appears as shown in the image below.

**Read More:** How to Change Column Name from ABC to 1 2 3 in Excel

__Method-3__: Using SUBSTITUTE Function to Obtain Column Letter of a Specific Cell

__Method-3__: Using SUBSTITUTE Function to Obtain Column Letter of a Specific Cell

What if you need the **Column Letter **instead of the **Column Number**? Our next method provides the answer to your question. Here, we’ll combine the **SUBSTITUTE **function and the **ADDRESS** function to return the **Column Letter**. So, let’s begin.

** Steps**:

- To start, navigate to the
**F5**cell and enter the following expression.

`=SUBSTITUTE(ADDRESS(1,COLUMN(C4),4),"1","")`

In the above formula, the **C4** cells point to the **Sales**.

**Formula Breakdown:**

**COLUMN(C4) →**returns the column number of the**C4**cell.**Output → 3**

**ADDRESS(1,COLUMN(C4),4) →**becomes**ADDRESS(1,3,4) →**creates a cell reference as text given the rows and columns. In this formula,**1**is theargument and*row_number***3**is theargument. Lastly,*column_number***4**represents the optionalargument which tells the*abs_num***ADDRESS**function to return a**Relative Reference**.**Output → C1**

**=SUBSTITUTE(ADDRESS(1,COLUMN(C4),4),”1″,””) →**becomes**=SUBSTITUTE(C1,”1″,””) →**Replaces existing text with new text in a text string. Here, the**C1**refers to theargument which is the*text***Sales**header. Next, the**“1”**represents theargument, and the*old_text***“”**points to theargument which is left blank.*new_text***Output → C**

- Similarly, repeat the process for the
**F6**cell as shown in the screenshot below.

Subsequently, the results should look like the sample image given below.

__Method-4__: Applying VBA Code to Return Matched Column Number in Excel

__Method-4__: Applying VBA Code to Return Matched Column Number in Excel

If you often need to get the column number of matches, then you may consider the **VBA **code below. It’s simple & easy, just follow along.

__Step-01:__ Open Visual Basic Editor

__Step-01:__Open Visual Basic Editor

- Firstly, navigate to the
**Developer**tab >> click the**Visual Basic**button.

This opens the **Visual Basic Editor** in a new window.

__Step-02:__ Insert VBA Code

__Step-02:__Insert VBA Code

- Secondly, choose the
**Sheet**where you want to insert a**Module**. In this case, we chose**Sheet4 (VBA Code)**. - Next, go to the
**Insert**tab >> select**Module**.

For your ease of reference, you can copy the code from here and paste it into the window as shown below.

```
Sub FindColumnNumber()
Dim RowNumber As Long
RowNumber = Application.WorksheetFunction.Match("Sales", Sheet1.Rows(3), 0)
MsgBox "The column number is " & RowNumber
End Sub
```

**Code Breakdown:**

Now, I will explain the **VBA** code used to return the column number. In this case, the code is divided into two steps.

- Firstly, in the first portion, the sub-routine is given a name.
- Next, define the variable
**RowNumber**and assign**the Long data**type.

- Secondly, in the later part use the
**MATCH**function to determine the**Column Number**of the**Sales**header. - Then, store this value
**RowNumber**variable. - Finally, use
**the MsgBox function**to display the result.

__Step-03:__ Running VBA Code

__Step-03:__Running VBA Code

- Now, press the
**F5**key on your keyboard.

This opens the **Macros** dialog box.

- Following this, click the
**Run**button.

Lastly, a message box pops up showing the **Column Number**.

__Method-5__: Utilizing Excel Table to Return Column Number of Match

__Method-5__:

Last but not least, we can also use **Excel Table** to obtain the column number of the match, in fact, an added benefit of this process is that it is dynamic which means if we insert a new column the column numbers will update automatically. Now, let’s assume the **List of Clients** dataset shown in the **B4:F14** cells containing the **Company Name**, **Address**, **City**, **State**, and **Zip Codes** respectively. Henceforth, let’s explore this method in detail with the necessary illustrations.

** Steps**:

- First and foremost, proceed to the
**B4**cell >> hit the**CTRL + T**keys to insert an**Excel Table**.

- Not long after, press the
**CTRL + SHIFT + Right Arrow (->)**keys to select all the column headers >> use the**CTRL + C**shortcut to copy them.

- Afterward, click the
**CTRL + ALT+ V**keys to launch the**Paste Special**option >> check the**Values**and**Transpose**radio buttons >> hit**OK**.

Now, you can follow the steps in real-time in the animated GIF shown below.

- Later, insert the formula in the
**C17**cell >> drag the**Fill Handle tool**to copy the formula to the cells below.

`=MATCH(B17,Clients_List[#Headers],0)`

For instance, the **B17** cell refers to the **Company Name** while the **Clients_List** is the name of the **Excel Table**.

- Additionally, if we insert an extra
**Manager**column the**Column Numbers**change accordingly as shown in the figure below.

You can also find a specific value in rows and return the column number by using VBA code.

**Return Value for Matched Column Using INDEX and MATCH Functions **

Our last method uses the **INDEX** function and the** MATCH** function to return the value of the match column. So, let’s see the process in detail.

Considering the following dataset shown in **B4:I8** cells. Here, the table shows the marks of each student in the 4 subjects i.e., **Physics**, **Chemistry**, **Biology**, and **Maths**.

** Steps**:

- Firstly, choose a
**Subject**and enter the**Student Name**. In this case, we chose**Physics**as the*Subject*and**Patrick**as the*Student Name*. - Then, go to the
**C14**cell and type in the expression given below.

`=INDEX(C6:I9,MATCH(C12,B6:B9,0),MATCH(C13,C5:I5,0))`

** **Here, the **C6:I9** range of cells represents the marks scored by the students in the 4 *Subjects*.

**Formula Breakdown:**

**MATCH(C12, B6:B9,0) →**returns the relative position of an item in an array matching the given value. Here,**C12**is theargument which refers to the*lookup_value**Subject Physics*. Following,**B6:B9**represents theargument from where the value is matched. Lately,*lookup_array***0**is the optionalargument which indicates the*match_type***Exact match**criteria.**Output → 1**

**MATCH(C13, C5:I5,0) →**In this formula, the**C13**cell points to the*Student Name**Patrick*. Next,**C5:I5**represents the array from which*Patrick*is matched. Finally,**0**indicates the**Exact match**criteria.**Output → 4**

**=INDEX(C6:I9,MATCH(C12,B6:B9,0),MATCH(C13,C5:I5,0)) →**becomes**=INDEX(C6:I9,1,4) →**Returns a value at the intersection of a row and column in a given range. In this expression, the**C6:I9**is theargument which is the marks scored by the students. Next,*array***1**is theargument which indicates the row location. Lastly,*row_num***4**is the optionalargument which points to the column location.*column_num***Output → 49**

Consequently, the results should look like the image given below.

**Practice Section**

For doing practice by yourself we have provided a** Practice** section like below in each sheet on the right side. Please do it by yourself.

**Download Practice Workbook**

**Conclusion**

This article provides quick and easy answers to how to return column number of match in Excel. Make sure to download the practice files. Hope you found it helpful. Please inform us in the comment section about your experience. Keep learning and keep growing!

**Related Articles**

- How to Find Column Index Number in Excel
- How to Convert Column Number to Letter in Excel
- How to Find Column Number Based on Value in Excel
- VBA to Convert Column Number to Letter in Excel
- How to Use VBA Range Based on Column Number in Excel

**<< Go Back to Column Number | Columns in Excel | Learn Excel**