Avoid this Costly XLOOKUP Mistake in Excel

🔗LINKS


Related Content:

00:00 Introduction
01:24 Handling Errors
02:48 Investigating Errors
04:05 Data Cleanup
06:49 Add Error Handling

[justtisha]

I love that you share the keys you press.

[Ready_Set_Boom]

I suggest using a table so that the formula uses a named reference and if you insert or move columns it won’t negatively affect your formulas.

[PaulEBrownbill]

Great video, love the reminder to check errors, and not found, at all times. thanks Paul

[johnborg]

Thanks Jon. That was very interesting. :) :)

[alinecardoso]

There's something I've really wanted to do in Excel, but I haven't figured out how to do it yet. I have a supplier quotation spreadsheet, and I'd like to create a dropdown list to select the supplier's industry. Then, I want to use a VLOOKUP to automatically bring up the supplier's name, phone number, and email in other columns. The problem is that the supplier information is in a different workbook.

[tracywilliams]

I will never blindly use error handle again and how you found the errors. A lot of the data I work with are in a cold report format so I can’t change the error directly so smart on doing a copy and paste to a different column. I was doing it the opposite way by modifying the output field. I also like that you share excel history. Thank you for making my job easier 😊

[patrickschardt]

XLOOKUP like many functions is very powerful. Not understanding the data you’re working with can render it powerless

Data cleaning is important

I think when it was introduced, the if_not_found argument was in a different position too

[yochintomimo]

In the mentioned example, to achieve the #N/A form in the Price column, you can use the formula =XLOOKUP(B5:B45, G5:G1973, I5:I1973) without needing to drag the formula down. It automatically executes an automatic spill of the results.

[tedmoy]

This is the best info in a long time! I've always wondered why results were not found.

[IvanCortinas_ES]

Excellent content, as always. Thank you Jon!!!

[magnus]

Hi, great video.
Is there a way to use xlookup (or some other method) to search and lookup values in several sheets at the same time?
As far as I know xlookup can only lookup in one sheet at a time.

[teoxengineer]

There is also one adding comment is that I want to find the whole wanted value series but XLOOKUP returns just one value which is found just first value. In order to find the whole series, we must use filter or other functions with XLOOKUP.
Is there any way to find those other values by using Xlookup?

[tomjun]

Very helpful video. This helped me a lot in my job. Appreciate all your contents.

[WheelDan]

Jon, your "next video" tile appears at 5:42, not near the end of the video.

[festokozel]

Bad habits....I needed to be the first to comment