How to do a Left Lookup in Excel with XLOOKUP (Made Easy)
ฝัง
- เผยแพร่เมื่อ 30 ก.ค. 2024
- Join 400,000+ professionals in our courses here 👉 link.xelplus.com/yt-d-all-cou...
A common Excel Lookup question is: How to return a value left of the lookup range in Excel? It's not easy if you use VLOOKUP.
⬇️ Grab the workbook here: pages.xelplus.com/left-lookup...
You either need to use a workaround or INDEX and MATCH.
With the Excel's XLOOKUP function we can use one single formula to do a left lookup in Excel. It's super easy because XLOOKUP takes the best of VLOOKUP and INDEX & MATCH formulas. We have a separate independent lookup range and a separate return range.
*** Availability of XLOOKUP: XLOOKUP is available in Excel for Office 365 and 2021. It will not be available for Excel 2016 or Excel 2019. If you'd like to learn more about the new dynamic array functions like FILTER, UNIQUE & SORT check out the complete course: www.xelplus.com/course/excel-...
Original XLOOKUP video: • Discover What XLOOKUP ...
★ My Online Excel Courses ► www.xelplus.com/courses/
➡️ Join this channel to get access to perks: / @leilagharani
👕☕ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
🎓 Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
🎥 RESOURCES I recommend: www.xelplus.com/resources/
🚩Let’s connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
Grab the file I used in the video from here 👉 pages.xelplus.com/left-lookup-file
Get access to the complete course: www.xelplus.com/course/excel-dynamic-array-365-formulas/
Thanks for additional tip
"soon"
Thanks for the XLOOKUP (with the correct arguments) fun, Leila!!!
Hello leila,
I just wanted to say thank you!
You helped me a lot through my career ! I wish you all the best.
Just found you today and I'd admit this is the best excel channel on TH-cam. Really appreciate your effort. SUBSCRIBED
Glad to have you here.
Hi Leila.. thanks for the left lookup with XLOOKUP. Of course, without XLOOKUP the go to favorite would be an INDEX and MATCH solution. Just for the fun of it, some don't know that you can get VLOOKUP to look left by tricking it with CHOOSE, as in: =VLOOKUP(A4,CHOOSE({1,2},Master!$C$4:$C$33,Master!$A$4:$A$33),2,0). Fun to slice and dice a problem using different solutions. Thanks for all your videos. I always learn something new at your channel :-)) Thumbs up!
Thanks for the video. You are the best looking teacher ever.
Thank you for the update Leila. Great Video.
Great explanation! Thank you 🙏🙏
Thanks a lot Ma'am for such an easy way to get the left hand side data
Valuable information and helpful, thanks
More useful excel majic from Leila. Thank you Thank you.
Thank you! Very helpful
Nice tutorial mam, keep it continue. Love you.
Thx for the video appreaciate all the hardwork
left or wright Leila is always in heart .thx queen
Thanks it’s very easy 👍🏻👏🏻
Thank you so much.
Thanks for video!
Great lesson.
Great, thank you!
I say bring lookup to the masses. Thanks for sharing
Great staff!!! :)
really..good.. its solves vlookup problem
This is great!
Nice video!
thanks great video
Thank you!
Excellent 👌👌👌👌
So great Leila, thank you, Viele Liebe Grüsse 🤗
Danke liebe Katerina!
Thanks for all
Thank you...
Nice video Leila :)
Good Knowledge in Excel And Accounting
Please make the video accounting bases
Thanks Leila
Love you
good one
this video changed my life.
What's unique in your channel is that your videos are so easy to learn. Good pacing as well. You should also be teaching in schools.
Thank you for the kind feedback!
Hi Liela,
Hope you are safe and doing good👍
You made excel find easy for me to work out.
I have a question to ask can we create a FORM where in start time and end time is entered into excel in a form created for row wise? If that is possible please make a video for our understanding.I am using tracker sheet in excel date wise tracker.
Am Using latest office version.
Regards
Hi Leila. I have recently started using an entire column cell reference like this: =SUM(A1:A:A) can you share your thoughts on this? Any precosions I might take when using this type of cell reference?
Soo pretty😙
Thnx 💞
Good
Thnx
thanks....
Leila I need to customize my Row and Column headings for default, how can I do that in Excel 2016?
Merci
Great Video!!! can you make a tutorial on how to use the xlookup in a table with a defined name to look at the leftmost column of the table similar to vlookup? I want to use the xlookup to search for approximate values but my data are in a table (dynamic changing) where the look values are located on the leftmost column. Thank you in advance 🙂
Fan from india
Thanks for the video can't wait for XLOOKUP to go GA
Me too :)
Nice
Thank you Leila for the great work, could you also please make a video about how to transpose a wide database into a long one?
Do you mean something like this: th-cam.com/video/dF0cVRyC8c4/w-d-xo.html
Nice dress mam as well as nice video...
For those without XLOOKUP the easiest way is to use the DGET function; it can look to the left.
Oh. Thanks. I still don't have XLOOKUP (Excel 2019). I'll lookup DGET (no pun intended) and practice a bit.
Hi Leila,
how to paste data into filter records.
Good vid. Thx. I would say, "locked" instead of "fixed" though. Fixed has the other meaning of corrected. That confuses me. I say locked when locking a range, but that is my personal preference.
Love you sister......😘😘😘
How can i format cell’s comma style ie.
I need to convert 1,000,000,000.00 to Tk. 1,00,00,00,000.00 also in negative value ie. Tk. (1,00,00,00,000.00).
Please suggest without VBA & changing regional location.
Thanks in Advance.
Hi Leila! I really need your help, i have had great use of your videos, but i need a function that allows me to cross-reference a name list (check all names on 2 different excel documents and then compare them to my list of names, to see if they are on that list or not) and then if possible add the names of the 2 other lists to my list of names if they are not on my list!
This is super labor intensive if i cant find a function on excel that will help me to do that... Please help!!!!!
I have not been able to find this in your videos, but maybe i missed something - I really hope you can help me!!!!!
Leila mam thank you
can be done also with hlookup and match functions in older version
=hlookup(column name containing the data to be found, aimed searching field, match(given lookup value, array containing the lookup value,match type),false)
how can i lookup by name..like a search bar so i can apply vlookup on the name searched
i am facing problem to use XLOOKUP function in excel. i am getting an error message.
please tell me why is it showing and how can i solve this problem
Once again, excellent, Leila! My problem is remembering all the things Excel can do and then identifying when I could use them instead of muddling through as is often the case :-) Do you have a sample database that can be downloaded to run through these exercises? Thanks, and best regards from Ireland!!
George Reynolds
If you go to her website, she has an excel file you can down load
Hi George - Check out the Excel file at the bottom of this blog post: www.xelplus.com/free-excel-tutorials/
I think the first step is to know the possibilities. Once you have an idea of what Excel can do, you'll know the keywords to use to search for a solution. It's not necessary to know everything by heart - just the fact that you had seen it somewhere and you know it can be done.
@@joefromdc Thank you!
@@LeilaGharani Thank you, Leila - you're a star! I'm going to sit down this weekend and run through a few exercises and get to know the possibilities as you suggest.
О! Этот вариант куда проще Индекс+Поискпоз. Кто знает, как называется эта функция в русской версии?
Please why I don't have this new feature in my excel 😞
i am facing the same problem too. try using dget function in excel.
thankyou
Where is XLOOKUP function? Which ver of Excel? If not how to do with our function or formula.
Check out the description of the video. It's available in Office 365 - currently insider version but coming soon to all 365 subscribers.
I am unable to get the XLOOKUP function in my Office 365. Any way?
Yes, join Insider Fast here: insider.office.com/en-us/
How do I get access to Xlookup?
Join Insider Edition of Office 365.
Love you sister 😗😘😘😂
Hi leila, My Name is amit kundu & from Haryana (India), i wanna apply a formula for tracking of overdue amount on the basis on customer's TOT (on days basis). Example: - In Cell A5 name of Customer after that in B5 if have payment term on the basis TOT (Term of Trade), in C5 i need to shown overdue amount (on the basis on payment term) in D5 Total outstanding amount, in E5 amount of 15 days, in F5 amount of 30 Days, in G5 amount of 45 Days, in E3 to G3 over due period (i wanna track day form there), so please suggest me for this task.
How can I activate xlookup in Excel 2013.
The only way to activate it in the future is to get Office 365.
Any word on when xlookup coming to office365? I'm not an insider, it's blocked
Dynamic arrays are now rolled out to 365 - I'd expect Xlookup to come soon too...
@@LeilaGharani Awesome, thanks
Is this only for 365? I could not find in 2019
Yes, only in 365.
Pls mam.. I need ur help 🙏🙏
let me know why didn't get this XLOOKUP formula in excel 2007, 2010, 2016
Because it's a complete change of Excel's calculation engine. It's not possible to implement it in these versions.
How can I get this function?
It will be available in Office 365. Currently it's still in Beta in the Insider Edition.
I still prefer the INDEX MATCH way though.
It was difficult for me to switch, but after testing XLOOKUP for different scenarios, I was convinced.
this seems way simpler though. but i agree index match has always been solid
good .....First comment yyyyyyyyaaaaaaa :)
Again, why describe a function which isn’t available yet, and could perhaps never pop-up outside the insider program?
Because I want to show the exciting, new features too. I doubt very much that it won't be released by Microsoft outside of Insider Edition. Dynamic arrays are being rolled out to Office 365 users. It's good to be ready for when they do come.
Leila Gharani they are only new exciting features when they are available to everyone. Just my 2 pennies. I am very sceptic that this function ever will be available in the wild.
@@pietdegeling4696 useful to me and I use it every day. It is the only lookup function I use. It is available to every one wirh 365--all u have to do is sign up for insider. It is available to anyone and only takes s few seconds to sign up. It's free. So just do it. It took you longer to write your comment than it takes to sign up for insider.
Great video. XLOOKUP is the only lookup function I use.
Richard Hay where do i say it is not a useful function? It sure took you less time to make assumptions then it took me to write my answer. And for your info, even though in fact it is none of your business, i am “subscribed” to the insider ring, and even so, no xlookup.
And even more, i asked Leila a question regarding why showing this, which she answered clear and to my satisfaction. Why you feel the need to “attack” me about things i never even hinted about is beyond my understanding.
I didn't get it in my 365 office
It's still in Beta in Office Insider.
I'm using office 365 but xlookup is not available !!
you need to be in the Insider program. Open Excel, go to account and click on Office Insider. Then update your office.
Sadly. It's an INSIDER program. INDEX and MATCH for now. :
Yes - we can get ready for when it comes. I'm hoping soon....
Why are you putting out videos about a function none of us can access?
Because I want to show the exciting, new features too. And you can access it. It's in Insider Edition of Office 365.
I can access it Tom, as can many others. It's very useful to me as an Excel user to know what functionality I can expect in the near future so that if I'm making changes to processes and Excel templates I know that they are (or are not) going to be replaced with something better shortly after. I may wish to hold off re-designing something difficult if the XLOOKUP formula can solve a later problem.
In future, I think things like XLOOKUP will be released more quickly too. I suspect that this particular change is taking some time because of the other, larger, changes to dynamic arrays and the calculation engine. Once they are released, I suspect that things like XLOOKUP will spend a lot less time in the insider program going forward.
Also, Leila puts out multiple videos - for free - on a host of existing features. It's not like you're short of useful information in her back catalogue.