Eshrak Kader
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
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
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
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
- 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
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.
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.
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
- 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
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 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
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)
