Ditch VLOOKUP: Why INDEX MATCH is Your Excel Power Move
The Gist: Learn how to use the INDEX and MATCH functions to level up your Excel game, offering a more flexible alternative to the traditional VLOOKUP.
Skill Level: Beginner
Grab the Practice File
Download the Excel file we use in this tutorial. It’s the perfect way to follow along and solidify your understanding by putting these formulas into practice.
Why INDEX MATCH Wins Over VLOOKUP
Before we dive in, let’s explore why you should add INDEX MATCH to your Excel toolkit. There are two key advantages it holds over VLOOKUP.
#1 – Lookup to the Left? No Problem!
VLOOKUP has a frustrating limitation: it can only return values from columns to the right of your lookup column. INDEX MATCH breaks free from this constraint, allowing you to retrieve data from columns to the *left*.
While you *could* technically use the CHOOSE function with VLOOKUP to work around this, it’s often more trouble than it’s worth.
#2 – Precise Lookups, Less Maintenance
INDEX MATCH lets you specify single columns for both your lookup and return ranges. VLOOKUP, on the other hand, requires you to define an entire table array.
Why is this a big deal? Because VLOOKUP formulas are prone to breaking when columns are inserted or deleted within the table array. They can also slow down calculations if there are complex formulas within the table. INDEX MATCH offers greater stability and requires less maintenance when your worksheet structure changes.
Deconstructing the INDEX MATCH Formula
An INDEX MATCH formula combines both the INDEX and MATCH functions, resulting in something that might look like this:
=INDEX($B$2:$B$8,MATCH(A12,$D$2:$D$8,0))
It can seem daunting at first, but don’t worry! We’ll break it down step-by-step.
We’ll start by understanding the MATCH function, then move on to INDEX, and finally, combine them into a single, powerful formula.
MATCH: VLOOKUP’s Row-Finding Cousin
Think of the MATCH function as VLOOKUP’s close relative. Its job is to search a range of cells for a match. However, instead of returning the *value* of a cell, it returns the *row or column number* where the match is found.
The MATCH function’s arguments are also similar to VLOOKUP’s. The key difference is that MATCH’s lookup_array argument is a single row or column. This eliminates the need for VLOOKUP’s column index number argument.
Let’s look at a real-world example.
MATCH in Action: Finding Your Favorite Starbucks Drink
Let’s say we want to use the MATCH function to find the row number for “Caffe Mocha” in our Starbucks menu.
Here’s how to write the MATCH formula:
1. lookup_value – The “What”
First, tell MATCH what you’re looking for. In this case, we’re searching for “Caffe Mocha,” which we’ve entered in cell A12 and referenced in the formula.
2. lookup_array – The “Where”
Next, tell MATCH where to search for the lookup value. We’ve selected the range $A$2:$A$8, which contains our list of items. MATCH will scan this column from top to bottom until it finds a match.
Using an absolute reference (press F4 after selecting the range) ensures that the range doesn’t change if you copy the formula down. It’s a good habit to develop.
Keep in mind that you can also specify a row for this argument, in which case MATCH will search across the row from left to right.
3. [match_type] – The “Closest/Exact” Match
Here, you specify whether the function should look for an exact match, or a value that is less than or greater than the lookup_value.
MATCH defaults to 1 (less than), so you’ll almost always want to specify 0 (zero) for an exact match. This is similar to using FALSE or 0 in VLOOKUP.
When looking up text, you’ll almost always want an exact match. However, the “Less than” or “Greater than” options can be useful when looking up numbers for things like tax brackets or commission rates.
The Result
The MATCH function returns 4 because it finds the lookup value in the 4th row of the lookup_array (A2:A8).
Important: this is *not* the row number of the spreadsheet itself. The row number returned by MATCH is relative to the lookup_array.
Now that you have a basic grasp of MATCH, let’s dive into INDEX.
INDEX: Your Spreadsheet Roadmap
The INDEX function acts like a roadmap for your spreadsheet. It returns the value of a cell within a range, based on the row and/or column number you provide.
The INDEX function has three arguments:
*The third argument [column_num] is optional and not needed for the VLOOKUP replacement formula.*
Let’s revisit our Starbucks menu and use INDEX to answer the question: “What is the price of a Grande Caffe Mocha?”
1. array – The “Where”
This argument tells INDEX where to look in the spreadsheet. We’ve specified $C:$2:$C$8 because this range represents the column of prices we want to return a value from.
*Again, it’s good practice to use an absolute reference so you can copy the formula without the range shifting.*
2. row_num – The “Row Number”
Next, we specify the row number of the value we want to return within the array. Remember, this is the row number *within the array*, not the row number of the sheet.
For now, let’s hardcode this and type a 4 into the formula.
The Result
The result is $3.95, the value in the 4th cell of the array.
Important: The number formatting from the array range isn’t automatically applied to the cell containing the formula. If INDEX returns a “4” instead of “$3.95”, apply a number format with decimal places to the formula’s cell.
INDEX is fairly straightforward on its own. Now, let’s combine it with MATCH.
INDEX + MATCH = Excel Power
By combining the INDEX and MATCH functions, we create a flexible and robust alternative to VLOOKUP.
The key is to use the MATCH function to dynamically determine the row_num argument within the INDEX function.
Earlier, we hardcoded the row_num argument as “4”. Now, we’ll replace that with our MATCH formula.
The MATCH function provides the row_num value to the INDEX function. INDEX then returns the value from that cell in the array.
The result: $3.95, the price of a Grande Caffe Mocha.
Here’s a quick guide to help you build the formula:
Again, think of MATCH as finding the row number for INDEX. INDEX then uses that row number to retrieve the corresponding value from a different column.
VLOOKUP vs. INDEX MATCH: The Showdown
Could VLOOKUP accomplish the same thing in our example? Yes. However, the key advantage of INDEX MATCH is that it’s less fragile when your spreadsheet is modified.
Handling Column Insertions and Deletions
Imagine we add a new cup size to our coffee menu, inserting a column between Tall and Grande. A VLOOKUP formula would likely return the wrong result because the column index number would now be incorrect.
You *can* use the MATCH function with VLOOKUP to mitigate some of these errors. However, VLOOKUP still can’t handle lookups to the left.
Looking to the Left, Effortlessly
With INDEX MATCH, the lookup column can be to the right of the return column, and the formula will still work perfectly.
In fact, you often don’t even need to rewrite the formula if you rearrange your columns.
Matching on Both Rows and Columns
You can even use two MATCH functions within INDEX to perform lookups based on both row and column criteria.
In this scenario, the return range spans multiple rows and columns (e.g., C4:E8).
Because MATCH can handle both vertical and horizontal lookups, you can use it twice inside INDEX to perform a two-way lookup, matching both item name and size.
Imagine adding dropdowns using data validation for both “Item” and “Size” to create an interactive price calculator. When the user selects the item and size, the INDEX MATCH MATCH formula automatically performs the lookups and returns the correct price.
The #REF! Error: A Common INDEX MATCH Pitfall
One of the most common errors you’ll encounter when combining INDEX and MATCH is the #REF! error.
This usually happens when the return range in INDEX is a different size than the lookup range in MATCH. If the specified criteria can’t be found due to this misalignment, the formula will return #REF!
To fix it, simply expand the smaller range to match the larger one. For instance, change the INDEX range to end at cell D8 instead of D7.
Just like VLOOKUP, INDEX MATCH will also return the #N/A error when a value isn’t found.
Use Excel Tables to Minimize Errors
While the example above is easy to spot and fix because of the small dataset, a mismatch can occur more often when working with larger data. Referencing Tables instead of ranges can alleviate that. Excel Tables can automatically expand to include new rows and columns, and prevent blank cells from causing errors.