How To Use Xlookup In Excel (#value Error Fix)

In this video, learn how one solution can take care of both #VALUE! errors and Absolute Reference errors when working with XLOOKUP formulas.

Related Videos:

Shortcuts for writing XLOOKUP and VLOOKUP formulas:

When to use VLOOKUP instead of XLOOKUP:

XLOOKUP for Excel: Explained in 3 Minutes:

The New XLOOKUP Function: Compared to VLOOKUP & INDEX MATCH in Excel:

VLOOKUP Tutorial for Excel - Everything You Need To Know:

00:00 Introduction
00:24 XLOOKUP Formula
02:54 Insert Table

#MsExcel #ExcelCampus

[HoangNguyen-mlek]

Thanks Jon. Really appreciate your video, it's short and straight forward.

[paulmartinlife]

Thanks, Jon, I very much appreciate your presentation style and Excel knowledge, including this video. I've been seeing a lot, various trainers talking about using the F4 button to make absolute references, but I don't usually see them discussing this in more detail, so I thought I'd share my perspective.

Personally, I find the $ sign unsightly in large formulas, so I use them sparingly. I think of the $ sign as an anchor, and I call my use of it as anchoring - anchoring a column or anchoring a row (or both). I find this conceptually useful when I'm looking at a cell reference in a formula, to consider what I need to anchor. Simply using F4 once (on a relative reference) and getting both row and column absolute references is often overkill, and anchoring only what's needed keeps my formulas as clean and easy to read as possible.

Another tip: as a long-time Excel VBA developer, I find the default Excel formulation difficult to read with no spaces (as a developer, I like ample white space for readability). I always put a space after each comma, which I find makes complex formulas much easier to read. Not often, but when I have a long formula, sometimes I'll use Alt-Shift to put separate sections of a formula on a different line. Cheers...

[G-love]

Thanks so very much. I really appreciate your tutorials. So easy to follow. Enjoy your day!

[suecampanelli]

Thanks! Very useful. I use tables as much as possible however not all of my colleagues know about them therefore the error demonstrated occurs frequently. This video helped me understand the root cause - I’m better prepared now to help my co workers! I’ll share the video with them as well! Thanks again!

[rammohan]

Thanks Jon,
Helped me a lot on Xlook up errors,

[axion]

"Value add", as always! Thanks.

[PaulEBrownbill]

Excellent video thanks, and good reference point to previous videos for a refresher. Thanks Paul

[shoaibrehman]

Good one it was happened, as you said absolute ref is the key. Thanks

[darrylmorgan]

As Always Great Explanations And Cool Tips...Thank You Jon :)

[williamarthur]

Never knew ctrl space, really handy for time saving, I do like structured references, they make me feel like I know what I'm doing. 😉 Enjoyed your video on structured 'absolute reference'.

[mohammedadnan]

thanks for sharing this valuable and informative video !!!

[jaidenty]

Hi Jon love the videos! I have started using lookup and also often get the #name errors. I then use trim on the columns on my main spreadsheet and the reference sheet where I am pulling the info from and still get the error any tips?

[sahirvalsan]

Thank u and please continue posting such videos. I tried using xlookup command and got incorrect value for a few cells out of multiple other cells where the value was pulled correct. When I tested with vlookup it did the same thing and the exact same cells had incorrect values as compared to xlookup. However when I changed the range from False to true using vlookup, it managed to pull the correct value for the affected cells. Any suggestions

[carlosyaguaratty]

Hi Jon, I actually having some troubles when referring a column of a table, when I go to the top of that column to see the black arrow to select the entire column the formula shows me a range reference instead of a table column reference, you mentioned something about to turn off a table formula, could you please tell me what video do you explain this please 🙏

[piadizon]

Very helpful thanks. Can you use multiple IF function with xlookup function?

[williamarthur]

One thing, is there any way around causing a vertical spill error when using an array When working within a table example (leila gharani video ) ;
Sequence ( Counta( range )), works fine if its in a range but use it within a table and it can't spill. Hmm.

[soumyachoudhary]

every time i reopen an excel sheet on which i use xlookup, all such cells show #Name? error, how can i resolve this?

[dirxqcw]

My friend shared an excel file with me, xlookup is not working on my system while xlookup working properly on his system at same file.
# value error is showing on my system

[veronicarg]

My Excel doesn’t let me use xlookup o vlookup between workbooks, and when I try to copy the sheet it doesn’t show all other files open, anyone know how fix it?

[montebont]

The problem you are trying to fix is BAD DATA. Fix the data instead of using Excel work-arounds. I know you mean well but the basic idea is that a stupid processor (Excel) can only work reliable with validated and complete data.

You address a data problem that should not be there in the first place...