A simple IF function is great for handling one condition, but real-world data often has layers of complexity. What if a sales commission isn't a single rate but changes based on performance tiers? You could stack IF functions inside one another, creating a nested formula.

This works, but it can get confusing to read and debug. Each nested IF adds another layer of parentheses and logical tests. For a cleaner approach, Excel introduced the IFS function. It lets you list pairs of conditions and results in a more straightforward sequence.

Notice how the IFS formula tests each condition in order. The first one that evaluates to TRUE returns the corresponding value, and the function stops. This makes complex logical gates much easier to manage. You can also combine conditions within a single test using AND and OR. For instance, to qualify for a bonus, a salesperson might need to exceed $50,000 in sales AND have a customer satisfaction score above 90%.

You’re likely familiar with VLOOKUP, the classic tool for finding data in a table. While revolutionary in its day, it has limitations. It can only look to the right of the lookup column, and its default

The XLOOKUP function is the modern, powerful successor. It's more intuitive and flexible. By default, it performs an exact match, which is safer. It can also look in any direction (left, right, up, or down) and return entire rows or columns.

Here’s the basic syntax:

The real power of XLOOKUP shines when handling multiple criteria. While you can't add multiple lookup arrays directly, you can use a clever trick with boolean logic. Let's say you need to find the price for a specific product (B2) from a specific supplier (C2).

We then ask XLOOKUP to find the value 1 in that resulting array. This pinpoints the exact row where both of our criteria are met. This method is far more efficient and readable than concatenating values in a helper column, a common workaround with VLOOKUP. Performance-wise, XLOOKUP also outperforms VLOOKUP, especially on large, unsorted datasets, because its underlying search algorithms are more optimized.

Even with XLOOKUP, the combination of INDEX and MATCH remains an essential skill, especially if you work with older Excel versions or need to perform two-way lookups. These two functions work together perfectly:

• MATCH(lookup_value, lookup_array, 0) finds the position (e.g., the 5th item) of a value in a list.
• INDEX(array, row_num, [column_num]) returns the value from a specific position in an array.

When combined, they create a fully dynamic lookup. To find a price based on a product name, you'd use MATCH to find the product's row number and feed that into INDEX.

The true magic happens when you need to look up both the row and the column dynamically. This is a common task in financial modeling, where you might need to pull a value for a specific account (row) in a specific month (column).

By using MATCH for both the row and column arguments of INDEX, you create a lookup that can find any value in a matrix based on its headers.

Finally, robust formulas anticipate errors. Instead of letting your sheet display #N/A when a lookup fails, wrap your formula in IFERROR or IFNA. IFNA is more specific, catching only #N/A errors, while IFERROR catches all error types.

This provides a clean, user-friendly output if the lookup value doesn't exist, making your spreadsheets more professional and reliable.