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 TutorialWatch on YouTube & Subscribe to our ChannelDownload the Excel FileYou can follow along using the same Excel file that I used in the video.
XLOOKUP Dangers.xlsxDownload 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 FormulaTo 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.
=XLOOKUP, then tab into that.Ctrl`+`Shift`+↓` to select all the way to the bottom. Hit F4 to make that an absolute reference.`Ctrl`+`Shift`+↓andF4`` for the absolute reference.**if\_not\_found** argument, we'll return a blank with two quotation marks, then hit Enter.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 ErrorsYou'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_foundA 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 ErrorsTo 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 XLOOKUPAfter 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 TriviaAn 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.
ConclusionHopefully, 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!
Link to post: The Most Dangerous XLOOKUP Argument