Index Match Function in Excel

Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What Does INDEX Function Do in Excel?

The INDEX function can return the result from the row number. In addition, the MATCH function can give us the lookup value position in the array. The combination of the INDEX MATCH Excel function is very useful in addressing a key limitation of VLOOKUP, which we cannot use to search the table from left to right. However, the INDEX MATCH function can achieve this goal effortlessly. In this article, we will discuss this in detail.

For example, suppose we have an Excel worksheet containing the list of student’s names and the corresponding scores they achieved in the exams in the range A1:B4. If we need to extract the value of cell A1 which contains the student name, “Micheal,” which is within the specified array (range), we can use the INDEX function. Using the INDEX function,“=INDEX(A1:B4,1,1),” we can obtain the required extracted values at the intersection of the specified row and column numbers. In this scenario, inserting the INDEX Excel function formula in cell C1, it reaches the range A1:B4. And fetches the cell’s value at the intersection of the first row (row 1) and the first column (column A). The cell is A1, and its value is “Micheal.” So, the Excel INDEX function returns “Micheal.”

In the same Excel worksheet, suppose we need to know the position of the scores ( score is 80, which is in column B1) achieved by the student, “Micheal” Therefore, in such a scenario, we can use the Excel MATCH function. Applying the MATCH formula, “MATCH(80,A11:A15,0),” returns 1 since the score achieved by Micheal is at the first position in the Excel worksheet. 

You can download this Index Match Function Excel Template here – Index Match Function Excel Template

It is as simple as that. For now, look at the syntax of the INDEX function.

Index Formula

Array: From which column or array do we need the value?

Row Number: In the provided array, do we need the result from which row?

These two arguments are good enough in most situations. So, now we will look into the example of the INDEX function.

INDEX MATCH Function Video Explanation

 

Example

For this example, consider the below data.

Index Formula Example 1

We have data from A1 to B7 cell range. In the D2 cell, we have the month name, and for this month’s name, we need sales value in cell E2.

Let us open the INDEX function in cell E2.

Index Formula Example 1-1

An array is the first argument, i.e., from which column we need the result, i.e., we need results from the “sales” column, so select from B2 to B7.

 Example 1-2

Next is the ROW number, i.e., the selected range of cells from which row we need the result. In this example, we need the sales value for the month “Mar.” In the selected range, “Mar” is the third row, so we need results from the third row.

Index Formula Example 1-3

Ok, that’s all. Close the bracket and press the “Enter” key. We will have sales value for the month of “Mar.”

Example 1-4

Like this, based on the row number provided, we will get the value from the supplied array.

–>> If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

What Does MATCH Function Do in Excel?

The MATCH function is used to find the lookup value position in the supplied array. In simple terms, lookup value row number or column number in the range of cells. Below is the syntax of the MATCH functionSyntax Of The MATCH FunctionThe MATCH function looks for a specific value and returns its relative position in a given range of cells. The output is the first position found for the given value. Being a lookup and reference function, it works for both an exact and approximate match. For example, if the range A11:A15 consists of the numbers 2, 9, 8, 14, 32, the formula “MATCH(8,A11:A15,0)” returns 3. This is because the number 8 is at the third position. read more.

Match Formula

Lookup Value: For which lookup value are we trying to find the position?

Lookup Array: In which array or range of cells are we looking for the lookup value?

Match Type: This will decide what kind of result we need. We can provide zero for an exact match.

Example of MATCH Function in Excel

For this example, consider the above data only.

Example 1

From the above data, we are trying to get the month “Mar” position in cell E5. So, we must open the MATCH function in the E5 cell.

Match function Example 1-1

The first argument is “lookup value,” so here, our lookup value is “Mar,” therefore, select the D5 cell.

Example 1-2

The lookup array is from which range of cells we are trying to look for the lookup value position. So, we must select the “Month” column.

Example 1-3

The last argument is “MATCH type” since we look at the exact match supply 0.

Match function Example 1-4

So, in the lookup array A2:A7, the position of the lookup value “Mar” is 3.

Combination of INDEX + MATCH Function in Excel

The INDEX can return the result from the mentioned row number, and the MATCH function can give us the position of the lookup value in the array. Instead of supplying the row number to the INDEX formula, we can enclose the MATCH function to return the row number.

Example 1-3

We must first open the INDEX function in cell E2.

Index Match Function Example 1-1

For the first argument, the array supplies B2 to B7.

Index Match Function Example 1-2

Instead of supplying the row number as 3, open the MATCH function inside the INDEX function for the row number.

Index Match Function Example 1-3

Select the lookup value as a D2 cell.

Index Match Function Example 1-4

Now, we must select the lookup array as A2 to A7.

Index Match Function Example 1-5

Then, we must insert zero as the match type.

Index Match Function Example 1-6

So, based on the row number provided by the MATCH function, the INDEX function will return the sales value. We can change the “Month” name in cell D2 to see dynamically changing sales value.

Powerful Alternative to VLOOKUP

We all have used the VLOOKUP functionVLOOKUP FunctionThe VLOOKUP excel function searches for a particular value and returns a corresponding match based on a unique identifier. A unique identifier is uniquely associated with all the records of the database. For instance, employee ID, student roll number, customer contact number, seller email address, etc., are unique identifiers. read more day in and day out. But, one of the limitations of VLOOKUP is that it can only fetch the value from left to right, not from right to left.

For example, look at the below data.

Alternate Index Match Function 1

The above data lookup value is “Month,” and the result column is “Sales.” But, the data result column (Sales) is to the left of the lookup array table (Month), so we cannot use the Excel VLOOKUP function here, but we can still fetch with the combination of the INDEX function with the MATCH function for the data from the table.

Recommended Articles

This article is a guide to Index Match Function in Excel. Here, we learn how to use the INDEX and MATCH functions as a powerful alternative to VLOOKUP along with examples and a downloadable Excel template. You may learn more about Excel from the following articles: