XLOOKUP vs VLOOKUP: Why You Should Switch (2025 Guide)

Nothing is worse than a #REF! error 5 minutes before a meeting. You've spent hours preparing your report, and now you're stuck with an error that you can't seem to fix. By the end of this post, you'll be able to identify and fix #REF! errors, and make the switch to XLOOKUP for more reliable lookups. Imagine you have a dataset of 5,000 Sales IDs, and you need to find the corresponding sales amount for each ID.

Scenario-Based Example

Imagine you have a dataset of 5,000 Sales IDs, and you need to find the corresponding sales amount for each ID. You can use the XLOOKUP function to achieve this. First, select the cell where you want to display the sales amount, then navigate to the Formula bar and type:

=XLOOKUP(A2, SalesIDs, SalesAmounts, "Not Found", 0, 1)

This formula looks up the value in cell A2 in the SalesIDs column, and returns the corresponding value in the SalesAmounts column. If the value is not found, it returns the text "Not Found".

Common Mistakes

One common mistake when using XLOOKUP is forgetting to specify the [if_not_found] argument. This can cause the formula to return a #N/A error if the lookup value is not found. To fix this, simply add the [if_not_found] argument to the formula, like this:

=XLOOKUP(A2, SalesIDs, SalesAmounts, "Not Found", 0, 1)

Another common mistake is using the wrong [match_mode] or [search_mode]. This can cause the formula to return incorrect results or errors. To fix this, make sure to use the correct [match_mode] and [search_mode] for your dataset.

When things go wrong, it can be frustrating. Here are some common errors and their solutions:

• #N/A error: This error occurs when the lookup value is not found. To fix this, make sure to specify the [if_not_found] argument in the XLOOKUP formula.
• #REF! error: This error occurs when the formula refers to a cell or range that is not valid. To fix this, make sure to check the cell references in the formula and correct any errors.
• #VALUE! error: This error occurs when the formula contains a value that is not valid. To fix this, make sure to check the data types of the values being used in the formula and correct any errors.

Suppose you have a dataset of sales data, with columns for Sales ID, Sales Amount, and Sales Date. You can use the XLOOKUP function to find the sales amount for a specific sales ID, like this:

=XLOOKUP(A2, SalesIDs, SalesAmounts, "Not Found", 0, 1)

This formula looks up the value in cell A2 in the SalesIDs column, and returns the corresponding value in the SalesAmounts column. If the value is not found, it returns the text "Not Found".

You can also use the XLOOKUP function with the INDEX and MATCH functions to perform lookups with multiple criteria. For example:

=XLOOKUP(1, (SalesIDs=A2)*(SalesDate=B2), SalesAmounts, "Not Found", 0, 1)

This formula looks up the value in cell A2 in the SalesIDs column, and the value in cell B2 in the SalesDate column, and returns the corresponding value in the SalesAmounts column. If the value is not found, it returns the text "Not Found".

Stop fighting with syntax. Generate this formula instantly with our tool: Use the Excel Formula Generator