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

Watch on YouTube & Subscribe to our Channel

Download the Excel File

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.
XLOOKUP Formula example

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.

blank space from XLOOKUP

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.

XLOOKUP Error Code

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.

no exact match causes error

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.

TEXTBEFORE function

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.

MAtch entore cell contents

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.

if_not_found argument

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!

3 comments

Your email address will not be published. Required fields are marked *

  • 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

keyboard shortcuts list banner

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

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter