APPLYING INDEX AND MATCH FUNCTIONS IN MICROSOFT EXCEL

In my last two articles, I wrote about how to use horizontal and vertical lookup functions in Excel. In this article, I’ll be writing about how to use the index and match functions in Microsoft Excel.

• The index and match functions are also used to perform lookups in a much more simpler way in Microsoft Excel. The index and match functions is the most popular tool in excel for performing lookups.
• The match function searches for a specified item in a range of cells, and then returns the relative position of that item in the range.
• The index function returns the value at a given location in a range or array.
• It is a combination of two functions (index and match) which can perform 2-way lookup, left lookup, case sensitive lookup and lookup based on multiple criteria.

# Let us look at how to use index and match functions. The dataset I will be using is a dummy dataset I created by myself (picture will be shown below)

# The index function takes three arguments which are: the lookup array, the lookup row number and the lookup column index number which is optional. In a case where the array used is the array of the total data, the lookup column index number has to be specified. Whereas, if the array used is the array of just the lookup column, there will be no need to input the lookup column index number.

# Let’s assume we want to look for the score of Mary Bajisma from the dataset, below is the formula to write using index and match:

1. =INDEX(C2:C7, MATCH(G5, A2:A7, 0)) OR
2. =INDEX(A2:D7, MATCH(G5, A2:A7, 0),3)

Picture depicting using the Match Function

# Let me split the formulas for detailed explanation;

• By splitting the first one, we have: =INDEX(C2:C7) AND =MATCH(G5, A2:A7, 0)
• C2:C7 represents the array of the lookup column i.e. Score is located in column C.
• G5 represents the lookup value (Mary)
• 0 represents exact match
• Using the match function alone as shown above will return the position of Mary in the data which is 2 (the row number). By combining the formula together back, we will have: =INDEX(C2:C7, 2) which will return the score where the lookup row index number is 2 which is 87. (The 2 here is the result gotten from using the match function which is in cell H5 as depicted below).

Picture depicting using the index function (using lookup column array) and match function with splitting

Picture depicting using the index function (using lookup column array) and match function without splitting

# If we are to use the second formula, by splitting it, we have: =INDEX(A2:D7, Match function result, 3) AND =MATCH(G5, A2:A7, 0)

• The match function remains the same which will return 2 (the row number)
• By combining the formula together back, we will have: =INDEX(A2:D7, 2, 3). The 3 here represents the column index number for Score column while A2:D7 represents the array of the total data. (The 2 here is still in the H5 cell as depicted in the picture below).
• The index function will search for the column index number 3 (Score) for row index number 2 from the entire data(A2:D7) which will also return 87.

Picture depicting using the index function (using entire data array) and match function with splitting

Picture depicting using the index function (using entire data array) and match function without splitting

# Another way to also do this is to first convert the data to a table and name it (I named mine as Data). Pictures are shown below.

Our first formula will now be written as: =INDEX(Data[Score], MATCH(G5, Data[First Name], 0)) and

Our second formula will now be written as: =INDEX(Data, MATCH(G5, Data[First Name], 0), 3)

Picture depicting using the index function (using entire data array) and match function from a table

Picture depicting using the index function (using lookup column array) and match function from a table

There are some few important things I want you to note below:

1. Match type has 3 types which are 0, 1 and -1. 0 represents exact match, 1 represents less than and -1 represents greater than. We needed an exact match in our case, hence, we used 0.
2. Microsoft Excel is not case-sensitive, hence, if we wanted to look for the score of mary (small letter) from the data, the value that will be returned will still be 87 which is wrong (it’s supposed to be 92). This will occur because:

• VLookup, HLookup, Index and Match standard formulas will always return the first look alike value it finds in the data.
• Excel is case-insensitive. In Excel, “Mary” is the same as “mary”.

P.S: In my next article, we will learn how to perform case-sensitive lookup with index and match.

N.B: The caption to each image posted is written underneath the image.

If while learning, you feel the need to ask questions for clarity, you can send me a mail @[email protected] or whatsapp 09021833246.