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

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

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