How to Use 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 have a data set of a company that contains the ID, name, and salary of employees. We will find out the Salary of employees by searching for the ID.

Step2:

• Put an ID condition as shown in the box below:

• Insert the following formula in the cell below it:

• Press Enter.

We get 3. It means our specified value is in the 3rd cell of that range.

• Wrap the formula in the INDEX function as below:

• Press Enter.

We get the salary of the employee with an ID of A-003 in the result.

Using INDEX MATCH Instead of VLOOKUP to Lookup from Right to Left

Steps:

• We will search for names and want to return ID.

• Write “Allisa” in the Name cell.
• Insert the following formula for the result cell:

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.

• Press Enter.

We get the ID as the return.

INDEX MATCH with Multiple Criteria Where VLOOKUP Seems Impractical

Steps:

• Modify the data set to apply multiple criteria.

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

• Put the conditions in the required boxes.

• Put the formula in Cell C14:

• Press Enter.

Apply INDEX MATCH Instead of VLOOKUP to Lookup in Both Row and Column

Steps:

• Modify the data set to apply the functions.

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

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

• Insert the following formula in Cell C14.

• Press Enter.

Advantages of Using INDEX MATCH Instead of VLOOKUP in Excel

Dynamic Column Reference

One of the main advantages of INDEX-MATCH over the VLOOKUP is the column reference. VLOOKUP requires a static column reference. On the other hand, INDEX-MATCH uses dynamic column referencing. This allows you to readily modify the dataset or the formula to search for other values.

Right to Left Lookup

VLOOKUP returns a value to the right 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 referencing.

Easy to Insert or Delete New Column

VLOOKUP uses a 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.

No Limit for a Lookup Value’s Size

VLOOKUP only accepts 255 characters for arguments, while INDEX-MATCH doesn’t have that limit.

Minimize Processing Time

The VLOOKUP function looks up the whole array or table. The INDEX-MATCH function lookup only the mentioned range or column. So, it returns results faster than VLOOKUP.

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 be in any column and also provides results from any column selected by the user.

Download the Practice Workbook

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