I don't use VLOOKUP anymore. I use this instead....

Рет қаралды 616,383

Күн бұрын

💥Get my XLOOKUP course & master this function 👉 chandoo.org/wp/lookups-for-da...
VLOOKUP is the most used Excel function out there. But here is a secret. I no longer use it!!!
That is right. I broke up with VLOOKUP.
I use XLOOKUP now. It is a new function in Excel 365.
So in this video, let me introduce you to my new crush 😍 - The XLOOKUP.
In this video, learn all about:
👉 What is XLOOKUP and how it's better than VLOOKUP
👉 Using XLOOKUP in business situations
👉 Errors & XLOOKUP (if not found option)
👉 XLOOKUP with tabular data
👉 Wildcard (pattern) matching with XLOOKUP
👉 Returning multiple values with XLOOKUP
👉 XLOOKUP vs. HLOOKUP
Video topics ⏱👇
==============
0:00 - VLOOKUP & XLOOKUP - Quick examples
2:13 - Difference between VLOOKUP & XLOOKUP
2:30 - Using XLOOKUP instead of INDEX + MATCH
3:32 - Dealing with Errors (#N/A) using XLOOKUP
4:57 - XLOOKUP with Tabular Data (tables)
5:40 - Wild card (pattern matching) with XLOOKUP
7:40 - Returning multiple values with XLOOKUP
9:10 - XLOOKUP works horizontally too!!!
Example file (with many many XLOOKUPs) 🟩👉
=======================================
Here is a sample file with many many XLOOKUP examples - chandoo.org/wp/wp-content/upl...
If you want more, check out this article: chandoo.org/wp/xlookup-examples/
Or join my mini-course on various lookup functions in Excel here - skl.sh/3hbR49d
Watch next 👨‍💻👩‍💻👉
================
FILTER function - • I don’t use filters in...
Spill Behaviour in Excel (# operator) - • Excel Dynamic Arrays a...
VLOOKUP explained - • Excel VLOOKUP - Compl...
INDEX+MATCH explained - • Excel's INDEX + MATCH ...
#excel #xlookup
~
What does Charles Xavier use to find mutants?
XLOOKUP of course 🤣

Пікірлер: 283

@Excelambda 2 жыл бұрын

Great video!! Still VLOOKUP is versatile when it comes to column indexes. For example, in "XLOOKUP (table)" spreadsheet, if we set in the range C5:F5 : Name, Start date, Salary, Department, to extract the values corresponding to those columns we can use in C7: =VLOOKUP(C6,staff,XMATCH(C5:F5,staff[#Headers])) or =VLOOKUP(C6,staff,{2,6,5,4})

@chandoo_ 2 жыл бұрын

That is a GREAT tip... Donut for you Exceλambda 🍩 I normally don't mind breaking such lookups in to separate formulas. If I am trying to combine two tables, I always try the "table relationships" or "power query" first before even considering any lookups.

@sandeeepkiran1130 2 жыл бұрын

What if the date has 2 employees joined. Will xlookup show both? Or just the first one?

@chandoo_ 2 жыл бұрын

Just the first one. You need to use FILTER or something else to get all matches. See this video - kzfaq.info/get/bejne/o8dnqJOkuq-xXXU.html

@Excelambda 2 жыл бұрын

@@sandeeepkiran1130 Hi, like Chandoo said, we can use FILTER, do this little experiment, set these values in the following ranges: A1:A6 B1:B6 D1:D2 a 1 b b 2 c b 3 c 4 c 5 d 6 Lookup vector (lv) D1:D2, lookup array (la) A1:A6 (has dups) , return array (ra) B1:B6 (can be any array with more than 1 clm) - formula similar to xlookup functionality in B8 or anywhere: =FILTER(B1:B6,ISNUMBER(XMATCH(A1:A6,D1:D2))) - or define a lambda that does the lookup: Duplicate Xlookup: DXL(lv,la,ra) =LAMBDA(lv,la,ra,FILTER(ra,ISNUMBER(XMATCH(la,lv)))) -call =DXL(D1:D2,A1:A6,B1:B6) Both, formula and function will extract the corresponding sections of "ra" for all matches with duplicates or not. Hope that helps.✌😉

@Excelambda 2 жыл бұрын

No need to say that this concept works also for arrays oriented horizontally. The only rule, since we use also XMATCH in the construction, both lookup vector and lookup array (lv and la) should be 1D arrays, no matter if they are horiz or vert.

@chrism9037 2 жыл бұрын

I agree Chandoo. Once XLOOKUP came out, I haven't used VLOOKUP since. Great video

@chandoo_ 2 жыл бұрын

The brave new world... XLOOKUP and FILTER changed the way I approach my spreadsheets now.

@udaimadduri6137 Жыл бұрын

This is fantastic! I have been dreaming for these simple useful features for several decades. Finally, my dream came true. Thank you so much!

@KhiaraHarsh 3 ай бұрын

I find your step by step instructions very useful and relatable to a practical scenario. Keep up the good work!

@wayneedmondson1065 2 жыл бұрын

Awesome Chandoo! Thanks for the great lookup examples both old and new. Thumbs up!!

@5pctLowBattery 2 жыл бұрын

Cool 😎 I started at a new company that uses office 365. Old company still used 2010 old excel. Lots to learn with new excel features.

@chandoo_ 2 жыл бұрын

Congrats on your new job. I suggest catching up on "dynamic array functions" too. See this video - kzfaq.info/get/bejne/hbSRhpp6srGugIE.html

@TannerDavies 2 жыл бұрын

One of the best tutorials I've seen. Love your excel and PBI videos!

@chandoo_ 2 жыл бұрын

Wow, thanks!

@Magahasaracom Жыл бұрын

Wow until I watched this video Xlookup has been complex for me to understand. You explained everything so simply and thank you

@rameshbhatt3231 Жыл бұрын

Thanks Chandoo, this is awesome. I look after so many things in KZfaq but never really subscribed anything. After watching your video today, I instantly subscribed and clicked on like as well. This is really helpful and presented very nicely. Appreciate it, god bless you!

@rkmsafi Жыл бұрын

Master class.. Way back I learn from you in excel webpage.. Now you are on youtube.. We are expecting a fire works..🔥🔥

@robevans2114 Жыл бұрын

Very well presented. I can not wait for my next vlookup project to try xlookup. Thank you

@dkjangir 2 жыл бұрын

great sir I believe you are the first website i used to learn excel at all.& I am very happy to see again chandoo excel expert.

@chandoo_ 2 жыл бұрын

Thanks and welcome

@canirmalchoudhary8173 2 жыл бұрын

Even I hate VLOOKUP and this video is best ever video as XLOOKUP tutorial Awesome 👌

@abbottkatz8830 Жыл бұрын

You can also return multiple resuts in VLOOKUP with column index numbers surrounded by squiggly brackets as array constants.

@nonoobott8602 2 жыл бұрын

Great tutorial and very apt use cases. Thanks for sharing

@chandoo_ 2 жыл бұрын

Thanks for watching!

@utubeAgape 2 жыл бұрын

Same here! Ever since Xlookup came on the scene, I stopped using VLookup AND index match - Xlookup is much more efficient!

@chandoo_ 2 жыл бұрын

😎

@neXianXaviaX Жыл бұрын

Chandoo help! I have a lot of things to lookup, I have multiple tables and I have to add my lookup results together! Not only that but there's many totals I want to calculate and put into a new table! Very useful, I've watched a few of your videos and I'm a fan so I clicked the subscribe button for more excel tips :p

@sass520 Жыл бұрын

Sir first of all i would like to tell you tht your videos are amazing and very nicely explained, i also got a query wht does (* ) means here when u used in xlookup ?? Is it same like And condition in the filter video?

@richasingh-zu8pz Жыл бұрын

wow you teach in such a simple manner !!!

@harishpoalampalli9637 Жыл бұрын

This is Incredible !! simply the superb replacement of INDEX MATCH + VLOOKUP very powerful!! thanks a chandoo !! This is helping me a lot !! Great Explanation with sample workbook !! the uniqueness in you is providing the entire workbook for practice which helps a lot by watching the video and on fly implementing them !! Great Keep them coming chandoo!!

@silverpegasusknight Жыл бұрын

Epic Knowledge!... for Day 2 day usage. Thanks!! Subscribed!!!

@afzaalahmad3185 2 жыл бұрын

Great Respected Sir, Mr. Chandooo !!!! August Way to Execute the knowledge !!!!

@chandoo_ 2 жыл бұрын

So nice of you

@yashmangupta Жыл бұрын

Interesting to learn this new function. Can it return multiple date of joining based on the example in your video. If there are multiple people joining the same date? Thanks much appreciated 👍

@bhaskardebnath6401 2 жыл бұрын

Very well explained... extremely high standard ....mush better than paid professional.

@bharatputra108 Жыл бұрын

Very usefull thanks . Will sure learn many things from your videos

@auliakhamil Жыл бұрын

Thanks Chandoo, great video 😃

@TheAbzy01 Жыл бұрын

Learned something new today, Thank you.

@xtraordinaire Жыл бұрын

Great stuff, Chandoo. Thanks!

@chandoo_ Жыл бұрын

Glad you liked it!

@incredible421 Жыл бұрын

This is very helpful. Thanks for the video

@johnkempes5538 Жыл бұрын

Thank you Uncle, you are very Humble,,,

@Moglikaur Жыл бұрын

Great video by great Chandoo .. thank you for this..

@BhadriShashikanth Жыл бұрын

Very informative. Thanks for sharing

@andresfrancojunor 2 жыл бұрын

Fantastic ! Thank you Chandoo !!

@chandoo_ 2 жыл бұрын

Glad you liked it!

@jamiereife5581 Жыл бұрын

It would be nice if there was a “match formatting indicator” in the formula to match the formatting from where our result derives so that you don’t have to correct the formatting in your formula result. So if the formatting in the result column was a date or currency, a match formatting indicator would automatically ensure that your result matched the formatting of the column from where the result derived. This would be nice because almost nobody would want a date answer returned in a Juliane date or currency returned without commas.

@jaganpreetsingh1842 Жыл бұрын

We can use choose() function with vlookup to give result which are on left side

@mindlearn2981 Жыл бұрын

Thank you for this explanation. I have another scenario that I would like some clarification on. What if in Column A, I have e-mail addresses of people, and then in Column B, I have text with data in it as follows separated by comma's e.g. pizza, pasta, potatoes. E-mail adress 1 has pizza, potatoes, E-mail address 2 has pasta and E-mail 3 has pasta, potatoes. I would now like to make 3 additional separate columns Pizza and Pasta and Potatoes with in these columns the ' lookedup ' e-mail addresses. In the colunm name Pizza, only e-mail addres 1 would be mentioned. In Column Potatoes, E-mail 1 and E-mail 3 would be shown and so on. How to go about? Can this be done with XLOOKUP? Or is some other formula needed? Thank you for your reply!

@abhiabhishek7942 Жыл бұрын

Thank you nice easy to understand tutorial

@Rood67 Жыл бұрын

3:47 into the video, and I stopped playback to subscribe. Great info that’s easy to follow, with practical examples.

@chandoo_ Жыл бұрын

Welcome aboard Rood😀

@damodaramgurram1905 Жыл бұрын

Good explanation Chandoo..

@mrspongetv4236 Жыл бұрын

Hi sir. Can you help me? What i wanted in my ghant chart is that when i add a holiday, all activities will adjust even if the other activities did not fall onto the holiday date.. can it be possible?? Love the video by the way.

@Unbox9999 6 ай бұрын

Thank you so much chandu you saved me

@razadigital92 Жыл бұрын

Super video and great information for digital world 🌍 .

@usmaniqbal1836 2 жыл бұрын

Great Chandoo! 👍

@chandoo_ 2 жыл бұрын

Thanks Usman ji.

@daprinceofdabay 2 жыл бұрын

Index/match got its own function. Wow I didn’t even realize. Will start using this now!

@chandoo_ 2 жыл бұрын

Welcome to the brave new world

@dhanflores2416 Жыл бұрын

Damn you just earned a new subscriber. This is a really powerful tool.

@Scooby_D000 Жыл бұрын

Hi Chandoo, have you used Xlookup for multiple criteria, may 2D or 3D (ie. search for criteria that is vertical and horizontal to get the Return Value). Thank you for your nice work!

@chandoo_ Жыл бұрын

I do. I explain these and many other advanced concepts in my mini-course here - chandoo.org/wp/lookups-for-data-analysis/

@KeenyNewton Жыл бұрын

Excellent! Vlookup is powerful but is painful if not done correctly. xlookup looks better.

@teoxengineer 2 жыл бұрын

Chando hi, good presentation. We can use Vlookup with different directions by using Choose function like =Vlookup(X,A1:C100,Choose({2,1},Array-1,Array-2),0). It provide us to go opposite direction like XLookUp. However, when we change column sequence or not sorted range, VLookUp is not giving us the correct result. So, I would like to learn just LookUp function because it is being used to find different matches as same value. How can we achieve it? Could you please prepare LookUp video for it. Thank you 🙏

@chandoo_ 2 жыл бұрын

Thanks Emre... The VLOOKUP(..CHOOSE()) is a good trick and I've used it in the past. I suggest watching the FILTER video for this. kzfaq.info/get/bejne/gNuEl81jz5Ode5s.html

@Mike-yv3ko 2 жыл бұрын

For different matches of same value use instead: UNIQUE+FILTER (you can also use transpose to list values across)

@saikrishnapagadala7449 2 жыл бұрын

Hi Chandoo, so basically only when we convert the range to table, we can return the entire row correct? Can we return a row when we are using normal data range instead of converting to table? Thanks in advance!

@chandoo_ 2 жыл бұрын

You can. FILTER(a1:a10, b1:b10>5) returns the A1:A10 values where B column value is >5

@thephunk1 2 жыл бұрын

This is awesome! Thank you!

@chandoo_ 2 жыл бұрын

You're very welcome!

@jatinpalsingh5562 2 жыл бұрын

awesome man, will save a lot of time and effort for me

@chandoo_ 2 жыл бұрын

Glad I could help

@deerajkumarkanala9145 Жыл бұрын

I have a filter for my column, and i am trying to select a keyword manually from the manually, so is there any option so that i can put this keyword in some cell and write some filter formula so that it will automatically filter the keyword from the list?

@jeromegonzales3809 Жыл бұрын

woww nice vids, another learning :)

@shiroyasha_007 2 жыл бұрын

Legends know that Chandoo has already made a video showing how he replaced index match with xlookup way back

@chandoo_ 2 жыл бұрын

OG!!! Yes, I have few other XLOOKUP videos on the channel. But I feel that most of our new followers are not familiar with XLOOKUP. So I made another video (with more tips) :)

@shiroyasha_007 2 жыл бұрын

@@chandoo_ You are a Legend Chandoo. Keep them coming.

@silvana11221122 2 жыл бұрын

hi Chandoo hope you are well. I like how you dont have to say exact match etc... i just need to practice and change my habits... i still do some lotus 123 lol

@chandoo_ 2 жыл бұрын

All good here Silvana... 😀 Wow, someone from Lotus times.

@silvana11221122 2 жыл бұрын

@@chandoo_ yes lol every time excel brings out a new version it hides our old lotus backstokes further and further away but i can still find some ha

@NeutronStar9 Жыл бұрын

Always thought why vlookup doesn’t allow me to select the query column and return column and it took I don’t know how much since your video is news to me, thank you!

@Svbp99 2 жыл бұрын

I think it was mentioned already but I use variables for column number and this does not seem to be possible in xlookup.

@mikatu Жыл бұрын

I am getting sick of these tips that only work in Office365. It is great, but why would I use a program that will never be mine? Anyway, great video and explanation.

@jeisonsanchez4842 2 жыл бұрын

Only issue with xlookup is that I can’t share the spreadsheet to people with older Excel versions. I have to think whether the file will be shared before I decide between vlookup and xlookup. Thanks for the awesome tips.

@fawazkolaposulaiman1925 2 жыл бұрын

You could break the links in your sheet, if you have to share and if other users don't need to understand the underlying function you used.

@chandoo_ 2 жыл бұрын

That is a good point. Yeah, if I working with a client that doesn't have XLOOKUP, I just revert to either VLOOKUP or INDEX MATCH as needed.

@amirhd2080 2 жыл бұрын

Simplypy to understand this way in Case by Case Thank chandoo

@chandoo_ 2 жыл бұрын

You are welcome Amir.

@nealbhakta9235 Жыл бұрын

Thank you so much for your hard work. I would like to confirm that xlookup is also in excel 2021 pro plus version.

@chandoo_ Жыл бұрын

Woohoo... That is a good news indeed. I have switched to 365 many moons ago so not keeping up with fixed versions. Thanks for letting us know :)

@shreyayerhs Жыл бұрын

Hi Chandoo, What are the limitations of xlookup, if any?

@sanjayjha1966 2 жыл бұрын

yes I also always use xlookup instead of vlookup, great experience sir

@chandoo_ 2 жыл бұрын

The best!

@vishnunair8790 2 жыл бұрын

Hi sir i beend using xlookup for while but at some point even xlookup fails example if we want to lookup value for one single column xlookup fails but vlookup runs fine example in one workbook i have employee id and in another work book i have same employee and want to get which are duplicate via formula with out using condition formatting function so xlookupfails or may be i dont knw how to reconstruct the formula could you help

@judithalex2087 Жыл бұрын

Hi Chandoo, thank you for this!! XLOOKUP is a game changer. Just one issue- I just used it to return a date on the cell. However, the source data for that particular lookup value is blank. But the XLOOKUP returns 1/0/1900. Can you share how I can have the cell come out as blank if the source data is blank?

@chandoo_ Жыл бұрын

You are welcome Judith. Excel doesn't have true BLANK values. When a formula finds blank, it returns 0. Which for dates, treated as 1/0/1900. You can try ISBLANK(XLOOKUP(..)) to check if the result of XLOOKUP is indeed blank and then do something. Another technique is to use File > Options > Advanced > "Display options for this worksheet" > Show a zero in cells that have zero value and unchecking it. But it will also remove any actual zeros from the screen.

@curious1731 5 ай бұрын

Very helpful

@yuthsumaleewichai4226 2 жыл бұрын

Hello Chandoo from 2:47, if there are more than one person starting working on the same date, what results would Xlookup will return? Thanks for making the videos about excel.

@chandoo_ 2 жыл бұрын

Xlookup (and all other lookups in Excel) just return the first matching value. If you want to see all results, you can use FILTER. Here is a video about that - kzfaq.info/get/bejne/gNuEl81jz5Ode5s.html

@mikedavies7662 2 жыл бұрын

Awesome video!

@rahulchandoria 2 жыл бұрын

Getting lots of inspiration from you chandu. Keep uploading the new videos. Its my suggestion for you could you please make a playlist of Excel learning from A toZ videos. I use to share your video and channel to my friends but they are getting difficulty to start from where

@chandoo_ 2 жыл бұрын

Thanks Rahul. I have many playlists on the channel on various themes. Check out kzfaq.info/love/8uU_wruBMHeeRma49dtZKAplaylists/

@Mrdayz 2 жыл бұрын

Thank you, I found this useful. I have been using =ifna(index...(match), It works well but looks messy. I will use xlookup in the future. Thank you

@chandoo_ 2 жыл бұрын

Glad it was helpful!

@maverickriderJB Жыл бұрын

One question, if we have multiple items in lookup array with same name will xlookup return all corresponding values. I tried and it brings only the first one.

@surajvishwakarma9199 Жыл бұрын

Sir you are a real champion

@hdreaming2315 2 жыл бұрын

good shit bruh, keep it up

@sachiniwickramasinghe1912 2 жыл бұрын

Awesome!!! Thank you!!!!

@chandoo_ 2 жыл бұрын

Welcome Sachini...

@shylesambrose Жыл бұрын

Brilliant, thanks

@mahmoudsaeed1892 Жыл бұрын

Amazing, thank you

@oa3999 2 жыл бұрын

how to use it to retrieve multiple values from the same column, is it possible? I use INDEX and ROW and it's so long formula

@asifkhan-zt3dl 2 жыл бұрын

From few days when ever I use xlookup, in data if i have error result after that would be error in rest of the line items. Any idea why?

@toto7002 2 жыл бұрын

What if I have more than one return, how can make sure there are no other value may match the criteria, assuming the look up not unique

@Adi-jk2ef 2 жыл бұрын

Truly Amazing👍

@chandoo_ 2 жыл бұрын

You are welcome Adi... 😀

@chandrashekarsheshadri7231 Жыл бұрын

Great video. Getting error with # Name? using excel 2010. do not work in 2010 version.

@prasadnaga9113 Жыл бұрын

Thanks for the video

@asukanta 9 ай бұрын

Thanks a lot boss

@amanmalalo8653 2 жыл бұрын

Awesome. Time saver

@chandoo_ 2 жыл бұрын

You are welcome Aman.

@vivek1202 Жыл бұрын

Hi, How to look for values of multiple choices like instead of looking for 1 data in respective column, if I want to find out data for multiple choices for eg. Todays Date, Symbol, Expiry, Option Type....Now I want to look for the price in there respective column against these multiple choices. In other words.... How to find 1 data for multiple choices?

@syarifairlangga4608 2 жыл бұрын

Xlookup is just so modern and easy. Data source also doesnt need to be on the left but can be anywhere. Basically just click n click n done. Microsoft need implement new modern formula like this.

@chandoo_ 2 жыл бұрын

and that is why, It is my new favourite function of Excel

@mymatemartin 2 жыл бұрын

Can do Chandoo does it again. Fantastic video.

@chandoo_ 2 жыл бұрын

Thanks again!

@canirmalchoudhary8173 2 жыл бұрын

Currently the only limit that I faced is that it can't simultaneously work on multiple rows and colum range. In that I have to apply XLOOKUP in adjacent column because I extract values for multiple row values spanning over multiple column.

@chandoo_ 2 жыл бұрын

If your adjacent column is a SPILL Range, then you can also use XLOOKUP like this: =XLOOKUP(a1#, lookup arry, result array) and it will automatically extend to the number of rows needed as per A1#

@canirmalchoudhary8173 2 жыл бұрын

@@chandoo_ Oh let me try that also.

@avishekd4229 Жыл бұрын

Superb 🙂

@tsumichama 2 жыл бұрын

I don't use vlookup either (or hlookup). But that's because I use index match. (Some people where I work still have excel 2010 so I avoid newer functions. I try to keep up with them as they come out but it's hard to master them when you don't use them 😭)

@chandoo_ 2 жыл бұрын

Aww.. sorry to hear that. I sometimes work with clients who are still using older versions of Excel and it annoys me to no end. If you want to just practice these functions, you can always try Excel online.

@uddipannath4162 2 жыл бұрын

You are simply amazing

@chandoo_ 2 жыл бұрын

Thank you so much 😀 You are awesome too :)

@theyeezyscript 2 жыл бұрын

Xlookup is literally my favourite and one of most used functions. I feel sad for people still looking vlookup haha

@deegadams 28 күн бұрын

I have a really complex problem. I feel like there is some way using filter & xlookup to solve this but I can't figure it out. I have a table that has 5 columns (standard, 5 day, duplicate, pre-eng, dwgs only, transcend, other) & then 10 rows with codes for simplicity sake well just say (AA, BB, CC, DD, etc.) There is data that intersects with each column & row. There is a validation input cell. My boss made this huge Ifs formula with Vlookup. I tried to just change all the VLookup with Xlookup but i get an error. I saw a video that can use multi criteria column header with row data but can't make that based on a validation input cell. I know there is a way but I'm wracking my brain to figure it out.

@268791avinash 2 жыл бұрын

Thats great, I am asking you a different question, WHich software you use to edit your videos :)

@chandoo_ 2 жыл бұрын

I use Camtasia for my videos. You can get a free trial here - techsmith.pxf.io/c/3169105/506622/5161

@amitpahwak Жыл бұрын

Thank you

@tillytoad804 2 жыл бұрын

Love this, no more iferror!

@chandoo_ 2 жыл бұрын

YES!!!

@thordeian 10 ай бұрын

Help Chandoo! I copied and pasted something from your templates into a "reference" workbook that I use regularly. In this copy/paste process I seemed to bring along with it a "DISCLAIMER" message Popup notification that requires me to click the "X" to remove it. How do I get rid of it? I can't seem to find any help on this, probably because I don't know what to call it to find help on it. Can you help, please?

@chandoo_ 10 ай бұрын

Not sure what you are referring to here... if this is a file that you downloaded or bought from me, it might be helpful if you can email me the details.