You've likely spent a good amount of time with VLOOKUP, and for good reason. It's a workhorse for pulling data from one table into another. But its limitations become clear quickly. It can't look to the left, it breaks if you insert a column, and handling errors requires wrapping it in another function.

Modern Excel offers more powerful and flexible tools. We'll focus on two: XLOOKUP, the versatile successor to VLOOKUP and HLOOKUP, and the classic INDEX & MATCH combination, which remains essential for compatibility and certain advanced scenarios.

Microsoft introduced XLOOKUP in 2019 to address the shortcomings of older lookup functions. It's a single, powerful function that can search vertically or horizontally, look in any direction, and handle errors gracefully. Its syntax is also more intuitive because you select the lookup range and the results range separately.

Let's break down the three required arguments:

• lookup_value: The item you're looking for.
• lookup_array: The column or row where you expect to find the item.
• return_array: The column or row containing the corresponding value you want to get back.

This structure immediately solves a major VLOOKUP problem. Because you specify the exact return_array, inserting new columns in your source data won't break the formula.

Imagine you have this simple product table and want to find the price for "Pencil".

The formula would be:

Excel looks for "Pencil" in the Product column (B2:B4) and returns the corresponding value from the Price column (C2:C4), which is $0.50. Simple and readable.

The real power of XLOOKUP comes from its optional arguments. The fourth argument, if_not_found, lets you specify a custom value or message if the lookup fails. This is much cleaner than the old IFERROR(VLOOKUP(...)) pattern.

This formula will return the text "Product not found" instead of an #N/A! error.

The fifth argument, match_mode, controls how Excel finds a match. The default is 0 for an exact match. But you can also find the next smaller item (-1) or the next larger item (1), which is useful for finding tax brackets or grade scales. You can also use 2 for a wildcard match, where characters like * (any sequence of characters) and ? (any single character) can be used in your lookup_value.

Before XLOOKUP, the combination of INDEX and MATCH was the gold standard for flexible lookups. It's still crucial to know, especially when working on spreadsheets that need to be compatible with older versions of Excel (2019 and earlier).

The logic is to use two separate functions:

1. MATCH: This function finds the position of a value within a range. For example, MATCH("Pencil", B2:B4, 0) would return 2, because "Pencil" is the second item in that range.
2. INDEX: This function returns a value from a range based on its position. For example, INDEX(C2:C4, 2) would return $0.50, the second item in the price list.

When you combine them, you use MATCH to find the row number dynamically and feed it into INDEX.

Using our product table, the formula to find the price of a pencil would be:

MATCH finds that "Pencil" is in position 2, and INDEX retrieves the value from position 2 of the Price column. Just like XLOOKUP, this combination can look left and isn't affected by inserting new columns, making it far more robust than VLOOKUP.

Time to test your knowledge on these powerful functions.

With these functions, you can build faster, more reliable, and more flexible spreadsheets.