For years, VLOOKUP has been the go-to function for finding data in Excel, but it comes with significant limitations, such as only being able to look to the right and requiring a specific column index number. XLOOKUP is the modern successor that is more powerful, flexible, and easier to use. This guide will show you how to master XLOOKUP to streamline your data analysis.

Step 1: Understand the XLOOKUP Syntax

Unlike VLOOKUP, which requires four arguments, the basic XLOOKUP function only requires three. The syntax is: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]). The first three are mandatory, while the others are optional features that give you more control over your search.

Step 2: Perform a Basic Vertical Lookup

To find a specific piece of information, select the lookup_value (the cell you are searching for), the lookup_array (the column where that value is located), and the return_array (the column containing the information you want to pull back). One of the biggest advantages is that XLOOKUP defaults to an exact match, so you no longer need to type 'FALSE' at the end of every formula.

Step 3: Perform a Left-Side Lookup

One of the biggest frustrations with VLOOKUP is that it cannot look at columns to the left of the lookup value. With XLOOKUP, this is no longer an issue. Simply select your return_array even if it sits to the left of your lookup_array. Excel will find the match and return the data regardless of column order.

Step 4: Use the 'if_not_found' Argument to Handle Errors

Instead of wrapping your formula in an IFERROR function, XLOOKUP has a built-in error handler. The fourth argument, [if_not_found], allows you to specify what should happen if no match is found. For example, you can enter "Not Found" or 0 between quotes to keep your spreadsheet looking clean and professional.

Step 5: Master Horizontal and Range Lookups

XLOOKUP replaces both VLOOKUP and HLOOKUP. If your data is arranged horizontally in rows rather than columns, the function works exactly the same way—just select row ranges instead of column ranges. Additionally, you can use the [match_mode] argument (set to -1 or 1) to perform approximate matches, which is ideal for calculating tax brackets or sales commissions.

Step 6: Utilize Wildcard Matches for Partial Data

If you only know part of a name or ID, you can use wildcards. By setting the [match_mode] to 2, you can use the asterisk (*) to represent any number of characters. For example, searching for "App*" in the lookup value will return the first result starting with "App," such as "Apple" or "Application."

💡 Pro Tip: Keep your software updated to avoid these issues in the future.