How to Use XLOOKUP in Microsoft Excel: A Complete Guide

For decades, VLOOKUP was the undisputed king of spreadsheet functions. It allowed users to search for a value in one column and return a corresponding value from another. However, it was famously rigid and prone to breaking. Recognising these limitations, Microsoft introduced a modern, immensely more powerful replacement: the XLOOKUP function. If you want to analyse and categorise data like a professional, learning how to use XLOOKUP is essential.

Why XLOOKUP is Better Than VLOOKUP

Before diving into how it works, it is important to understand why you should immediately switch to XLOOKUP:

• Search in any direction: Unlike VLOOKUP, which can only search from left to right, XLOOKUP can search from right to left, top to bottom, or bottom to top.
• No more broken formulas: VLOOKUP relies on a hard-coded column index number (e.g., column 3). If you insert a new column into your spreadsheet, your VLOOKUP breaks. XLOOKUP uses dynamic cell ranges, meaning it automatically updates if you add or remove columns.
• Built-in error handling: XLOOKUP allows you to specify a custom message (like “Not Found”) if your search yields no results, completely eliminating the need for complex IFERROR wrappers.
• Exact match by default: VLOOKUP defaults to an approximate match, which frequently causes catastrophic data errors. XLOOKUP defaults to an exact match.

The Anatomy of an XLOOKUP Formula

The basic syntax for the XLOOKUP function looks like this:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

Let us break down exactly what each of these arguments means:

• lookup_value: This is the item you are searching for (e.g., an employee ID number or a product code).
• lookup_array: This is the specific column or row where you want Excel to search for the lookup_value.
• return_array: This is the column or row containing the data you want to extract and bring back.
• [if_not_found]: (Optional) The text you want Excel to display if it cannot find a match.

How to Use XLOOKUP: A Practical Example

Imagine you have a spreadsheet with employee data. Column A contains their names, and Column B contains their email addresses. You want to find the email address for an employee named “Sarah”.

1. Click on the empty cell where you want Sarah’s email address to appear.
2. Type =XLOOKUP( to begin the formula.
3. First, specify the lookup_value. You can type “Sarah” in quotation marks, or simply click on a cell that contains the word Sarah. Add a comma.
4. Next, highlight the lookup_array. Select all the cells in Column A (the names column) where Excel should search for Sarah. Add a comma.
5. Then, highlight the return_array. Select all the cells in Column B (the emails column). This tells Excel where the answer lives. Add a comma.
6. Finally, provide an [if_not_found] value. Type “Email Missing” in quotation marks, then close the bracket ).

Your final formula will look something like this: =XLOOKUP("Sarah", A2:A50, B2:B50, "Email Missing").

Press Enter, and Excel will instantly fetch the correct email address. By mastering XLOOKUP, you can dramatically speed up your workflow and completely bulletproof your spreadsheets against common errors.