How To Use Xlookup In Excel (

[@rammohan2998]

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

[@ExcelCampus]

Glad it helped 😀

[@HoangNguyen-ml9ek]

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

[@ExcelCampus]

Thanks, Hoang! :)

[@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...

[@ExcelCampus]

Thanks for the additional tips, Paul! I appreciate you taking the time to share. 👍

[@suecampanelli311]

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!

[@ExcelCampus]

Thank you Sue! I'm happy to hear you'll be helping your coworkers with this technique! 👍

[@G-love5769]

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

[@ExcelCampus]

Thanks S.C! I appreciate the nice feedback. 😊

[@ohlawdy1986]

This was a life saver!

[@axion8788]

"Value add", as always! Thanks.

[@ExcelCampus]

Thanks Axion!

[@PaulEBrownbill]

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

[@ExcelCampus]

Thanks Paul! I appreciate your support.

[@martinsjankovskis3045]

perfect - thank you vm!

[@ExcelCampus]

You're welcome! 😀

[@williamarthur4801]

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'.

[@paulmartinlife]

FYI, outside of a table Ctrl-Space selects the entire row. Shift-Space select the entire column, or table column if the active cell is in a table. I find this really useful when I have a section of data to delete, for example. Once selected, Ctrl-Space and Shift-Space will select the entire column/row and then click Delete.

[@ExcelCampus]

Thanks William! I appreciate your support!

[@mohammedadnan8641]

thanks for sharing this valuable and informative video !!!

[@ExcelCampus]

Thanks Mohammed! 🙂

[@shoaibrehman9988]

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

[@ExcelCampus]

Thanks Shoaib! 🙂

[@darrylmorgan]

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

[@ExcelCampus]

Thanks Darryl! 🙌

[@MrDodavis61]

You are a genius! 🙏

[@ExcelCampus]

Thanks! 😊

[@leoliu5789]

this is super usefull!!

[@kenwong518]

You fix my error , thanks

[@narekpapukyan5455]

Thank you for this!!

[@ExcelCampus]

You're so welcome! 😀

[@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

[@carlosyaguaratty1873]

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?

[@ExcelCampus]

Thanks Sofia! Yes, it's possible. Depending on the scenario you might be able to use a lookup formula instead of IF. Here is a video that explains more about that technique. kzbin.info/www/bejne/n3zCYZaqhJeDp6s I hope that helps. Thanks again and have a nice day! 🙂

[@soumyachoudhary5421]

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

[@williamarthur4801]

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.

[@SyedAdnanAbbas-m2z]

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

[@ameerchand7918]

thanks

[@v.eromrg]

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?

[@gtp1ok]

what version. I believe it is available only on the newest version and 365.

[@gtp1ok]

sorry, what version of excel are you using? It is only available the latest version.

[@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...

[@ignacioa3698]

i performed a power query to split up MY source data in a column that had special characters delimiters such as: /, ~, -, and a comma. then i loaded my revised source data into the same 'Sheet' and is now in Table format. then when i went to perform XLOOKUP now that i have all my revised data in one column splited, some of my cells (i noticed it was all numbers) the XLOOKUP came back as "not found", however, when i did spot check that number is currenlty actually in the comparative sheet/ (is actually in a separate workbook). do i switch my PowerQuery loaded revised data column back to a non-Table format or can i leave it as a Table? And if my new POwerQuery data column is ok to leave as Table, does it have to have the same number of rows as the Comparative lookup table then? I also got error warning in about 8-10 of the cells (from my loaded PowerQuery data) regarding the numbers format...i tried to change the cell to a "Numbers" type but XLOOKUP didnt pick up correcttly. Do i change the format to Text, General, or NUmber format for my loaded PowerQuery column data? my powerquery revised data do i leave as Table or change it to non-Table format? My comparative data column (that is in a diffr't workbook) I'll change the look-up comparative column to a Table based on your recommendation above. I'm at a lost and obvioulsy frustrating!!