How to Return Column Number of Match in Excel (5 Useful Ways)

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.

Dataset-1

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

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.

Excel Return Column Number of Match Using MATCH Function

Excel Return Column Number of Match Using MATCH Function

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

Using MATCH Function

Read More: [Fixed] Excel Column Numbers Instead of Letters (2 Solutions)


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.

Excel Return Column Number of Match Using COLUMN Function

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

Excel Return Column Number of Match Using COLUMN Function

The result appears as shown in the image below.

Using COLUMN Function

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

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 the row_number argument and 3 is the column_number argument. Lastly, 4 represents the optional abs_num argument which tells the 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 the text argument which is the Sales header. Next, the “1” represents the old_text argument, and the “” points to the new_text argument which is left blank.
    • Output → C

Excel Return Column Number of Match Using SUBSTITUTE Function

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

Excel Return Column Number of Match Using SUBSTITUTE Function

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

Using SUBSTITUTE Function

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

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

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

Excel Return Column Number of Match Using VBA Code

This opens the Visual Basic Editor in a new window.


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.

Excel Return Column Number of Match Using VBA Code

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

Excel Return Column Number of Match Using VBA Code

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.

Code Explanation


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.

Excel Return Column Number of Match Using VBA Code

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

Excel Return Column Number of Match Using VBA Code

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.

Dataset 2

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 the lookup_value argument which refers to the Subject Physics. Following, B6:B9 represents the lookup_array argument from where the value is matched. Lately, 0 is the optional match_type argument which indicates the 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 the array argument which is the marks scored by the students. Next, 1 is the row_num argument which indicates the row location. Lastly, 4 is the optional column_num argument which points to the column location.
    • Output → 49

Excel Return Column Number of Match Using INDEX and MATCH Functions

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

Excel Return Column Number of Match Using INDEX and MATCH Functions


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.

Practice Section


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

Eshrak Kader

Eshrak Kader

Hello! Welcome to my Profile. I completed my BSc. at Bangladesh University of Engineering & Technology from the Department of Naval Architecture & Marine Engineering. Currently, I am conducting research & posting articles related to Microsoft Excel. I am passionate about research & development and finding innovative solutions to problems.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo