How to Efficiently Perform Excel Lookups with INDEX MATCH?

Introduction

Navigating the world of data analysis can be a daunting task, but with the right tools and techniques, you can unlock a whole new level of efficiency and insights. One such powerful tool in the Excel arsenal is the INDEX MATCH formula, a dynamic duo that can revolutionize the way you work with data. In this comprehensive guide, we'll explore the intricacies of INDEX MATCH, uncover its hidden potential, and equip you with the knowledge to tackle even the most complex data challenges.

INDEX MATCH - what is it and how to write the formula

At its core, the INDEX MATCH formula is a two-step process that combines the power of the INDEX and MATCH functions. The INDEX function allows you to retrieve a value from a specified range or array, while the MATCH function helps you locate the position of a given value within a range. By combining these two functions, you can create a flexible and robust lookup formula that can handle a wide variety of data structures and scenarios.

The syntax for the INDEX MATCH formula is as follows:

=INDEX(array, MATCH(lookup_value, lookup_array, [match_type]))

Here's a simple example to get you started:

Imagine you have a table of employee data, with names in one column and their corresponding start dates in another. You want to find the name of the employee who joined on a specific date. Using the INDEX MATCH formula, you can write the following:

=INDEX(names_column, MATCH(20-Nov-18, start_dates_column, 0))

The MATCH function first locates the position of the date "20-Nov-18" in the start_dates_column, and the INDEX function then retrieves the corresponding name from the names_column. This powerful combination allows you to quickly and accurately retrieve the desired information, even in complex data structures.

What to do when there is an error

One of the common challenges with lookup formulas is dealing with errors, such as the dreaded "#N/A" error. When a lookup value is not found in the data, the INDEX MATCH formula will return an error. To handle this gracefully, you can wrap the formula in an IFERROR function, which will display a custom message or alternative value in case of an error.

Here's an example:

=IFERROR(INDEX(names_column, MATCH(88000, salaries_column, 0)), "Not Found")

In this case, if the salary of 88,000 is not found in the salaries_column, the formula will return the text "Not Found" instead of the error message.

Combine two tables with INDEX MATCH functions

The true power of the INDEX MATCH formula shines when you need to combine data from multiple sources. Imagine you have an employee table with basic information, and a separate table with department-level bonus information. Using INDEX MATCH, you can seamlessly link the two tables and populate the bonus data for each employee.

The formula would look something like this:

=INDEX(bonus_column, MATCH(department, departments_column, 0))

This formula first matches the department of the current employee with the departments_column, and then uses the resulting position to retrieve the corresponding bonus value from the bonus_column.

Stacked data with INDEX MATCH

Sometimes, your data may not be neatly organized in a table format, but rather in a stacked or unstructured layout. The INDEX MATCH formula can still come to the rescue in these scenarios. For example, imagine you have a list of names and their corresponding email addresses, but they are in separate cells going down the column.

To retrieve the email address for a given name, you can use the following formula:

=INDEX(email_column, MATCH(name, name_column, 0) + 1)

The MATCH function first locates the position of the name in the name_column, and the INDEX function then retrieves the value from the next cell, which corresponds to the email address.

Multiple conditions (criteria) with INDEX MATCH

One of the most powerful applications of the INDEX MATCH formula is its ability to handle multiple conditions or criteria. Imagine you have a bonus structure that varies based on both the department and the employee's gender. Using a combination of INDEX, MATCH, and the logical AND operator, you can create a formula that accurately retrieves the correct bonus percentage.

The formula would look like this:

=INDEX(bonus_percentages, MATCH(1, (department=D5)*(gender=E5), 0))

In this formula, the MATCH function first checks for the department and gender conditions, and the INDEX function then retrieves the corresponding bonus percentage from the bonus_percentages range.

INDEX MATCH MATCH function for two-way lookup

When you need to perform a two-way lookup, where you need to match values in both rows and columns, the INDEX MATCH MATCH formula comes into play. Imagine you have a matrix of bonus percentages, with departments in the rows and genders in the columns. To retrieve the bonus percentage for a specific department and gender, you can use the following formula:

=INDEX(bonus_matrix, MATCH(department, departments_range, 0), MATCH(gender, genders_range, 0))

The first MATCH function locates the row position based on the department, and the second MATCH function locates the column position based on the gender. The INDEX function then retrieves the value at the intersection of the row and column.

Alternative to INDEX MATCH (XLOOKUP)

While the INDEX MATCH formula is a powerful and versatile tool, Excel has introduced a newer function called XLOOKUP, which can provide a simpler alternative in certain scenarios. XLOOKUP combines the functionality of VLOOKUP, HLOOKUP, and INDEX MATCH into a single function, with a more intuitive syntax.

Here's an example of how you can use XLOOKUP to achieve the same result as the earlier INDEX MATCH example:

=XLOOKUP(20-Nov-18, start_dates_column, names_column)

The XLOOKUP function takes the lookup value, the lookup range, and the return range, making it a more straightforward option for some lookup tasks.