The majority of Excel users concur that **INDEX****–****MATCH** is significantly superior to **VLOOKUP** when it comes to matching data in a vertically arranged table. Many people, however, continue to use **VLOOKUP** because of its simplicity and because they do not fully understand all of the advantages of using the **INDEX-MATCH** formula in Excel. In this article, we will explain why using the **INDEX-MATCH** formula is better and how you can use **INDEX-MATCH** instead of **VLOOKUP** in Excel.

**Contents**hide

## 📁 Download Excel File

Download this file to practice.

## Why Using INDEX-MATCH Formula Better Than VLOOKUP Function?

The most commonly utilized Excel tools for more complex lookups are **INDEX** and **MATCH**. This is due to how versatile **INDEX** and **MATCH** are. You can perform lookups in all directions, including horizontally and vertically, in two directions, left lookup, with or without case, and even based on multiple criteria. **INDEX** basically finds the value at a specific location in a range. In order to locate an item in a range, the **MATCH function** was created with that one goal in mind. Using **INDEX **and **MATCH** together serves a wide range of functions. In short, the **MATCH** function is nested in the **INDEX** function, so **MATCH** finds the numeric position of a value and **INDEX** retrieves that value.

**VLOOKUP** is a search function that scans vertically across a table or spreadsheet for specific data. **VLOOKUP** is an abbreviation for vertical lookup. It allows for approximate and exact matching, as well as wildcards (“*” or “?”) for partial matches. **VLOOKUP** requires lookup values to be in the first column of the table passed to it.

While VLOOKUP is easy and straightforward to use, it has some limitations. There are a couple of reasons to use the INDEX-MATCH combination instead of VLOOKUP. They are-

**Column Reference**

While **INDEX-MATCH** needs a robust column reference, **VLOOKUP** needs a fixed column reference. When using **VLOOKUP**, you have to manually type a number that refers to the column whose value you want to return. So adding a new column to the table breaks the **VLOOKUP** formula gets because you are using a fixed reference. You can click **INDEX MATCH** to select which column to retrieve the value from.

**Adding/Removing Columns**

The syntax of VLOOKUP requires that you specify the index number of the column from which you want to retrieve the data, so when a new column is added to or deleted from a lookup table, the formulas become broken or produce incorrect results. The index number inevitably changes when you add or remove columns. When using the **INDEX-MATCH** formula, you specify the return column range rather than an index number. That’s why you can add and remove as many columns as you like without having to worry about updating all of the related formulas.

**Position of LOOKUP value**

The lookup value must be in the first column for **VLOOKUP** to function. **VLOOKUP** is unable to perform left-looking searches. However, because **INDEX-MATCH** conducts the lookup both horizontally and vertically, it resolves this issue. Therefore, the lookup value need not be in the first column and may instead be located elsewhere.

**The size of LOOKUP value**

To avoid getting the **#VALUE! **error, make sure the total length of your lookup criteria is no longer than 255 characters. On the other hand, **INDEX MATCH** can lookup values longer than 255 characters.

**Processing Speed**

If your table size is small, the performance difference between **VLOOKUP** and **INDEX-MATCH** will be insignificant. However, **INDEX-MATCH** will perform much more quickly than **VLOOKUP** if your worksheets have a large number of rows and formulas because Excel will only process the lookup and return columns rather than the entire table.

**📕 Read More: Use INDEX Function to Match and Return Multiple Values Vertically**

## Learn to Use INDEX-MATCH Instead of VLOOKUP in Excel with These 3 Examples

In this article, we will learn how to use **INDEX****–****MATCH** instead of **VLOOKUP** in Excel in 3 simple but efficient ways and understand why it is most recommended. We will use a dataset of goals scored by different teams in last FIFA World Cups. The first scenario will explain the basic use of the** INDEX-MATCH** formula, and the second one will showcase how this formula returns results for multiple criteria. Lastly, we will build a formula where rows and columns will be matched to find certain results.

**Example 1**

### 1. Matching Columns

**VLOOKUP**, as previously stated, cannot glance to the left. So, unless your lookup values are in the leftmost column, there’s no way a **VLOOKUP** formula can get the desired result. The **INDEX****–****MATCH** formula in Excel is more adaptable and is unconcerned with the location of the lookup and return columns.

So for our first method, we will explain how to retrieve data using the **INDEX-MATCH** formula from a matching column.

**⬇️⬇️ STEPS ⬇️⬇️ **

- First, insert the Team name in cell
**C17**.

- Next, in cell
**C18**, insert this formula to get corresponding goals.

**=INDEX(D6:D15,MATCH(C17,B6:B15,0))**

**🔨 Formula Breakdown**

`👉`

Firstly, the **MATCH** function will search for the value of **C17** in **B6:B15** and match the corresponding value in **D6:D15**.

`👉`

Next, the **INDEX** function retrieves that value from **D6:D15**. The **VLOOKUP** would’ve returned an error.

- Press
**Enter**to get the goal scored by the team in cell**C17**.

**📕 Read More: 3 Easy Ways to Use INDEX-MATCH Across Multiple Sheets**

**Example 2**

### 2. Matching Multiple Criteria

The requirement to include a helper column is a key drawback of using **VLOOKUP** for matching several criteria. The good news is that Excel’s **INDEX****–****MATCH** method can also do a lookup using two or more criteria without altering or rearranging your source data.

**⬇️⬇️ STEPS ⬇️⬇️ **

- First, insert the Goal number in cell
**C17**and the Team name in**C18**.

- Next, in cell
**C19**, insert this formula to get the corresponding most scorer.

**=INDEX(C6:C15,MATCH(1,(C17=D6:D15)*(C18=B6:B15),0))**

**🔨 Formula Breakdown**

`👉`

For each individual criterion, we built two arrays of **TRUE** and **FALSE** values, signifying matches and non-matches, and then multiplied the corresponding components of these arrays to analyze multiple criteria. The multiplication operation converts **TRUE** and **FALSE** to 1 and 0, respectively, and creates an array with 1’s designating rows that satisfy all requirements.

`👉`

With a lookup value of 1, the **MATCH** function identifies the first “1” in the array and gives its location to **INDEX**, which produces a value from the given column in this row.

- Press
**Enter**to get the Most goal scored by the team in cell**C18**.

**📕 Read More: 6 Methods to Match Multiple Criteria from Different Arrays in Excel**

**Example 3**

### 3. Matching Row and Column

In this method, we will look at an example where the formula will search for values matching rows and columns. We will use two **MATCH** functions to get the row and column index numbers, and then the **INDEX** function will fetch the particular value.

**⬇️⬇️ STEPS ⬇️⬇️**

- First, insert the Team name in cell
**C15**and the Year in cell**C16**.

- Next, in cell
**C17**, insert this formula to get the corresponding Goals.

**=INDEX(C6:E13,MATCH(C15,B6:B13,0),MATCH(C16,C5:E5,0))**

**🔨 Formula Breakdown **

`👉`

**MATCH(C16,C5:E5,0)** looks in cells **C5:E5** for the value in cell **C16** (“2014”) and gives its position, **3**.

`👉`

**MATCH(C15,B6:B13,0))** looks through **B6:B13** to find the value in cell **C15** (“Brazil”), which is **6**.

`👉`

The aforementioned row and column numbers correspond to the **INDEX** function’s relevant arguments: **INDEX(C6:E13, 6, 3)**

As a consequence, Excel found the value in cell **E11** at the intersection of the **6th **row and **3rd **column in the range **C6:E13**.

- Press
**Enter**to get the most goal scored by a team in a particular World cup in cell**C17**.

## 📝 Takeaways from This Article

`📌`

Firstly, we explained the basics of **INDEX** and **MATCH** functions and why their combination is better than using **VLOOKUP**.

`📌`

Then, we demonstrated an example of applying the **INDEX-MATCH** formula.

`📌`

We also used this combination for fetching data when multiple criteria are demanded.

`📌`

Last but not least, we created a formula for matching rows and columns simultaneously.

## Conclusion

We have come to the conclusion of our article. I hope that after reading this article, you will feel comfortable using the **INDEX-MATCH** formula instead of **VLOOKUP** in Excel. If you have any questions, feel free to comment below. I will try to answer them as soon as possible. Have fun using Excel, and you can visit **Excelden.com** for more tutorials. Thank You.