Index Match Function In Excel
Last Updated :
21 Aug, 2024
Blog Author :
Edited by :
Ashish Kumar Srivastav
Reviewed by :
Dheeraj Vaidya, CFA, FRM
Table Of Contents
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.
Table of contents
- What Is INDEX Match Function In Excel?
- 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.
The syntax of the INDEX function is:
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 function is:
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. Different functions in Excel have different features that aim to make handling and working with data easier. To explore more, you can enroll in Crash Course In Excel LOOKUP Function.
Let us now learn how this function works with the following example.
Example
For this example, consider the below data.
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.
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.
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.
Ok, that’s all. Close the bracket and press the "Enter" key. We will have sales value for the month of “Mar.”
Like this, based on the row number provided, we will get the value from the supplied array.
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.
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.
The first argument is “lookup value,” so here, our lookup value is “Mar,” therefore, select the D5 cell.
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.
The last argument is "MATCH type" since we look at the exact match supply 0.
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.
We must first open the INDEX function in cell E2.
For the first argument, the array supplies B2 to B7.
Instead of supplying the row number as 3, open the MATCH function inside the INDEX function for the row number.
Select the lookup value as a D2 cell.
Now, we must select the lookup array as A2 to A7.
Then, we must insert zero as the match type.
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 function 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.
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:
support@wallstreetmojo.com