Skip to main content
Back to path

Welcome to a Grasp lesson

Create a free account to keep studying and create your own study path.

Lesson 12

XLOOKUP: Modern Two-Way Lookups

Recommended resources

Welcome to our next session. In the previous lesson, you mastered the powerful but somewhat complex INDEX and MATCH combination, learning how to build flexible lookups that overcome the limitations of VLOOKUP. While INDEX(MATCH) is an essential technique for any advanced Excel user, modern versions of Excel offer an even better solution.

Today, we will explore XLOOKUP, a single function that combines the power of INDEX(MATCH) with a more intuitive syntax. Our goal is to understand its basic use and then leverage it to perform robust two-way lookups, which find data at the intersection of a specific row and column. This will effectively replace the INDEX(MATCH, MATCH) structure you learned previously with a more modern and readable formula.

From Two Functions to One: Introducing XLOOKUP

XLOOKUP was designed by Microsoft to be the successor to both VLOOKUP and HLOOKUP. It simplifies the process of data retrieval by providing a single, versatile function that can look vertically or horizontally, search in any direction (left, right, up, or down), and handle errors gracefully without needing other functions.

Let's begin by understanding the function's syntax and its arguments.

XLOOKUP function in Excel with formula examples - Ablebits.com

This section from an article by Ablebits clearly breaks down the syntax of the XLOOKUP function.

Please read the section Excel XLOOKUP function - syntax and uses. As you read, notice that the first three arguments (lookup_value, lookup_array, return_array) are required and are very similar to the components of the INDEX(MATCH) formula you already know. The optional arguments give XLOOKUP its extra power.

The key advantages of XLOOKUP over its predecessors are:

  • Simplicity: It combines finding an item and returning a corresponding value in one function.
  • Flexibility: It can look to the left or right with no extra effort.
  • Robustness: Inserting or deleting columns won't break the formula, as it refers to ranges, not a fixed column index number.
  • Default Exact Match: Unlike VLOOKUP, XLOOKUP defaults to an exact match, which is what you need most of the time. This makes it safer to use.

Now, let's watch a short tutorial that walks through a basic XLOOKUP formula and highlights an essential practical skill: locking cell references.

How to Use XLOOKUP in Excel – Step-by-Step Tutorial for Beginners

This video from Kevin Stratvert provides a clear, step-by-step introduction to using XLOOKUP for a simple vertical lookup.

First, watch the basic demonstration from the beginning. See how the first three arguments are used to find a client's order amount. Then, watch the segment from 4:09 to 6:11. This part is crucial as it explains why and how to lock the lookup_array and return_array with the F4 key to prevent errors when copying the formula down.

Two-Way Lookups with Nested XLOOKUP

Just as you used INDEX(MATCH, MATCH) to look up data in a grid, you can achieve the same result with XLOOKUP. The technique involves nesting one XLOOKUP function inside another.

The logic is as follows:
=XLOOKUP(row_value, row_headers, XLOOKUP(column_value, column_headers, entire_data_table))

  1. The inner XLOOKUP runs first. It looks for the column value (e.g., "Physics") in the header row and returns the entire corresponding column of data from the table.
  2. The outer XLOOKUP then takes over. It looks for the row value (e.g., "Greg") in the row headers. For its return_array, it uses the dynamic array returned by the inner XLOOKUP.

This might sound complex, but seeing it in action makes it clear.

How to Use the NEW & IMPROVED Excel XLOOKUP (with 5 Examples)

This video by Leila Gharani provides an excellent demonstration of building a two-way lookup from scratch using nested XLOOKUPs.

Please watch the segment from this section. She first sets up dropdowns to make the lookup dynamic, then builds the nested XLOOKUP formula step-by-step. Notice how the inner XLOOKUP resolves to an entire column of data, which then becomes the return_array for the outer XLOOKUP.

The resulting formula is often more readable than its INDEX(MATCH, MATCH) counterpart. Here is a static example that clearly shows the formula in the formula bar, looking up Greg's Math score.

This image shows a nested XLOOKUP formula. The inner XLOOKUP finds the 'Math' column, and the outer XLOOKUP finds 'Greg's row within that column to return the score of 21.

Building Robust Lookups

The learning outcome calls for creating "modern and robust" lookups. A key part of robustness is handling situations where a lookup value isn't found. With VLOOKUP or INDEX(MATCH), this requires wrapping the formula in another function like IFERROR or IFNA.

XLOOKUP has this capability built-in with its optional fourth argument, [if_not_found].

This becomes even more powerful in a two-way lookup, as you can specify different error messages for the row lookup versus the column lookup.

2-Way XLOOKUP in Excel: Beginner to Advanced

This article from Excel Campus explains the structure of a 2-way XLOOKUP and then demonstrates a clever way to handle errors for both dimensions of the lookup.

First, quickly review the section Basic 2-Way XLOOKUP Setup. This reinforces the nested structure you just saw in the video. Next, carefully read the section on advanced error handling. Pay close attention to how the if_not_found argument is used in the outer (row) lookup, and how IFERROR is used to wrap the inner (column) lookup. This allows you to give specific feedback, like "Team Not Found" or "Plan Not Found," which is invaluable for building user-friendly tools for clients.

By providing distinct error messages, you make your spreadsheets much easier to debug and use, a hallmark of a professional freelance product.

Conclusion

You've now learned how to use XLOOKUP, Excel's modern and powerful data retrieval function. It not only simplifies the formulas you would have built with INDEX and MATCH but also adds native functionality for creating more robust and user-friendly spreadsheets.

Here are the key takeaways from today's lesson:

  • XLOOKUP is the modern standard: It replaces VLOOKUP, HLOOKUP, and, in many cases, INDEX(MATCH).
  • The syntax is intuitive: Its core structure is XLOOKUP(what_you_are_looking_for, where_to_look, what_to_return).
  • Two-way lookups are nested: You can perform a lookup on a grid by using one XLOOKUP to dynamically find the return column for another XLOOKUP.
  • Robust error handling is built-in: The optional [if_not_found] argument lets you handle lookup errors gracefully without needing to wrap your formula in another function.

In our next lesson, we will dive deeper into another one of XLOOKUP's powerful optional arguments: [match_mode]. This will allow us to perform approximate match lookups, which are essential for scenarios like calculating sales commissions from a tiered table or assigning grades based on scores.