Thanks for all amazing training videos, short, sweet and right to the point but very through! Would you kindly let me know how I can use XLOOKUP up to search for the number of rows with a field includes certain value for This and/ or Last Week, This and/ or Last Month, This and/ or Last Quarter and This and/ or Last Year? ( The first column in my worksheet is a Created Date and I would like to compare the created rows for different categories based on above mentioned time periods, last month's quote, orders, invoiced,.....) How would the formula could look? Do you know a better solution without NO VBA? Thank you!
Hello Moe Sadr, we're glad you found the video helpful! To better understand and answer your question, could you please share a sample of your worksheet and what you would like it to do by joining our Slack channel here: goskills.typeform.com/to/h0GBH4oe. See you there!
I know this was three years ago, but... this REALLY helped. I have watched (too) many vids on VLookups with much, much frustration. I was recently told about XLookups and wanted to know the difference between them... this was super helpful and was presented in a manner I could actually understand. I am not an Excel fan, but I have to use it. My brain is not wired to grasp this stuff easily. I will definitely go back to this vid and check out the others. Thank you!
In return array what to do if my columns are not together from where I am selecting. Like there are other columns in between them. So what do I need to type or how should I select.
Hi @somyajain3431, if you want to return multiple columns using XLOOKUP, those columns must all be adjacent (next to each other). If you want to return values from more than one column but those columns are not adjacent, you might consider using FILTER nested within FILTER. You can take a look under the subheading "Example 4 - FILTER and FILTER" in the following article for how this can be done: www.goskills.com/Excel/Resources/Excel-dynamic-arrays. Hope this helps!
Fantastic Video, I love the way you highlighted the results in green, One thing I often do is search for data on another worksheet which you can do easily just put the name of the worksheet followed by an exclamation mark! and then the lookup range eg: =XLOOKUP(A2,placements!A:A,placements!B:B,"Unknown") In this example the worksheet I am looking at is called placements
hi @joypid, are you new to Excel? We have a free crash course here: www.goskills.com/Course/Learn-Excel-Tutorial/About If you want to do the Basic to Advanced certification, check it out here: www.goskills.com/Course/Excel/About Contact our Support team if you have any questions! Email them at support@goskills.com
Amazing explanation, however Need more complex examples in xlookup .. also vlookup only searches for data on the right and not left .. should have been emphasized more when speaking about advantages about xlookup and this is one of the key differentiating factor between xlookup and vlookup
Hi @nangiasharad, that's right! Lookup in any direction is definitely one of the biggest advantages of XLOOKUP. The full list is summarized in the article linked in the video description www.goskills.com/Excel/Resources/Xlookup-vs-vlookup. For a more challenging workout, try these Excel challenges featuring XLOOKUP and compare your solution to ours. Challenge 7: th-cam.com/video/OX9IX1mBAn8/w-d-xo.html Challenge 24: th-cam.com/video/mos0NMw3jeM/w-d-xo.html Enjoy!
very clear explanation. I have a question, I am able to get the first name last name at a time as you explained but when I tried to apply it to other cells I am unable to do it like how we drag or double click.please help me
Hello Divya, you can copy the formula in the usual way - by dragging or double-clicking on the fill handle. But to make sure that your range stays the same, use absolute cell referencing like this: =XLOOKUP(A6,$J$4:$J$14,$K$4:$K$14) before you copy. You're just putting dollar signs before each reference. Learn more about absolute cell referencing here: th-cam.com/video/BdoKfkO0MYg/w-d-xo.html
Hi Leroy, you can lock the range (make it an absolute reference) by inserting "$" before the row and the column of the reference. E.g. A2:A12 would become $A$4:$A$12. See our video th-cam.com/video/BdoKfkO0MYg/w-d-xo.html for an explanation and shortcuts to using this feature. Otherwise, you could just turn your dataset into a Table and not have to worry about that at all th-cam.com/video/r-kQtyKq6A4/w-d-xo.html
Hi, I have a question. I am using VLOOKUP to insert information to a new sheet. Now, I would like it to be done automatically after the input is written. Any ideas to solve this?
Hi Emmanuel! The purpose of $ doesn't change. It is used to "lock" or fix references to a cell, so yes you would still use $ to make a reference absolute. If you don't like using $ to refer to a range, you can convert your data set to an Excel table.
Sorry Vlookup, Xlookup is my new best friend
Very well explained
Tq
greatly
Thanks for all amazing training videos, short, sweet and right to the point but very through! Would you kindly let me know how I can use XLOOKUP up to search for the number of rows with a field includes certain value for This and/ or Last Week, This and/ or Last Month, This and/ or Last Quarter and This and/ or Last Year? ( The first column in my worksheet is a Created Date and I would like to compare the created rows for different categories based on above mentioned time periods, last month's quote, orders, invoiced,.....) How would the formula could look? Do you know a better solution without NO VBA? Thank you!
Hello Moe Sadr, we're glad you found the video helpful! To better understand and answer your question, could you please share a sample of your worksheet and what you would like it to do by joining our Slack channel here: goskills.typeform.com/to/h0GBH4oe. See you there!
Thank you so much for giving such a nice differentiated video between the two essential formulas. I think,Vlookup is about to RIP now.
Hands down one of the best excel tutorials I have seen and I have seen too much
Glad you think so!
I know this was three years ago, but... this REALLY helped. I have watched (too) many vids on VLookups with much, much frustration. I was recently told about XLookups and wanted to know the difference between them... this was super helpful and was presented in a manner I could actually understand. I am not an Excel fan, but I have to use it. My brain is not wired to grasp this stuff easily. I will definitely go back to this vid and check out the others. Thank you!
It's a classic! We're glad to know it has been useful for you since then! 🌟
In return array what to do if my columns are not together from where I am selecting. Like there are other columns in between them. So what do I need to type or how should I select.
Hi @somyajain3431, if you want to return multiple columns using XLOOKUP, those columns must all be adjacent (next to each other). If you want to return values from more than one column but those columns are not adjacent, you might consider using FILTER nested within FILTER. You can take a look under the subheading "Example 4 - FILTER and FILTER" in the following article for how this can be done: www.goskills.com/Excel/Resources/Excel-dynamic-arrays.
Hope this helps!
Does X work faster than INDEX+MATCH?
Yes, much and it has a "built in" iferror.
Fantastic Video, I love the way you highlighted the results in green, One thing I often do is search for data on another worksheet which you can do easily just put the name of the worksheet followed by an exclamation mark! and then the lookup range eg: =XLOOKUP(A2,placements!A:A,placements!B:B,"Unknown") In this example the worksheet I am looking at is called placements
Correct, Rupert. Glad it was useful!
Index match match still the best lol
excellent ill use this for my reports. thanks!!
Simple explanation and to the point. Thank you
Thank you for the review, Basil! We're glad to hear this tutorial has been useful!
Best video on xlookup on youtube. Well done.
Great Job, thank you
Happy to help!
Yo this helped me alot
excellent, thank you
Thanks ,will like to know about excel.
hi @joypid, are you new to Excel? We have a free crash course here: www.goskills.com/Course/Learn-Excel-Tutorial/About
If you want to do the Basic to Advanced certification, check it out here: www.goskills.com/Course/Excel/About
Contact our Support team if you have any questions! Email them at support@goskills.com
Thank you so much!
You are a King!!! The explanations are clear and concise. Very grateful I found this channel.
I appreciate that!
Amazing explanation, however Need more complex examples in xlookup .. also vlookup only searches for data on the right and not left .. should have been emphasized more when speaking about advantages about xlookup and this is one of the key differentiating factor between xlookup and vlookup
Hi @nangiasharad, that's right! Lookup in any direction is definitely one of the biggest advantages of XLOOKUP. The full list is summarized in the article linked in the video description www.goskills.com/Excel/Resources/Xlookup-vs-vlookup.
For a more challenging workout, try these Excel challenges featuring XLOOKUP and compare your solution to ours.
Challenge 7: th-cam.com/video/OX9IX1mBAn8/w-d-xo.html
Challenge 24: th-cam.com/video/mos0NMw3jeM/w-d-xo.html
Enjoy!
Thank you great short comparison.
You're so welcome!
EXCELLENT VIDEO!!!!! Everything is well explained and slow step by step instructions!
Great! Thanks!
Great Informative Video.. Thanks For sharing
Great explanation
Thank you, Santhosh!
Thank you!! You made it nice and simple and most important quick!!
This was super helpful. Thank you!
Excellent tutorial! Thanks Dan!
Thank you! Your tutorial was very simple, to the point, and easy to follow.
Thank you so much! We're thrilled to hear that you found the tutorial simple and easy to follow. 😊 Your feedback means a lot to us!
This is so nice. can't believe I was still doing ISNA and VLOOKUP for the past three years🙈
😂 XLOOKUP certainly is a game-changer, Helena!
very good explanation
Thanks Anshul! Glad you liked it!
thanx for the video
Our pleasure!
very clear explanation. I have a question, I am able to get the first name last name at a time as you explained but when I tried to apply it to other cells I am unable to do it like how we drag or double click.please help me
Hello Divya, you can copy the formula in the usual way - by dragging or double-clicking on the fill handle. But to make sure that your range stays the same, use absolute cell referencing like this: =XLOOKUP(A6,$J$4:$J$14,$K$4:$K$14) before you copy. You're just putting dollar signs before each reference. Learn more about absolute cell referencing here: th-cam.com/video/BdoKfkO0MYg/w-d-xo.html
Nice video
Happy to help!
Describe techniques are good.
Nice, but when I try and copy the formula to other cells it changes the cell lookup range. How do you lock this field?
Hi Leroy, you can lock the range (make it an absolute reference) by inserting "$" before the row and the column of the reference. E.g. A2:A12 would become $A$4:$A$12. See our video th-cam.com/video/BdoKfkO0MYg/w-d-xo.html for an explanation and shortcuts to using this feature.
Otherwise, you could just turn your dataset into a Table and not have to worry about that at all th-cam.com/video/r-kQtyKq6A4/w-d-xo.html
Thanks, Dan. Very valuable!
Hi, I have a question. I am using VLOOKUP to insert information to a new sheet. Now, I would like it to be done automatically after the input is written. Any ideas to solve this?
For example; cell A4 is written then the rest fill out based on the previous functions written instead of continuing to drag down
Hi @@aphricanlenss , you can format your data as a table and new rows will be populated with the formulas automatically. Hope this helps!
You might want to check out this video: th-cam.com/video/r-kQtyKq6A4/w-d-xo.html
great teacher
Do I still need to use absolute reference '$' in xlookup?
Hi Emmanuel! The purpose of $ doesn't change. It is used to "lock" or fix references to a cell, so yes you would still use $ to make a reference absolute. If you don't like using $ to refer to a range, you can convert your data set to an Excel table.
Superb
Clear, short and crisp
Excellent explanation. Thanks!!!
Thank you, Miluska! 🙌 We're glad you found it helpful!
nice. put excel file link in the description
Hi Ubaidillah, you can find the file download in this article: www.goskills.com/Excel/Resources/Xlookup-vs-vlookup
Very clear and precise explanation. Thank you!
You're welcome!
Clear and concise! Thanks!
Happy to hear that, Rodney!
Really helpful! Thank you.
Glad it was helpful!
Very helpful, Dan. Thank you.
Glad it was helpful!
Amazing! Just an amazing explanation. Thank you, Sir.
You helped me solve the formula, thank you so much!