How to Use INDEX MATCH Instead of VLOOKUP in Excel (3 Ways)

VLOOKUP, INDEX, 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 the substitute for the VLOOKUP function. In this article, we will show how to use the INDEX-MATCH instead of VLOOKUP in Excel.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Introduction to INDEX and MATCH Functions

The INDEX Function

The INDEX function returns a value or the reference to a value from within a table or range. INDEX function 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.

The 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

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 on 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
  • 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 use the INDEX-MATCH functions.


3 Ways to Use INDEX MATCH instead of VLOOKUP in Excel

1. INDEX MATCH to Lookup Right to Left in Excel

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 names and want to return ID. So, the data set will look like this:

Step 2:

  • Write “Allisa” in the Name
  • 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
  • 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.

Read More: INDEX MATCH vs VLOOKUP Function (9 Examples)


2. INDEX MATCH instead of VLOOKUP with Multiple Criteria in Excel

In INDEX-MATCH Function we can use multiple criteria 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.

Read More: Excel INDEX and MATCH Functions with Multiple Criteria (4 Formulas)


Similar Readings


3. Apply INDEX MATCH to Lookup in Both Row and Column

In the section, we will show how to look up both in the row and the column. 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.

Read More: Index Match Multiple Criteria in Rows and Columns in Excel


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.


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 have a look at our website Exceldemy.com and give your suggestions in the comment box.


Related Articles

Alok

Alok

Hello, this is Alok. I am working as an Excel & VBA Content Developer at Exceldemy. I want to provide solutions to various Excel-based problems. I completed my study at East West University major in Telecommunications Engineering. I love traveling, reading books, playing cricket.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo