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:

match_type:

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:

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.