Flexible Lookups with INDEX-MATCH

Why Not Just VLOOKUP?

Before we dive into the mechanics of INDEX-MATCH, it's important to understand why it's so highly regarded. VLOOKUP is popular, but it has two major limitations:

1. It can only look to the right: The value you want to retrieve must be in a column to the right of the column you are searching in. This forces you to structure your data in a specific way.
2. It is "brittle": VLOOKUP relies on a fixed column number. If you insert or delete a column in your table, the formula breaks because the column reference is no longer correct.

INDEX-MATCH solves both of these problems. It can look up values in any direction, and it is not affected by the insertion or deletion of columns. The image below shows this perfectly. When a new column ("Subject") is added, the INDEX-MATCH formula correctly returns the "Marks," while the VLOOKUP formula breaks and incorrectly returns the "Subject."

Deconstructing the Formula: INDEX and MATCH

The power of INDEX-MATCH comes from combining two separate functions, each with a distinct job. Let's understand them individually first. The following video from Leila Gharani provides an excellent conceptual overview.

To solidify these concepts, let's review them:

• INDEX(array, row_num, [column_num])

  • array: The range of cells where your answer might be (the "map").
  • row_num: Which row to go down to.
  • [column_num]: Which column to move across (optional).

• MATCH(lookup_value, lookup_array, [match_type])

  • lookup_value: What you are looking for.
  • lookup_array: The single row or column where you are looking for it.
  • [match_type]: How to match. You will almost always use 0 for an exact match.

The key insight is that MATCH's output (a number) is the perfect input for INDEX's row_num or column_num argument.

Performing a Basic One-Way Lookup

Now, let's combine them. The basic structure for looking up a value is:

=INDEX(column_to_return_value_from, MATCH(what_you_are_looking_for, column_to_search_in, 0))

This structure elegantly solves VLOOKUP's problems. Because you specify the "return" and "search" columns separately, it doesn't matter if the return column is to the left or right of the search column.

The following video provides a very practical, step-by-step walkthrough of creating a basic lookup.

For a textual reference that breaks this down clearly, the following article is an excellent resource.

Performing a Two-Way (Matrix) Lookup

The real power of INDEX-MATCH shines when you need to find a value at the intersection of a specific row and a specific column. For example, retrieving sales for a given product in a particular month from a sales grid.

To do this, we use the column_num argument in the INDEX function and populate it with a second MATCH function. The structure becomes:

=INDEX(the_entire_table_of_values, MATCH(row_lookup_value, column_of_row_headers, 0), MATCH(column_lookup_value, row_of_column_headers, 0))

Let's break that down:

1. INDEX(the_entire_table_of_values, ...): The "map" is now the whole grid of data, not just a single column.
2. MATCH(row_lookup_value, ...): The first MATCH finds the correct row number, just like before.
3. MATCH(column_lookup_value, ...): The second MATCH finds the correct column number.

The image below shows a complete two-way lookup formula in action. The formula in cell G4 finds the value $180 by matching "May" in the rows and "Green" in the columns.

The video from Kenji Explains demonstrates this "Level 2" scenario perfectly.

Common Issues and Next Steps

As you use INDEX-MATCH, the most common error you'll encounter is #N/A. This simply means the MATCH function couldn't find your lookup_value in the lookup_array. This is usually caused by:

• A typo in your lookup value.
• Extra spaces (which you can fix with the TRIM function we learned about!).
• Mismatched data types (e.g., looking up a number that is stored as text).

It's also worth knowing that in the newest versions of Excel (Microsoft 365/Office 2021), there is a new function called XLOOKUP which simplifies this process. However, INDEX-MATCH is a critical skill because it is compatible with all versions of Excel you are likely to encounter in a business environment.

Conclusion

In this lesson, you've unlocked one of Excel's most versatile and essential lookup methods. You've moved beyond simple aggregations and can now retrieve specific data points from anywhere in your dataset with a robust and flexible formula.

Here are the key takeaways:

• INDEX-MATCH is a powerful combination that overcomes the limitations of VLOOKUP, allowing for lookups in any direction and remaining stable when columns are added or removed.
• INDEX retrieves a value from a specific coordinate (row and column) within a given range (the map).
• MATCH finds the numeric position of an item in a list, providing the coordinates for INDEX.
• You can create a one-way lookup with one MATCH function for the row number, or a two-way (matrix) lookup by using a second MATCH for the column number.

In our next lesson, we will explore conditional logic using IF and IFS functions. These functions will allow you to build formulas that perform different calculations or return different values based on whether certain conditions are met, adding another layer of dynamic decision-making to your analytical models.