How to Use the Match Function in VBA

By Lysis

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.

Step 1

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 Monday----------->$150.00 Tuesday---------->$90.00 Wednesday----->$200.00

Step 2

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.

Step 3

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.

Step 4

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.

×