# The Most Dangerous XLOOKUP Argument

Bottom Line: Avoid using a certain argument in XLOOKUP without proper investigation to prevent errors and ensure accurate data retrieval.

Skill Level: Intermediate

## Watch the Tutorial

You can follow along using the same Excel file that I used in the video.

XLOOKUP is a powerful function in Excel, but it has one feature that can cause problems if not used correctly. In this blog post, I'll highlight that feature and share some best practices and habits to help you avoid potential issues.

## Writing the XLOOKUP Formula

To demonstrate this common mistake with XLOOKUP, let's write an XLOOKUP formula.

In our example, we have some ticker symbols in a portfolio table, and we want to look them up in another data set to return the price. We'll use XLOOKUP for this.

1. Start typing `=XLOOKUP`, then tab into that.
2. Select your lookup value (the ticker symbol), then press `Ctrl+Shift+` to select all the way to the bottom. Hit `F4` to make that an absolute reference.
3. Move on to the return array, which will be the price. Again, use `Ctrl+Shift+↓ ` and `F4` for the absolute reference.
4. For the `if_not_found` argument, we'll return a blank with two quotation marks, then hit Enter.
5. Double-click the fill handle to copy the formula down.

If writing this formula causes confusion for you, feel free to take a step back and check out these tutorials that explain XLOOKUP formulas in more detail:

## Identifying Common Errors

You'll notice some blank cells, which might be because not all ticker symbols exist in the other table. However, there could be other reasons.

A common mistake is specifying the `if_not_found` argument without investigating the cause of the error. This argument handles any errors, so if there's a mismatch or Excel can't find the value, it returns what we specify in `if_not_found`.

Many users get in the habit of specifying this without investigating.

## Best Practice for Using `if_not_found`

A good habit is NOT to specify `if_not_found` first unless you're diligent about investigating blank cells. Since it's an optional argument, you can leave it out. After copying the formula down, errors will stand out more, prompting you to investigate.

## Investigating Errors

To investigate, copy the ticker from the row that has the error (`Ctrl+C`), and open the Find window using `Ctrl+F`. Paste the value and search within the ticker column of the source data.

If the value is there but has an extra character like an asterisk or space, XLOOKUP won't find it due to the exact match requirement. Cleaning up these characters is crucial.

If you have tickers with extra spaces, use the TEXTBEFORE function to clean them up. The text to evaluate is the ticker symbol, and the delimiter is a space. If it doesn't find a space, it returns the ticker. Double-click the fill handle to copy the formula down, and your data is cleaned up.

If there's still an error, gut-check it with another find operation to ensure no matches exist. Ensure “Match entire cell contents” is unchecked in the Find Options for a more thorough search.

## Adding Error Handling Back to XLOOKUP

After investigation, you can add the error handling back to XLOOKUP. Specify the `if_not_found` argument, such as returning a blank or a custom message. This ensures errors are handled appropriately after thorough investigation.

## XLOOKUP Trivia

An interesting piece of trivia: XLOOKUP was the first function to receive error handling with the `if_not_found` argument. Previously, we used the IFERROR function with VLOOKUP or INDEX MATCH for error handling. XLOOKUP's built-in error handling cleans up formulas and makes them easier to read. However, it's essential to use it correctly, with proper investigation to avoid issues.

## Conclusion

Hopefully, this post helps you prevent errors with your XLOOKUP formulas. I'm curious to know if it's helpful for you or if you have other tips for preventing errors. Leave a comment below and let us know.

I mentioned in the video that you can subscribe to my free newsletter. Click here to do that.

And if you're wondering why I didn't use Excel tables, check out this video where I explain why. Thanks for reading. Have a great day!

• V Srinivasan says:

You video clippings are very useful and I am fan of yours.

• Heinz Lyner says:

Hi, Instead of the lookup functions, I use Offset/Match combinations. Maybe they are slower, but I work with spreadsheets reports generated by an ERP, which structures are often somehow different and where neither the columns nor the rows are always where I expected. A bit tricky but quite flexible because data have not to be sorted. An d of course I include some “cleaning” functions and at the the end ifN/A or Iferror.

• Thanks for the insightful article on XLOOKUP! I personally use the INDEX-MATCH combination, which I find quite powerful because it allows for more flexibility, especially with dynamic ranges and lookup criteria. I’ve gotten pretty used to it since XLOOKUP came later and I’ve been working with INDEX-MATCH for a while.

Generic filters
Exact matches only

#### Excel Shortcuts List

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List