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**.

**Download Practice Workbook**

You can download the practice workbook from the link below.

**5 Ways to Return Column Number of Match in Excel **

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: ****[Fixed] Excel Column Numbers Instead of Letters (2 Solutions)**

__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 Convert Column Number to Letter in Excel (3 Ways)**

__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.

**Read More: How to Reference Cell by Row and Column Number in Excel (4 Methods)**

__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**.

**Read More: VBA to Convert Column Number to Letter in Excel (3 Methods)**

__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.

**Read More:** **Find Value in Row and Return Column Number Using VBA in Excel**

**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.

**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. We, the **Exceldemy** team, are happy to answer your queries. Keep learning and keep growing!

## Related Articles

**Excel VBA: Get Row and Column Number from Cell Address (4 Methods)****How to Change Column Name from ABC to 1 2 3 in Excel****How to Convert Column Letter to Number Chart in Excel (4 Ways)****Excel VBA: Set Range by Row and Column Number (3 Examples)****VBA to Use Range Based on Column Number in Excel (4 Methods)**