I’ve Used Excel for Years—And This Trick Still Blew My Mind

Excel, the ubiquitous spreadsheet software, has been a staple in offices and homes for decades. Many of us consider ourselves proficient users, navigating formulas, charts, and pivot tables with relative ease. However, even seasoned Excel veterans can occasionally stumble upon a hidden gem, a powerful function or technique that dramatically streamlines workflows and unlocks new analytical capabilities. For us, that recent revelation involved the often-overlooked combination of INDEX and MATCH functions, working together to create dynamic and flexible lookups that surpass the limitations of the more commonly used VLOOKUP and HLOOKUP.

Beyond VLOOKUP: Discovering the Power of INDEX and MATCH

For years, VLOOKUP (Vertical Lookup) has been the go-to function for retrieving data from tables. It’s a simple concept: you provide a lookup value, specify a table range, indicate the column number containing the desired result, and choose whether to perform an exact or approximate match. HLOOKUP (Horizontal Lookup) performs the same function but searches horizontally across the first row of a table.

However, VLOOKUP has inherent limitations. Primarily, it requires the lookup column to be the leftmost column in the table array. This can be restrictive, forcing you to rearrange your data or create auxiliary columns. Furthermore, if you insert or delete a column within the table array, you must manually update the column index number in the VLOOKUP formula, making it prone to errors.

The INDEX and MATCH functions, when used together, provide a more robust and flexible alternative. Instead of relying on a fixed column number, INDEX and MATCH dynamically determine the position of the lookup column and the result column, making your formulas more resilient to changes in your data.

Understanding the Individual Functions: INDEX

The INDEX function returns the value of a cell in a table or range, given its row and column number. It has two primary forms:

Array Form: INDEX(array, row_num, [column_num]) – Returns the value at the intersection of a specified row and column in a given array.
Reference Form: INDEX(reference, row_num, [column_num], [area_num]) – Returns a reference to a cell at the intersection of a specified row and column in a range of cells. This form is useful for working with non-contiguous ranges.

For the purposes of this article, we’ll focus on the Array Form of the INDEX function, as it’s the most commonly used in conjunction with MATCH. For example, INDEX(A1:C10, 5, 2) would return the value in the cell at the 5th row and 2nd column of the range A1:C10.

Understanding the Individual Functions: MATCH

The MATCH function searches for a specified value in a range of cells and returns the relative position of that value within the range. Its syntax is:

MATCH(lookup_value, lookup_array, [match_type])

lookup_value: The value you want to find.
lookup_array: The range of cells you want to search.
match_type: An optional argument that specifies how MATCH finds the lookup_value.
- 0: Finds the first value that is exactly equal to lookup_value. This is the most common and recommended setting for precise lookups.
- 1: Finds the largest value that is less than or equal to lookup_value. The lookup_array must be sorted in ascending order.
- -1: Finds the smallest value that is greater than or equal to lookup_value. The lookup_array must be sorted in descending order.

For example, MATCH("Apple", A1:A10, 0) would return the position of “Apple” in the range A1:A10. If “Apple” is in cell A3, the function would return 3.

The Dynamic Duo: Combining INDEX and MATCH for Superior Lookups

The true power of INDEX and MATCH lies in their combined use. By using MATCH to dynamically determine the row and column numbers for the INDEX function, we create a lookup formula that is both flexible and robust.

The general formula structure is as follows:

INDEX(result_array, MATCH(lookup_value, lookup_array, 0), MATCH(column_header, header_array, 0))

Let’s break this down:

result_array: The range of cells containing the values you want to retrieve.
MATCH(lookup_value, lookup_array, 0): This part finds the row number of the lookup value within the lookup array. The lookup_value is what you are searching for, the lookup_array is the range where you expect to find it, and 0 specifies an exact match.
MATCH(column_header, header_array, 0): This optional part finds the column number of the column containing your desired result. The column_header is the name of the column you’re interested in, the header_array is the range containing the column headers, and 0 again specifies an exact match. This allows you to select which column to pull data from. If you want to return a value from a single column, you can omit this MATCH function and provide the column number directly.

Example Scenario: Employee Data Lookup

Imagine we have a table of employee data with the following columns: Employee ID, Name, Department, Salary, and Start Date. This data is in the range A1:E100.

Let’s say we want to retrieve the Salary of an employee given their Employee ID. Instead of using VLOOKUP, which would require the Employee ID to be the first column, we can use INDEX and MATCH.

The formula would be:

=INDEX(C2:E100, MATCH(H2, A2:A100, 0), MATCH(H1, C1:E1, 0))

Where:

A2:A100 contains the Employee IDs.
C2:E100 contains the data we want to retrieve (including Salary).
C1:E1 contains the column headers (Department, Salary, Start Date).
H2 contains the Employee ID we want to look up.
H1 contains the column header we want to retrieve - in this case, “Salary”.

This formula first uses MATCH to find the row number corresponding to the Employee ID in cell H2. Then, it uses the second MATCH to find the column containing “Salary”. Finally, it uses INDEX to retrieve the value at the intersection of that row and column within the data range.

