Hassan Shuvo
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.
📁 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.
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.
🔨 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
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.
🔨 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
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.
🔨 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.
