For decades, VLOOKUP was the go-to formula for data retrieval. However, it had significant limitations, such as only being able to look to the right and requiring an exact column index number. Enter XLOOKUP—a more powerful, flexible, and robust function available in both Microsoft Excel and Google Sheets. In this guide, we will show you how to master XLOOKUP to streamline your data analysis workflow.

Step 1: Understand the XLOOKUP Syntax

Before diving into the steps, it is important to understand what the formula requires. The basic syntax for XLOOKUP is: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).

• lookup_value: The value you are searching for (e.g., an ID or a name).
• lookup_array: The range or column where the search value is located.
• return_array: The range or column from which you want to pull data.
• if_not_found (Optional): A custom text or value to display if no match is found (replaces the need for IFERROR).

Step 2: Perform a Basic Vertical Lookup

To perform a basic search, follow these steps:

• Select the cell where you want the result to appear.
• Type =XLOOKUP(
• Click on the cell containing the value you want to look up.
• Type a comma, then highlight the column containing the source data.
• Type another comma, then highlight the column containing the information you want to retrieve.
• Close the parentheses and press Enter.

Unlike VLOOKUP, you do not need to count columns or worry about your lookup value being in the leftmost column.

Step 3: Handle Missing Data with "If Not Found"

One of the best features of XLOOKUP is the built-in error handling. If a value doesn't exist, VLOOKUP would return a messy #N/A error. With XLOOKUP, you can define a custom message.

• In your formula, after selecting the return_array, add a comma.
• Type the message you want to display in quotation marks, for example: "Not Found".
• Your formula should look like this: =XLOOKUP(A2, D2:D10, E2:E10, "Data Missing").

Step 4: Use XLOOKUP for Horizontal Searches

Previously, if your data was organized in rows rather than columns, you had to use HLOOKUP. XLOOKUP replaces this entirely. The process is exactly the same, but instead of selecting vertical columns, you select horizontal rows for both the lookup_array and the return_array.

Step 5: Utilize Wildcard Matches for Partial Searches

If you only know part of a name or ID, you can use wildcards. To do this, you must change the match_mode (the 5th argument in the formula).

• Use an asterisk (*) as a wildcard in your lookup_value.
• Set the match_mode to 2.
• Example: =XLOOKUP("Apple*", A2:A10, B2:B10, , 2). This will find the first entry that starts with "Apple" (like Apple iPhone or Apple MacBook).

Step 6: Perform a Two-Way Lookup

You can nest XLOOKUP inside another XLOOKUP to find a value at the intersection of a specific row and column. This is an advanced technique that replaces the complex INDEX and MATCH combination.

• The inner XLOOKUP finds the column, while the outer XLOOKUP finds the row.
• Example: =XLOOKUP(Row_Lookup_Value, Row_Range, XLOOKUP(Col_Lookup_Value, Col_Range, Data_Grid)).

By mastering XLOOKUP, you eliminate the most common spreadsheet errors and significantly reduce the time spent on data management.

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