FLASH SALE! - "CHATGPT AND ARTIFICIAL INTELLIGENCE FOR MICROSOFT EXCEL AT 60% OFF" Enroll Now

Index Match Function In Excel

Updated on February 28, 2024
Article byJeevan A Y
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What Is INDEX Match Function 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.

Key Takeaways

  • The INDEX MATCH in Excel function is used to search tables from left to right. Unlike VLOOKUP, INDEX MATCH lets you return a result from a specific row using the INDEX function and find the position of the lookup value in the array using the MATCH function.
  • Use INDEX and MATCH functions to retrieve data from specific rows in an array—nest MATCH within INDEX to automate row number retrieval.
  • One limitation of VLOOKUP is that it can only look up values from left to right in a table, not right to left.

Syntax

Since INDEX MATCH function is not an available function by default, we have to combine the functions, INDEX and MATCH.

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

The syntax of the INDEX function is:

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.

Similarly, 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 is:

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.

So, the syntax of INDEX MATCH function is

=INDEX(MATCH)

INDEX MATCH Function Video Explanation

 

How To Use INDEX Function In Excel?

We know that INDEX function returns the result from row number. Let us learn how this function works with the following example.

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.

How To Use MATCH Function 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.

Example

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

Consider the below example.

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.

Important Things To Note

  • The INDEX function is used to obtain the result from row number.
  • The MATCH function is used to find the position of the array value.
  • Using INDEX MATCH function, i.e., by combining the two functions in Excel, we can find better results than VLOOKUP function.

Frequently Asked Questions (FAQs)

1. What are the common problems with INDEX MATCH Excel?

An issue has been identified in cross-sheet formulas that employ the =INDEX(MATCH()) function. These formulas are found to misconstrue dash symbols and do not consistently sequentially match characters. This can result in erroneous interpretation of data and generate incorrect results. It is, therefore, imperative to be aware of this issue and take necessary precautions to ensure accurate outputs.

2. What is the difference between INDEX MATCH and offset match?

When it comes to the differences between INDEX() and OFFSET(), it’s important to note that INDEX() is used to refer to an array or range of cells directly. In contrast, OFFSET() is used to refer to a range that is located a specific number of rows and columns away from another cell or range of cells.

3. Does INDEX MATCH work horizontally?

INDEX and MATCH in Excel are popular tools for performing advanced lookups. They are highly flexible and enable various types of lookups, such as horizontal, vertical, left, case-sensitive, and multi-criteria lookups.

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: