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})
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 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.✌😉
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.
Thanks Chandoo, this is awesome. I look after so many things in CZcams 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!
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!!
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
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!
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?
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.
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?
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
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 👍
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.
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.
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.
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!
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!
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) :)
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
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.
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!
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?
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})
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.
What if the date has 2 employees joined.
Will xlookup show both? Or just the first one?
Just the first one. You need to use FILTER or something else to get all matches. See this video - czcams.com/video/ma7u0sUIM-A/video.html
@@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.✌😉
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.
I agree Chandoo. Once XLOOKUP came out, I haven't used VLOOKUP since. Great video
The brave new world... XLOOKUP and FILTER changed the way I approach my spreadsheets now.
This is fantastic! I have been dreaming for these simple useful features for several decades. Finally, my dream came true. Thank you so much!
I find your step by step instructions very useful and relatable to a practical scenario. Keep up the good work!
Awesome Chandoo! Thanks for the great lookup examples both old and new. Thumbs up!!
One of the best tutorials I've seen. Love your excel and PBI videos!
Wow, thanks!
Wow until I watched this video Xlookup has been complex for me to understand. You explained everything so simply and thank you
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.
Congrats on your new job. I suggest catching up on "dynamic array functions" too. See this video - czcams.com/video/ONaS7IMKJPM/video.html
Master class.. Way back I learn from you in excel webpage.. Now you are on youtube.. We are expecting a fire works..🔥🔥
Thanks Chandoo, this is awesome. I look after so many things in CZcams 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!
Very well presented. I can not wait for my next vlookup project to try xlookup. Thank you
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.
Thanks and welcome
Thanks Chandoo, great video 😃
Great video by great Chandoo .. thank you for this..
Epic Knowledge!... for Day 2 day usage. Thanks!! Subscribed!!!
wow you teach in such a simple manner !!!
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!!
Very usefull thanks . Will sure learn many things from your videos
Learned something new today, Thank you.
You can also return multiple resuts in VLOOKUP with column index numbers surrounded by squiggly brackets as array constants.
This is very helpful. Thanks for the video
Great stuff, Chandoo. Thanks!
Glad you liked it!
woww nice vids, another learning :)
Thank you Uncle, you are very Humble,,,
Very informative. Thanks for sharing
Great tutorial and very apt use cases. Thanks for sharing
Thanks for watching!
Super video and great information for digital world 🌍 .
Very well explained... extremely high standard ....mush better than paid professional.
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
Good explanation Chandoo..
Thank you nice easy to understand tutorial
Even I hate VLOOKUP and this video is best ever video as XLOOKUP tutorial
Awesome 👌
Thank you so much chandu you saved me
Fantastic ! Thank you Chandoo !!
Glad you liked it!
Amazing, thank you
Awesome video!
Great Respected Sir, Mr. Chandooo !!!!
August Way to Execute the knowledge !!!!
So nice of you
Brilliant, thanks
Sir you are a real champion
Thanks for the video
Same here! Ever since Xlookup came on the scene, I stopped using VLookup AND index match - Xlookup is much more efficient!
😎
Very helpful
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!
Damn you just earned a new subscriber. This is a really powerful tool.
Thanks a lot boss
good shit bruh, keep it up
Awesome.
Time saver
You are welcome Aman.
This is awesome! Thank you!
You're very welcome!
We can use choose() function with vlookup to give result which are on left side
I think it was mentioned already but I use variables for column number and this does not seem to be possible in xlookup.
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?
Thankyou!
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.
Great Chandoo! 👍
Thanks Usman ji.
3:47 into the video, and I stopped playback to subscribe. Great info that’s easy to follow, with practical examples.
Welcome aboard Rood😀
Awesome!!! Thank you!!!!
Welcome Sachini...
Simplypy to understand this way in Case by Case
Thank chandoo
You are welcome Amir.
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?
Superb 🙂
awesome man, will save a lot of time and effort for me
Glad I could help
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
Index/match got its own function. Wow I didn’t even realize. Will start using this now!
Welcome to the brave new world
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?
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 👍
Thank you
Excellent! Vlookup is powerful but is painful if not done correctly. xlookup looks better.
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
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.
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.
hi can anybody tell me that if start date is more then 1 or more then we need all the details how we can sort that ?? i am using only Excel 2019
yes I also always use xlookup instead of vlookup, great experience sir
The best!
Truly Amazing👍
You are welcome Adi... 😀
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
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.
Love this, no more iferror!
YES!!!
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!
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!
You can. FILTER(a1:a10, b1:b10>5) returns the A1:A10 values where B column value is >5
thank you
Hi Chandoo,
What are the limitations of xlookup, if any?
What if more than one matching information available? how to return 2 or more details?
You are simply amazing
Thank you so much 😀 You are awesome too :)
Legends know that Chandoo has already made a video showing how he replaced index match with xlookup way back
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) :)
@@chandoo_ You are a Legend Chandoo. Keep them coming.
Superb video
Thanks Subranjit...
Nice
Thank you so much for your hard work. I would like to confirm that xlookup is also in excel 2021 pro plus version.
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 :)
can vlookup use in same excel file, in 2 different sheets....
Great 👍
Thank you! Cheers!
Wow too good formula...
I totally agree with you.
😎😎😎
If multiple person joined same date.how we can find out..is xlook up return multiple values for a single date.
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
Glad it was helpful!
Xlookup is literally my favourite and one of most used functions. I feel sad for people still looking vlookup haha
Thumbs up 👍🏻
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
Thanks Rahul. I have many playlists on the channel on various themes. Check out czcams.com/channels/8uU_wruBMHeeRma49dtZKA.htmlplaylists/
Thanks very much chadoo
You are welcome Osofo...
Thanks 🙏
You are welcome Taizoon...
You are an angle of education.
Just a wish, "Allah bless you"
😊 Thank you Mr Azim
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.
and that is why, It is my new favourite function of Excel
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!
I do. I explain these and many other advanced concepts in my mini-course here - chandoo.org/wp/lookups-for-data-analysis/
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?