VLOOKUP, INDEX, and MATCH are popular Excel functions used in Microsoft Excel. VLOOKUP is very common in large data operations. The INDEX-MATCH function combinedly can be used as a substitute for the VLOOKUP function. In this article, we will show how to use the INDEX-MATCH instead of VLOOKUP in Excel.
Excel INDEX Function
The INDEX function returns a value or the reference to a value from within a table or range. The INDEX function is used in two ways: Array form and Reference form.
INDEX(array, row_num, [column_num])
array – It is the range of cells or an array constant. The use of row_num and column_num depends on the rows or columns in this array.
row_num – It is required unless column_num is present. Selects the row in the array from which to return a value. If row_num is omitted, column_num is required.
column_num – It selects the column in the array from which to return a value. If column_num is omitted, row_num is required.
Excel MATCH Function
The MATCH function looks for a specified object in a range of cells and then returns the corresponding position of that object. This function works in any direction and gets the exact match.
MATCH(lookup_value, lookup_array, [match_type])
lookup_value – This is the value we want to match in an array. This may be a value (number, text, or logical value) or a cell reference to a number, text, or logical value.
lookup_array – It is the specified range we want to search from.
match_type – This is optional. The numbers are -1, 0, or 1. The match_type argument specifies how Excel matches lookup_value with values in lookup_array. The default value for this argument is 1.
How to Combine INDEX and MATCH Functions in Excel
We will use the combination of INDEX and MATCH functions rather than using the VLOOKUP function. Here, we will show how to combine the two functions.
To apply the formula, we take a data set of a company that contains the ID, name, and salary of employees.
Now, will find out the Salary of employees searching on the ID instead of other options.
- First, put an ID as shown in the box below:
- Write the MATCH function on Cell C13.
- We will try to find a match of Cell C12 in the range B5:B10. So, the formula is:
- Then, press Enter.
In the return, we get 3. It means our specified value is in the 3rd cell of that range.
- Now, insert the INDEX function.
- We want to get the salary. So, we used D5:D10 as the range.
- The formula will be:
- Then press Enter.
Finally, we get the salary of the A-003 in the result. In this way, we combinedly the use of the INDEX-MATCH functions.
Using INDEX MATCH Instead of VLOOKUP to Lookup from Right to Left
In this section, we will show how the INDEX-MATCH function can look up from the right to the left. VLOOKUP only can search objects from left to right and the search object must be in the first column. We don’t have to follow these rules in the case of the INDEX-MATCH function.
- We will search for names and want to return ID. So, the data set will look like this:
- Write “Allisa” in the Name column.
- Now, write the INDEX-MATCH formula:
- Here, we will look up in Name column and will get a return from the ID column.
- We are applying operation from the right to the left.
- Then, press Enter.
We get the ID as the return. But if we used VLOOKUP that will return an error.
Read More: Excel INDEX MATCH Example
INDEX MATCH with Multiple Criteria Where VLOOKUP Seems Impractical
We can use multiple criteria in the INDEX-MATCH function that are not possible in the case of VLOOKUP.
- First, modify the data set to apply multiple criteria.
- We will apply two criteria Department and Name and want Salary as the result.
- Put the condition on the required box as the following image.
- Now, put the formula on Cell C14.
- The formula is:
- Finally, press Enter.
As both conditions match, we get a result. If any of the criteria don’t fulfill that result will be an error. In VLOOKUP this is not possible. That’s why we use INDEX-MATCH instead of VLOOKUP.
Apply INDEX MATCH Instead of VLOOKUP to Lookup in Both Row and Column
In this section, we will show how to look up with multiple criteria both in the rows and the columns. VLOOKUP is unable to search both in the row and column.
- First, modify the data set to apply the functions.
- We set to look up names along column B and year in the 4th row.
- Set the condition on the required boxes on name and year.
- Now, write the formula on Cell C14.
- The formula is:
- Now, press Enter.
Finally, we the amount of increment Jose got in the year 2020.
Advantages of Using INDEX MATCH Instead of VLOOKUP in Excel
1. Dynamic Column Reference
One of the main advantages of INDEX-MATCH over the VLOOKUP is the column reference. VLOOKUP requires static column reference. On the other hand, INDEX-MATCH requires dynamic column reference. Due to static reference when we add or delete any row or column the formula remains unchanged. The effect of column changing is not reflected in that.
2. Right to Left Lookup
The VLOOKUP allows returning a value to the left. But cannot perform any operation while searching from right to left. It is one of the most significant advantages of the INDEX-MATCH function. We can use the INDEX-MATCH function in some situations where VLOOKUP is unable to work due to left and right side references.
3. Easy to Insert or Delete New Column
We already know that VLOOKUP uses static column reference. So, at the time of adding or deleting any new column, we need to modify the formula each time. And we need to do this manually. But when we work with large data set this modification becomes very complex. Rather than using the INDEX-MATCH function, we don’t need to think about this. The formula is automatically modified.
4. No Limit for a Lookup Value’s Size
We need to make sure that the length of the lookup criteria should not exceed 255 characters in VLOOKUP. Otherwise, it will show an error value. In the case of INDEX-MATCH, we can look up more than 255 characters.
5. Minimize Processing Time
When we are considering the processing time of the INDEX-MATCH function reduces the processing time to a large amount. The VLOOKUP function looks up the whole array or table. And INDEX-MATCH function lookup only the mentioned range or column. So, it gives results in the shortest time compared to the VLOOKUP.
6. Lookup Value Position
In VLOOKUP the lookup value must be in the first column of the array or range. But in the INDEX-MATCH function, the look-up value can locate at any column and get also provides results from any column selected by the user.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
In this article, we described how to use the INDEX-MATCH function instead of VLOOKUP in Excel. We also explain the advantages of the INDEX-MATCH function over the VLOOKUP. I hope this will satisfy your needs. Please give your suggestions in the comment box.
- INDEX-MATCH Formula to Find Minimum Value in Excel
- How to Use INDIRECT, INDEX, and MATCH Functions in Excel
- How to Use INDEX MATCH with Multiple Criteria for Date Range
- Excel INDEX MATCH If Cell Contains Text
- XLOOKUP vs INDEX-MATCH in Excel
- [Fixed!] INDEX MATCH Not Returning Correct Value in Excel
- Excel INDEX MATCH to Return Multiple Values in One Cell