How to Use INDEX MATCH Instead of VLOOKUP in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.

Syntax:

INDEX(array, row_num, [column_num])

Arguments:

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.

Syntax:

MATCH(lookup_value, lookup_array, [match_type])

Arguments:

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.

Data set to Use INDEX MATCH instead of VLOOKUP in Excel

Now, will find out the Salary of employees searching on the ID instead of other options.

Step 1:

  • First, put an ID as shown in the box below:

Step 2:

  • 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:
=MATCH(C12,B5:B10,0)

Combine INDEX and MATCH Functions

Step 3:

  • Then, press Enter.

In the return, we get 3. It means our specified value is in the 3rd cell of that range.

Step 4:

  • Now, insert the INDEX function.
  • We want to get the salary. So, we used D5:D10 as the range.
  • The formula will be:
=INDEX(D5:D10,MATCH(C12,B5:B10,0))

Combine INDEX and MATCH Functions

Step 5:

  • Then press Enter.

Combine INDEX and MATCH Functions

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.

Step 1:

  • We will search for names and want to return ID. So, the data set will look like this:

Step 2:

  • Write “Allisa” in the Name column.
  • Now, write the INDEX-MATCH formula:
=INDEX(B5:B10,MATCH(C12,C5:C10,0))
  • 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.

INDEX MATCH to Lookup Right to Left in Excel

Step 3:

  • Then, press Enter.

We get the ID as the return. But if we used VLOOKUP that will return an error.


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.

Step 1:

  • First, modify the data set to apply multiple criteria.

Step 2:

  • We will apply two criteria Department and Name and want Salary as the result.

Step 3:

  • Put the condition on the required box as the following image.

INDEX MATCH instead of VLOOKUP with Multiple Criteria in Excel

Step 4:

  • Now, put the formula on Cell C14.
  • The formula is:
=INDEX(E5:E10,MATCH(1,(C12=D5:D10)*(C13=C5:C10),0))

INDEX MATCH instead of VLOOKUP with Multiple Criteria in Excel

Step 5:

  • Finally, press Enter.

INDEX MATCH instead of VLOOKUP with Multiple Criteria in Excel

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.

Step 1:

  • First, modify the data set to apply the functions.

Step 2:

  • We set to look up names along column B and year in the 4th row.

Step 3:

  • Set the condition on the required boxes on name and year.

Apply INDEX MATCH to Lookup in Both Row and Column

Step 4:

  • Now, write the formula on Cell C14.
  • The formula is:
=INDEX(C5:E10,MATCH(C12,B5:B10,0),MATCH(C13,C4:E4,0))

Apply INDEX MATCH to Lookup in Both Row and Column

Step 5:

  • Now, press Enter.

Apply INDEX MATCH to Lookup in Both Row and Column

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.


Conclusion

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.


<< Go Back to INDEX MATCH | Formula List | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Alok Paul
Alok Paul

Alok Paul has completed his B.Sc. in Electronics and Telecommunication Engineering from East West University. He has been working on the ExcelDemy project for more than 2 years. He has written 220+ articles and replied to numerous comments. He is experienced in Microsoft Office, especially in Excel. He also led some teams on Excel and VBA content development. He has a keen interest in Advanced Excel, Data analysis, Excel Pivot Table, Charts, and Dashboard. He loves to research... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo