3 Examples to Use INDEX-MATCH Instead of VLOOKUP in Excel

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.

Introduction


๐Ÿ“ย  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.

Dataset to use INDEX-MATCH instead of VLOOKUP


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.

Data input

  • Next, in cell C18, insert this formula to get corresponding goals.
=INDEX(D6:D15,MATCH(C17,B6:B15,0))

INDEX-MATCH formula to use INDEX-MATCH instead of VLOOKUP

๐Ÿ”จ 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.

INDEX-MATCH result

๐Ÿ“• 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.

data for multiple criteria

  • 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))

INDEX-MATCH formula for multiple criteria

๐Ÿ”จ 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.

result for multiple criteria

๐Ÿ“• 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.

Data input for matching row and column

  • 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 for mathing row and column to use INDEX-MATCH instead of VLOOKUP

๐Ÿ”จ 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.

Matching row and column result


๐Ÿ“ 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.


Related Article

(Visited 35 times, 1 visits today)
Hassan Shuvo

Hassan Shuvo

Hello, I am Mehedi Hassan Shuvo.I am an Engineering graduate from Bangladesh University of Engineering and Technology. I love reading Thriller books, watching Anime, and playing Cricket. I also love learning about new software. Excel is one of my favorite ones by far. My efforts will be worthwhile if I can utilize my expertise to assist anyone. If you find any faults in my writing please let me know.I will try to correct them as I am learning everyday.

We will be happy to hear your thoughts

Leave a reply

ExcelDen
Logo