Advantages of INDEX and MATCH Over VLOOKUP and HLOOKUP

The benefits of using INDEX and MATCH become even clearer when compared to VLOOKUP:

Flexibility: INDEX and MATCH are not restricted by the position of the lookup column. You can search for a value in any column and retrieve data from any other column, regardless of their relative positions.
Resilience: Adding or deleting columns does not break the INDEX and MATCH formula. Unlike VLOOKUP, you don’t need to manually update column index numbers. The MATCH function dynamically adjusts to changes in the table structure.
Clarity: Some find INDEX and MATCH easier to understand and maintain, as the logic is more explicit. The formula clearly separates the lookup value, lookup array, and result array.
Performance: In some cases, INDEX and MATCH can be faster than VLOOKUP, especially with large datasets. This is because MATCH only searches the specific lookup array, while VLOOKUP might iterate through the entire table array.
Two-way Lookup: You can use the flexibility of INDEX and MATCH to perform lookups in both dimensions simultaneously. Say you have employees listed down the side and dates across the top, and the values are sales figures. You can use MATCH to find the correct employee and another MATCH to find the correct date, and then INDEX to get the corresponding sales figure. VLOOKUP cannot do this without restructuring the data.

Advanced Techniques: Expanding the Capabilities of INDEX and MATCH

Beyond basic lookups, INDEX and MATCH can be combined with other Excel functions to perform more complex tasks:

Handling Errors with IFERROR

If the lookup value is not found in the lookup array, the MATCH function will return an error (#N/A). To handle these errors gracefully, you can wrap the INDEX and MATCH formula with the IFERROR function.

The syntax of IFERROR is:

IFERROR(value, value_if_error)

For example:

=IFERROR(INDEX(C2:E100, MATCH(H2, A2:A100, 0), MATCH(H1, C1:E1, 0)), "Employee ID Not Found")

This formula will return “Employee ID Not Found” if the Employee ID in cell H2 is not found in the range A2:A100.

Performing Case-Sensitive Lookups

By default, Excel lookups are case-insensitive. If you need to perform a case-sensitive lookup, you can combine INDEX and MATCH with the EXACT function and an array formula.

The EXACT function compares two text strings and returns TRUE if they are exactly the same (including case) and FALSE otherwise.

To perform a case-sensitive lookup, enter this as an array formula (press Ctrl+Shift+Enter):

=INDEX(result_array, MATCH(TRUE, EXACT(lookup_array, lookup_value), 0))

For example, let’s say you want to find the salary of an employee, but you need to ensure the Employee ID matches the case exactly.

=INDEX(C2:E100, MATCH(TRUE, EXACT(A2:A100, H2), 0), MATCH(H1, C1:E1, 0))

Remember to enter this as an array formula by pressing Ctrl+Shift+Enter.

Returning Multiple Values

INDEX and MATCH can be used to return multiple values based on a single lookup. For instance, to retrieve the department, salary, and start date for a given employee ID into adjacent cells, you can adjust the column lookups in the formula. If you’re always retrieving adjacent columns, this can be simplified with the OFFSET function as well.

Dynamic Ranges with Named Ranges and Tables

To make your INDEX and MATCH formulas even more dynamic, you can use named ranges or Excel tables. Named ranges allow you to assign a name to a range of cells, making your formulas more readable and easier to maintain. Excel tables automatically adjust their size as you add or remove data, ensuring that your formulas always reference the correct range. By using Tables, you automatically get the benefits of using Structured References too. This means you can refer to the columns within the tables by their headers!

Real-World Applications: Unleashing the Potential

The versatility of INDEX and MATCH makes them invaluable in a wide range of applications:

Financial Modeling: Retrieving financial data from various sources and performing calculations.
Inventory Management: Tracking inventory levels and identifying items that need to be reordered.
Project Management: Assigning tasks to team members and tracking project progress.
Sales Analysis: Analyzing sales data and identifying top-performing products or regions.
Human Resources: Managing employee data, tracking performance, and generating reports.
Data Validation: Ensuring data integrity by validating entries against a lookup table.

For instance, consider a scenario where you manage a product catalog. With INDEX and MATCH, you can effortlessly retrieve product details such as price, description, and supplier based on the product ID. This eliminates the need for manual searching and ensures data accuracy. You can even create a dropdown list of product IDs using Data Validation, and then use INDEX and MATCH to automatically populate the corresponding product details in other cells.

Conclusion: Embrace the Power of INDEX and MATCH

While VLOOKUP may be familiar and easy to grasp, the combination of INDEX and MATCH offers a more powerful, flexible, and robust solution for data lookups in Excel. By understanding the individual functions and how they work together, you can unlock new analytical capabilities and streamline your workflows. From handling errors to performing case-sensitive lookups, INDEX and MATCH empowers you to tackle complex data manipulation tasks with ease. So, ditch the limitations of VLOOKUP and embrace the dynamic duo of INDEX and MATCH – you might be surprised at what you’ve been missing! It is a powerful and useful trick that we encourage you to explore.

You also may like 〣〣