The "Match" function in Microsoft Excel VBA (Visual Basic for Applications) procedures finds a match within a range of cells and prints it to the spreadsheet. The function is useful when entering data that needs to be evaluated for certain values. For instance, if you have a spreadsheet filled with sales numbers, the match function can be used to find sales that were below a certain numeric threshold. The function can be used anywhere on the Excel spreadsheet and only takes one line of code in a cell.
Create a list of numbers in the spreadsheet. In this example, a list of daily sales revenue is used. Enter the following into the Excel spreadsheet:
Column A--------Column B
Understand the Match function syntax. To successfully use the function, it's important to understand the syntax. Below is the basic syntax:
Match ( my_lookup_value, my_lookup_array , [my_match_type] )
The "my_lookup_value" is the value you want to find in the range specified in "my_lookup_array." "My_match_type" is one of three values. Using "0" for this value finds an exact match. Using number "1" triggers the function to find the closest match that is less than the given number. Conversely, number "2" triggers the function to find the closest match that is greater than the given look-up value.
Enter the function in an available cell on the spreadsheet. Once the syntax is known, you can enter the function in a cell and customize it to your needs. In this example, the Match function is used to detect sales that are under $100.00. Using the data set up in Step 1, the function is below:
=Match (100, B1:B3, 1 )
Notice the equals sign. This is necessary so Excel knows the text entered is a function and not literal.
Evaluate and test the results. In this example, the answer shown is "90.00." Match returns the closest number to the threshold value, so whatever is closest to 100 will be shown.
VBA Search & Word Replacement
Visual Basic for Applications (VBA) is a programming language built into Microsoft Office's Word, Excel and Access programs. Users of these programs...
How to Use VBA and Regex
Use the VBScript regular expression class to enable your VBA programs to perform regular expression searches. Regular expressions are text patterns that...
How to Use Excel Match
The Excel MATCH function returns the row number or relative position of the item searched for within a range (array). It is...
How to Use Variables to Select a Cell in VBA
Visual Basic for Applications (VBA) is a programming language used exclusively for Microsoft Office applications. VBA allows you to customize Excel by...
How to Call a Function in VBA
Visual Basic for Applications is Microsoft’s event-driven programming language, which is associated with Visual Basic 6 and its Integrated Development Environment. VBA...
How to Do an Exact Match in Excel Using VBA
A Visual Basic Application procedure efficiently searches through large amounts of data using the Find method. This method is used to do...
Excel VBA: Find Text
Learning how to find text with Excel's programming language, VBA or Visual Basic for Applications, is useful for finding specific information quickly...
OFFSET and MATCH Functions in Excel
Extracting data from tables in Excel is routinely done in Excel by way of the OFFSET and MATCH functions. The primary purpose...