Skip to Content

How to Use INDEX MATCH to VLOOKUP to the Left in Excel

Posted on Last updated:

Before we begin, make sure to download the left lookup example file below:

Download File

If you’ve ever used Excel’s VLOOKUP function, you’ve probably run into one of its biggest limitations—it can’t look to the left. That means if the data you need is in a column to the left of your lookup column, VLOOKUP won’t help.

Fortunately, there’s a smarter way: using a combination of Excel’s INDEX and MATCH functions. In this guide, you’ll learn how to overcome this limitation and perform left lookups in Excel with ease—even better than VLOOKUP.

In this tutorial, you’ll learn how to:

  • Use the MATCH function to find the position of a lookup value in a range.
  • Use the INDEX function to retrieve data from a specific position in a table.
  • Combine INDEX and MATCH to perform left lookups in Excel—something VLOOKUP can’t do.

Let’s start by understanding how the MATCH function works.

Understanding the MATCH Function in Excel

The MATCH function in Excel helps you find the position of a value in a list. It doesn’t give you the actual value—it just tells you where it is.

Think of it like this: if you’re looking for someone’s name in a guest list, MATCH tells you which number on the list their name is.

It’s especially useful when combined with other functions like INDEX.

This function goes with three arguments outlined below:

MATCH Function Syntax:

MATCH(lookup_value, lookup_array, [match_type])

Let’s break this down in simple terms:

  • lookup_value – This is what you’re searching for (e.g. a name or ID).
  • lookup_array – This is where you’re searching (the list or range).
  • match_type(optional) – This tells Excel how to match:
    • Use 0 if you want an exact match – this is the most common and safest option.
    • 1 and -1 are for advanced use with sorted data and are rarely needed.

⚠️ Tip: Always use 0 unless you’re working with sorted data. If you skip the match_type, Excel assumes it’s 1, which may give incorrect results.

Example of MATCH() function

The above screenshot is a table that contains customer order information. There are about 10 records in this table.

To illustrate the MATCH() function, let’s say you have a list of customer names, and you want to find where Jacob Sophia is in the list.

Here’s the formula you’d use: =MATCH(“Jacob Sophia”, A2:A11, 0)

Or

This tells Excel:

“Look for Jacob Sophia in the list, and tell me the position where it appears.”

If Jacob Sophia is the 6th name on the list, Excel returns 6.

See the screenshot below:

⚠️ What If It Doesn’t Find the Name?

If the name you’re searching for isn’t in the list, Excel will show #N/A, which means “Not Available”—the value doesn’t exist in that range.

To grasp how this formula works, you’ll have to try it yourself. Also, check this step-by-step guide on how to use the MATCH function.

Understanding the INDEX Function in Excel

As you can see, the MATCH() function only does half of the work of VLOOKUP by returning only the position of the search term. It can’t even retrieve the actual content of the cell.

It is clear that the MATCH() function isn’t so useful on its own. It becomes useful only when you combine it with another function (like the INDEX function)

While the MATCH function tells you the position of something in a list, the INDEX function takes that position and gives you the actual value from a table.

Think of it like this: MATCH says, “Jacob Sophia is number 6 in the list,” and INDEX says, “Okay, what’s in the 6th row of this column? Let me return that value.”

This function has the following arguments:

INDEX Function Syntax:

INDEX(array, row_num, [column_num])

Here’s what each part means:

  • array – The full range of cells you’re working with (like a table).
  • row_num – The row number inside that range you want to pull from.
  • column_num (optional) – The column number inside the range you want the value from.

INDEX() function example

Using the table above, let’s illustrate how the index function works.

Assuming the above table is sorted in a way that the first name in the list is our first customer, the second name is our second customer, and so on, up to the end.

Using the index function, we’ll find and retrieve the product our 5th customer bought. So, consider the following formula:

=INDEX(B5:F14,5,3)

This formula gives you the content of the cell in the 5th row of the 3rd column, which is cell D9.

But how?

The first argument (B5:F14) specifies the range of cells we are interested in. You can also use a named range for this argument, like CustomerTable.

The second argument also finds the row we want, and that is the 5th row.

Finally, the third argument represents the index number of the product column which is 3.

Altogether, the formula retrieves the 5th value in the 3rd column of the table.

See screenshot:

📝 Tip: You don’t always need to hard-code the number 5. Instead, you can combine MATCH and INDEX, which we’ll explore next, to perform the powerful left lookup.

Excel “Left lookup” using INDEX-MATCH

These two functions (the MATCH and INDEX functions) are often used in each other’s company to perform lookups.

On their own, they are almost useless. But when they are used together, they have the ability to overcome the various VLOOKUP limitations.

Example of Left lookup

Consider the following example:

In the example below, using the Customer column, the formula retrieves information from the Date column which is found at the left side of the lookup column (Customer). This is a clear example of VLOOKUP to the left (or left lookup), something the VLOOKUP() function cannot handle.

This worksheet lets you enter a customer by name and then retrieve the corresponding date on which the customer made a purchase. Since the Date column is to the left of the Customer column (or lookup column), VLOOKUP can’t perform this task. The solution is to use the MATCH INDEX technique.

Left lookup breakdown

Considering the above example, to lookup to the left, first find the position of the product you are looking for using the MATCH() function:

=MATCH(D2,F5:F14,0)

If you run this formula alone, you’ll get a value of 7 because the search term (William Emma) is located in the 7th row of the table.

Now, you can get the position of any lookup value, thanks to the MATCH() function.

With this information, you can also retrieve any corresponding cell content by employing the INDEX() function.

All you have to do is use the MATCH() function as the second argument (row_num) in the INDEX() function, then specify the column from which you want to retrieve the corresponding value. And bam! Your formula can now look to the left.

Here’s how you’d merge these functions into a single formula:

=INDEX(CustomerTable,MATCH(D2,F5:F14,0),1)

These two functions, when combined together, can be a very powerful tool for advanced Excel users. Apart from using them for the left lookup (or VLOOKUP to the left), you can also use them to perform a double lookup.

🟢 Conclusion

The combination of INDEX and MATCH is a powerful alternative to VLOOKUP—especially when you need to look up values to the left of your search column in Excel. While VLOOKUP has its place, it’s limited when it comes to flexibility.

With INDEX and MATCH, you not only overcome those limitations but also gain better control over your lookups. Whether you’re working with large datasets or just trying to extract data more efficiently, this method will serve you well.

💡 Tip: Once you get comfortable with INDEX and MATCH, you’ll find yourself using them more than VLOOKUP.