How to use MATCH function in VBA in Microsoft Excel
By
SpreadCheaters
In this tutorial, we will learn how to use the MATCH function in VBA in Microsoft Excel. Excel provides the built-in MATCH function however it may not always be sufficient for complex data sets or specific needs. This is where VBA can be particularly useful in Excel. With VBA we can automate the process to save time and reduce errors.
Currently, we have a data set above representing a list of names. We want to know the row number of the name “David” in cell B2 using the MATCH function in VBA.
The MATCH function is a built-in function in Microsoft Excel. The MATCH function is commonly used to search a lookup value in a specified lookup array. It returns the position of the lookup value with reference to the lookup array. The MATCH function is often used with the INDEX function to extract specific data out of bulk.
Step 1 – Go to the Developer Tab
– Go to the Developer tab in the menu bar.
Step 2 – Click on the Visual Basic
– Click on the Visual Basic button on the right end of the ribbon.
– The visual Basic window will open.
Step 3 – Insert a New Module
– Right-click on the sheet name in the Project-VBA Project menu at the left of the window.
– Click on Insert.
– Click on Module
Step 4 – Declare a Procedure
– Declare a procedure by entering “Sub Match()”.
Step 5 – Enter the VBA Code
– Enter the VBA code:
Range(“B2”) = WorksheetFunction.Match(“DAVID”, Range(“A1:A6”), 0)
– Where the first range i.e. B2 is the reference of the cell in which you want to display the output.
– In the Match function the first argument i.e. DAVID is the lookup value.
– The second argument i.e. Range(“A1:A6”) is the reference of the lookup array.
– The third argument i.e. 0 is the match type.
Step 6 – Run the Code
– Run the code by clicking on the Run button in the menu bar.
– Click on the Run/Sub userForm option.
– The row number of the name “David” will be displayed in the targeted cell.
