For decades, VLOOKUP was the go-to formula for finding data in Excel. However, it had significant limitations, such as only searching to the right and requiring sorted data for approximate matches. XLOOKUP is the powerful, modern successor that simplifies data analysis by being more flexible, faster, and easier to read.

Step 1: Understand the XLOOKUP Syntax

Before diving into the steps, you need to understand the components of the formula. Unlike VLOOKUP, which requires a column index number, XLOOKUP uses ranges. The basic syntax is: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).

• lookup_value: What you are searching for.
• lookup_array: Where to look for that value.
• return_array: The range containing the data you want to retrieve.

Step 2: Perform a Basic Vertical Lookup

To perform a standard lookup, follow these steps:

1. Select the cell where you want the result to appear.
2. Type =XLOOKUP( and select the cell containing the value you want to find (e.g., an Employee ID).
3. Type a comma, then highlight the column containing the IDs (lookup_array).
4. Type another comma, then highlight the column containing the names you want to return (return_array).
5. Close the parentheses and press Enter.

Step 3: Use the Built-in "If Not Found" Feature

One of the best parts of XLOOKUP is that you no longer need to wrap your formulas in IFERROR. If a value isn't found, you can define a custom message directly in the formula.

Example: =XLOOKUP(A2, D:D, E:E, "Not Found"). If the value in A2 is missing from column D, the cell will display "Not Found" instead of the ugly #N/A error.

Step 4: Perform a Left Lookup

Unlike VLOOKUP, XLOOKUP can look to the left. Since you are selecting specific ranges for the lookup and return arrays, it doesn't matter where they are located in relation to each other. Simply select your return_array even if it sits to the left of your lookup_array.

Step 5: Use XLOOKUP for Horizontal Lookups (HLOOKUP Replacement)

XLOOKUP isn't just for columns; it works for rows too. If your data is arranged horizontally:

1. Select your lookup_value.
2. Select the row (e.g., Row 1) as your lookup_array.
3. Select the row (e.g., Row 5) as your return_array.
4. Excel will automatically process this as a horizontal search, eliminating the need for the HLOOKUP function.

Step 6: Utilize Wildcard Matches for Partial Text

If you only have part of a name or code, you can use wildcards. To do this, you must change the match_mode (the 5th argument) to 2.

Example: =XLOOKUP("*"&A2&"*", D:D, E:E, , 2). This will find the value in A2 even if it is surrounded by other text in the lookup column.

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