Using Index with Match in VBA for Lookups

How to use Match function with Index function in VBA for lookups for data analysis in Excel. Earlier we had shown how to nest the Match function inside the Index function.

The MATCH function searches for a value in an array and returns the relative position of that item. The first two parameters lookup_value and lookup_array are straight forward but the match_type requires some explanation:


1: The MATCH function will find the largest value which is less than or equal to value. We need to sort the data in our array or table in ascending order. If the match_type parameter is omitted, Excel assumes a match_type of 1 because that is the default value,

0: The MATCH function will find the first value which is equal to the searched value. The array need not be sorted.

-1: The MATCH function will find the smallest value which is greater than or equal to value you are searching. We need to sort the data in our array or table in descending order.

INDEX Function:

The INDEX function returns a value in a table or array based on the intersection of a row and column  within that table or array. The first row in the table below the header is row 1 and the first column in the table is column 1.

The syntax for the INDEX function in MS Excel is:
INDEX( table, row_number, column_number )
Let’s understand the parameters or arguments of the INDEX function.
A table is a range of cells that contains the table of data also know as array of data.
The row_number is the row position in the table (below the header) where the value we want to lookup is located.
The column_number is the column position in the table where the value we wish to lookup is located.

Watch the video below and then study the code step by step.

You can watch this video also on YouTube.

Here’s the complete VBA code to search for data using INDEX and MATCH functions:

Sub myMatch()
‘Range(“G2”) = “= Match(F2, B2:B6, 0)”
‘Range(“G2”) = “=index(C2:C6,3,1)”
Range(“G2”) = “=Index(C2:C6,match(F2,B2:B6,0))”
End Sub
Further reading: