I learned something you probably wasnt expecting to teach. I never realized you could select part of the function in the formula bar and press f9 to get the true/false of the equation. I always used the step through process to determine the results.
@@LeilaGharani Thank you so much for your work. I have another problem, help me fix excel vba autocomplete search from a combobox to search for numbers, date, currency data types not only the text format, your feedback will be highly appreciated, thank you in advance
Such clever approaches to solving every stumbling block I've ever come across in Excel can be found on your channel. Plus all the problems I didn't even know I had. You're the best Excel Unicorn on RUclips.
Dear Leila, The LOOKUP function allows the 'lookup_value' to also be a vector and not a single cell. To find the Bonus: =LOOKUP(B2:B5; G2:G6; H2:H6) To find ID =LOOKUP(A2:A5; M2:M11; K2:K11) 🤗
Great video!! If the lookup vector is an array, values of last column of that array will be returned, so no need for 3rd argument. Spreadsheet "Lookup" cell C2 : =LOOKUP(B2:B5,G2:H6) Spreadsheet "Lookup Advanced" , single cell formula in C2: =LET(b,A2:A17,p,B2:B17,MAP(b,SEQUENCE(ROWS(b)),LAMBDA(x,i,LET(s,SEQUENCE(i),XLOOKUP(x-1,INDEX(b,s),INDEX(p,s),"-",,-1)))))
Thank you very much Leila. On finding the LOOKUP function it seemed to be exactly what I wanted and couldn't get it to work. On watching this video, I could not believe that the list needed to be ordered. The Microsoft description of the function didn't mention that, but it was mentioned in small print towards the end of the help page. Watching this video saved me a lot of time on this useless function.
You did it again. You took an overlooked function and delivered a great result. Although th XLookup is better the thought behind the Lookup is far supperior.
The real fun happens when you nest multiple xlookup functions together. The most I have done so far is 3 nested together, and it works like a charm. The beginning of the formula looks like: =(XLOOKUP(XLOOKUP(XLOOKUP(...) The result from your first xlookup (the furthest to the right) serves as the reference value for the second and so on. This allows you to lookup a value in one table, search for the match in a second table, and then take those matches and search for a match in a third table.
Thank you very much for this. I have been trying to write this formula for two days now and I keep getting the wrong result until now, I've learned that my list has to be sorted out in ascending order. thanks again.
When your dealing with “larger than RAM” data you need to consider using a database to store your dat, or also using something like apache arrow which will efficiently split the files for your analysis. If your computer can’t handle it, it is gonna take forever to process and you’re CPU/GPU will hate you 😅
Hello ! thanks for the explanations. My only use of LOOKUP until now was to find the last non blank value in a range with some blanks. If the non blanks values were numbers, I used =LOOKUP(9^9;$A$2:A3), if they were text =LOOKUP("ZZZZ";$A$2:A3)
I wish more people would use tables to keep your data dynamic if you're adding to a table; it's a little tricky to learn how to reference but once you have it down it's actually easier
Hi Leila great to see Lookup getting a bit limelight! I have used it to avoid a nested IF say where you convert a lookup value to an index number CHOOSE can use to turn on the formula to apply e.g. =CHOOSE(LOOKUP([@[Major Cost Type]],{"Beams","Piers","Slabs"},{1,2,3}),fx1,fx2,fx3) Its so simple to embed in a formula but you do have to have the list in alphabetical order I think from memory. Maybe IFS() is a better option now I haven't decided. But I like LOOKUP, it's simple and reads well in a formula.
I use the UNIQUE function all the time! The only thing I wish about UNIQUE is that it could calculate just as accurately across rows as it does down columns. So I just have the extra step of Copying and Pasting Special > and Checking the Transpose Box, but it isn’t that bad.
Your a queen, thank you for passing this information I kind of knew what I was doing but I just needed a extra bit of help the memory isn't perfect sadly lol
Is it possible to explain in a video the difference between VLOOKUP (Excel) and Merge tables (Power BI) and 1 to many relationship in data model (Power BI) ? And when to use ? Pieter
Brilliant video. So nice to revisit older functions Often people seem to be more interested in the newer and flashier when older are proven and often more appropriate.
Please friend, can you make tutorials on how to get recent football form with the date, teams home and away, results from their last games played, thank you!
Waaaaaaay over my skill level ... 😵🥴🥴😵😵🥴🥴😵😵🥴🥴 ... but glad I stuck around to the 10-minute mark. Candidly, I don't think I have any situation to which this technique might apply ... but I will not discard it so quickly. Maybe I do, but do not realize it ... yet. As usual, thank you Leila ... for opening doors to other worlds ...
I have not used a Vlookup for years. thanks as this has brought most of it back, for my next trick I have to apply this to the client and national data sets. Question for me now as there is so much data, how do we validate this?
So today I was using the =POWER formula but couldn't solve the equation I was trying to solve, what would you recommend. The equation is 3^x + 4^y = 5^z xyz are integers and can't be the same number :)
The use on Bom application Is very awsome, But for cost proposal if we want to find the cost for those element children (means for example one element on level 2 that have other sublevels 3 or 4 inside it) we find a mistake cause those other level 3-4 Will be exclude of level 2 if we simple try to perform the sumifs funtion we loose the cost for those other elements
Spreadsheet "Lookup Advanced" , single cell formula in C2: =LET(b,A2:A17,p,B2:B17,MAP(b,SEQUENCE(ROWS(b)),LAMBDA(x,i,LET(s,SEQUENCE(i),XLOOKUP(x-1,INDEX(b,s),INDEX(p,s),"-",,-1)))))
When sorting data, you do not have to select the data first, as long as it is a contiguous range. Excel knows that already. You could have simply used your right-click method, used the sort/filter dropdown from the Home Tab, or from the Data tab.
LookUp is so interesting function which I'm searching most of time to understand its consept. So, thank you for you to make a well explanatory video. Besides, can we use nested sumproducy function in LookUp function to eleminate error values for good sorted range of array?
@@LeilaGharani I found it troublesome because when I use xlookup and when the data in lookup sheet is replaced the lookup colum will always be moving to few columns ahead so I had to go back to use vlookup for those scenario
Someone told me to use this technique for creating sub ledger from ledger. But this is so difficult to understand and apply. Im not sure I'll ever be able to use it
Hi can you help with a solution for drop down list were someone cannot paste on those cells If we lock the cells we won't be able to use the drop down as well so how to overcome this problem
IndexMatch works much better. Lookup can't even do a proper ID to Name match function between two columns from a drop down or even not a drop down. Keeps screwing up the numbers. Even if I put dollar signs. IndexMatch does it perfectly.
Thank you for your interest in spreading the flag please help me understand which has been used using xlookup I understood how it was done but I need to know from you that the result appeared thanks
@@LeilaGharani Thank you for your attention and respectable reply, May God bless your age. I have a request at your presence. I am a cost accountant who needs a sheet of excel to monitor inventory using incoming pricing. First, it is spent first in stock because I have many codes and the inventory system has a limited period of time. I need equations to implement in my eccesiliate sheet, monitoring the incoming stock. First, it is possible, if you please, an email to send you an oxil sheet to apply the equation to it because seriously, you need your help in helping you. Dr. Lilly, thank you.
Hello Leila, Is your courses available in French. Or are they translated to french. if not, where do you recommend to refer friends to watch excel content in french since my friend loved your courses alot but he is french educated
Dear Leila, May i ask for an advice? I create charts with thousands of rows and dozens of columns that are assembled from multiple files - sometimes it happens that single values need to be deleted, and finding it inside HUGE table is impossible . Is there a way to indicate data's row/column from a table by pointing to a point on a chart? to be exact, to highlight a cell from the data extracted from the plot. I have searched many ways, but unfortunately have not found a solution to this problem. The only information I was able to get was the coordinates of the point, but I want to highlight the exact row and column.
Do you have a video to do lookups on tiered pricing by quantity? For example I have one item with 3 prices depending on the quantity. So it would first look at the item # and then the quantity 1000
You might consider concatenating item numbers with a deliminator and the minimum qty. Then do a range lookup. Sumproduct would work nicely for a small dataset but there is too much overhead for thousands of rows.. I've done tiered pricing in Access using the where clause to join tables. It worked very well.
I've tried for years to figure out what the algorithm is that Excel is using when it returns funky answers when doing an approximate match AND when the lookup vector is NOT sorted in ascending order. Just curious, has anyone figured that out yet?
It's interesting that the arguments are described as VECTOR instead of RANGE. This is very accurate because vectors are ordered since they have a origin and direction.
Grab the file I used in the video from here 👉 pages.xelplus.com/lookup-function-file
I learned something you probably wasnt expecting to teach. I never realized you could select part of the function in the formula bar and press f9 to get the true/false of the equation. I always used the step through process to determine the results.
I'm very happy you found something new!
I am happy to see u teaching in
Such easy manner
@@LeilaGharani Thank you so much for your work. I have another problem, help me fix excel vba autocomplete search from a combobox to search for numbers, date, currency data types not only the text format, your feedback will be highly appreciated, thank you in advance
same here. I did not know you could do it. Is it introduced in a certain version of excel ?
I did not know this either and it has changed my life 🤣🤣
Such clever approaches to solving every stumbling block I've ever come across in Excel can be found on your channel. Plus all the problems I didn't even know I had.
You're the best Excel Unicorn on RUclips.
😭😭😭You make me fall in love with Excel everytime you post a new video. Thank you.
Dear Leila,
The LOOKUP function allows the 'lookup_value' to also be a vector and not a single cell.
To find the Bonus: =LOOKUP(B2:B5; G2:G6; H2:H6)
To find ID =LOOKUP(A2:A5; M2:M11; K2:K11) 🤗
Great video!!
If the lookup vector is an array, values of last column of that array will be returned, so no need for 3rd argument.
Spreadsheet "Lookup" cell C2 :
=LOOKUP(B2:B5,G2:H6)
Spreadsheet "Lookup Advanced" , single cell formula in C2:
=LET(b,A2:A17,p,B2:B17,MAP(b,SEQUENCE(ROWS(b)),LAMBDA(x,i,LET(s,SEQUENCE(i),XLOOKUP(x-1,INDEX(b,s),INDEX(p,s),"-",,-1)))))
Leila you are too good, had trouble with LOOKUP, but no other video explained that data needs to be sorted, also learned the F4🙏
Glad it was helpful!
Nice to see old school LookUp getting some attention and love.
Thank you very much Leila. On finding the LOOKUP function it seemed to be exactly what I wanted and couldn't get it to work. On watching this video, I could not believe that the list needed to be ordered. The Microsoft description of the function didn't mention that, but it was mentioned in small print towards the end of the help page.
Watching this video saved me a lot of time on this useless function.
You explained the logical part beautifully. The simplified lookup function in office 365.
You did it again. You took an overlooked function and delivered a great result. Although th XLookup is better the thought behind the Lookup is far supperior.
The SORTING part is the think that I was looking for. Thank you.
The real fun happens when you nest multiple xlookup functions together. The most I have done so far is 3 nested together, and it works like a charm.
The beginning of the formula looks like:
=(XLOOKUP(XLOOKUP(XLOOKUP(...)
The result from your first xlookup (the furthest to the right) serves as the reference value for the second and so on. This allows you to lookup a value in one table, search for the match in a second table, and then take those matches and search for a match in a third table.
Thank you very much for this. I have been trying to write this formula for two days now and I keep getting the wrong result until now, I've learned that my list has to be sorted out in ascending order. thanks again.
You’ve increased my confidence so much. I appreciate you and I hope all is well
I learned x lookup from you and makes my life easier.
It's requires a lot of CPU processing power though (in large amount of cells).
When your dealing with “larger than RAM” data you need to consider using a database to store your dat, or also using something like apache arrow which will efficiently split the files for your analysis. If your computer can’t handle it, it is gonna take forever to process and you’re CPU/GPU will hate you 😅
Hello ! thanks for the explanations. My only use of LOOKUP until now was to find the last non blank value in a range with some blanks. If the non blanks values were numbers, I used =LOOKUP(9^9;$A$2:A3), if they were text =LOOKUP("ZZZZ";$A$2:A3)
I would prefer to see the Bonus % being higher for the lower paid so that the resulting bonus amount is similar!!!
Good explanation thank you.
underperformance should be rewarded less than overperformance, so the example was on point
I wish more people would use tables to keep your data dynamic if you're adding to a table; it's a little tricky to learn how to reference but once you have it down it's actually easier
Hi Leila great to see Lookup getting a bit limelight!
I have used it to avoid a nested IF say where you convert a lookup value to an index number CHOOSE can use to turn on the formula to apply e.g.
=CHOOSE(LOOKUP([@[Major Cost Type]],{"Beams","Piers","Slabs"},{1,2,3}),fx1,fx2,fx3)
Its so simple to embed in a formula but you do have to have the list in alphabetical order I think from memory. Maybe IFS() is a better option now I haven't decided. But I like LOOKUP, it's simple and reads well in a formula.
Thanks Leila. Very interesting, I almost never use this but it's good to know!
I hardly use it myself 😉 My go to is XLOOKUP.
I'm using Vlookup only, because too old for all these xlookups :) Recently began to use UNIQUE function - its amazing! :)
I use the UNIQUE function all the time! The only thing I wish about UNIQUE is that it could calculate just as accurately across rows as it does down columns. So I just have the extra step of Copying and Pasting Special > and Checking the Transpose Box, but it isn’t that bad.
Xlookup is a gamechanger...
It really is!
Your a queen, thank you for passing this information I kind of knew what I was doing but I just needed a extra bit of help the memory isn't perfect sadly lol
Nice, Now I want to see it with Grouping of the levels, How can you make the grouping work?
Hm... Thank you Leila!
I'll try to apply this formula on my work! :)
Cause before it i've used another one to check levels.
Is it possible to explain in a video the difference between VLOOKUP (Excel) and Merge tables (Power BI) and 1 to many relationship in data model (Power BI) ? And when to use ?
Pieter
love the "attention guy" !
😁
I'm using xLookup anytime it's required as it avoids me to build queries. It's faster and makes the file much lighter.
Awesome Leila! Thanks for the detailed demonstration and lookup tricks. Thumbs up!!
Glad you liked it, Wayne!
I always look forward to seeing your Excel videos. Thank you so much.
Thank you for your support.
Salaam Khaneem Leila, could you please upload full class of excel. I want to lean from beginning please upload it. Tashakor!
I loved the BOM reference!
Brilliant video. So nice to revisit older functions
Often people seem to be more interested in the newer and flashier when older are proven and often more appropriate.
Please friend, can you make tutorials on how to get recent football form with the date, teams home and away, results from their last games played, thank you!
Thank you for the BOM example. How would I also apply the% of each component depending on the parent?
So good material 🥰 I'm in love 🤓
Really well explained, using great examples!
Loved this video Leila!
And as always........ Looking great whilst doing it 🥳
and there it goes, when you think you know xlookup and you are proven that there is so much more to learn :), thank you once again
Great technique, I have been looking to build something exactly like this to a parent hierarchy problem 😀
Thank U👍
Waaaaaaay over my skill level ... 😵🥴🥴😵😵🥴🥴😵😵🥴🥴 ... but glad I stuck around to the 10-minute mark. Candidly, I don't think I have any situation to which this technique might apply ... but I will not discard it so quickly. Maybe I do, but do not realize it ... yet.
As usual, thank you Leila ... for opening doors to other worlds ...
Thanks for watching that long! Agree this function is not for everyone. I hardly use it myself. XLOOKUP is my go-to.
That's awesome! Thank you Leila!
Thanks
Every time Iam trying to learn something from your videos..i end up staring at you and forget what you said..😳
Excellent video! thank you Leila!
Greatest Help to me...so much Thanks to you !
Glad it's helpful!
everyday, excellent for you
I have not used a Vlookup for years. thanks as this has brought most of it back, for my next trick I have to apply this to the client and national data sets. Question for me now as there is so much data, how do we validate this?
So today I was using the =POWER formula but couldn't solve the equation I was trying to solve, what would you recommend. The equation is 3^x + 4^y = 5^z xyz are integers and can't be the same number :)
Thank you for sharing this😊❤.
Another great video lesson!
That was exceedingly useful and well explained, thanks!
Hi there! I'm looking to compare two products list and their pricing. Which one of your videos do you recommend me watch?
Thanks so much
could u make an other of this solving in diffrent way.
I never knew lookup function. Always used vlookup a lot.
I love your teaching.....
The use on Bom application Is very awsome, But for cost proposal if we want to find the cost for those element children (means for example one element on level 2 that have other sublevels 3 or 4 inside it) we find a mistake cause those other level 3-4 Will be exclude of level 2 if we simple try to perform the sumifs funtion we loose the cost for those other elements
The XLOOKUP solution is better, but is there a way of doing it with a single formula?
Spreadsheet "Lookup Advanced" , single cell formula in C2:
=LET(b,A2:A17,p,B2:B17,MAP(b,SEQUENCE(ROWS(b)),LAMBDA(x,i,LET(s,SEQUENCE(i),XLOOKUP(x-1,INDEX(b,s),INDEX(p,s),"-",,-1)))))
Thanks for the share.
When sorting data, you do not have to select the data first, as long as it is a contiguous range. Excel knows that already. You could have simply used your right-click method, used the sort/filter dropdown from the Home Tab, or from the Data tab.
Very well explained. Thanks.
Glad you liked it!
Hello Ms. Please tell me how to remove open password in excel .... Great help .... Open password is not the protection password
LookUp is so interesting function which I'm searching most of time to understand its consept. So, thank you for you to make a well explanatory video.
Besides, can we use nested sumproducy function in LookUp function to eleminate error values for good sorted range of array?
Thanks Emre. Yes, you can mix and match functions but I'd personally go with XLOOKUP if you have that. It's neater and easier to manage.
@@LeilaGharani I found it troublesome because when I use xlookup and when the data in lookup sheet is replaced the lookup colum will always be moving to few columns ahead so I had to go back to use vlookup for those scenario
awsome
thank you Leila
Gorgeous and a very smart woman you are Leila ;) , thanks for all your great videos ;)
Our pleasure :)
hy mam,
in vlookup we can take results from multiple columns at the same time is that possible in xlookup?
Great 👍
Thank you!!!!
I Exclusively Use Xlookup !! but I want to know how Xlookup can replace sumif fingers crossed.
I love your videos!!!!
Thank you for recap old 🗝️🗝️
Thanks for watching!
Someone told me to use this technique for creating sub ledger from ledger. But this is so difficult to understand and apply. Im not sure I'll ever be able to use it
it seems the lookup function will make life much easier rather than writing lots of "if" functions instead.
Can I use xlookup to create bin location for my inventory?
Sometimes I don't understand a thing, but I still watch because it's like watching a superhero do his thing...
😁
Genius solution.
Thank you 😊
Hi can you help with a solution for drop down list were someone cannot paste on those cells
If we lock the cells we won't be able to use the drop down as well so how to overcome this problem
That is just amazing
IndexMatch works much better. Lookup can't even do a proper ID to Name match function between two columns from a drop down or even not a drop down. Keeps screwing up the numbers. Even if I put dollar signs. IndexMatch does it perfectly.
Omg.... Why have I not been using F9 stuff in the formula bar before??? The amount of headaches that would have saved me, lol
Thank you for your interest in spreading the flag please help me understand which has been used using xlookup I understood how it was done but I need to know from you that the result appeared thanks
Thanks for watching. You can download the file I used and check on your side. You'll find the link to the blog post in the description of the video.
@@LeilaGharani Thank you for your attention and respectable reply, May God bless your age. I have a request at your presence. I am a cost accountant who needs a sheet of excel to monitor inventory using incoming pricing. First, it is spent first in stock because I have many codes and the inventory system has a limited period of time. I need equations to implement in my eccesiliate sheet, monitoring the incoming stock. First, it is possible, if you please, an email to send you an oxil sheet to apply the equation to it because seriously, you need your help in helping you. Dr. Lilly, thank you.
The great explanation, I can't use Xlookup function because I am using Excel 2016. It can be solved using Lookup function
Hello Leila,
Is your courses available in French. Or are they translated to french. if not, where do you recommend to refer friends to watch excel content in french since my friend loved your courses alot but he is french educated
Sorry, no French. But our courses have English caption. Maybe this helps.
Funny: for several videos there are two words that sounded like one wore and that puzzled me, "press enter" I was only hearing this as "presenter"
😁
Anytime i want to learn something new it’s Leila to the rescue.
🙌🏼
Can i use this for my karaoke song book?
Good
Dear Leila,
May i ask for an advice?
I create charts with thousands of rows and dozens of columns that are assembled from multiple files - sometimes it happens that single values need to be deleted, and finding it inside HUGE table is impossible .
Is there a way to indicate data's row/column from a table by pointing to a point on a chart? to be exact, to highlight a cell from the data extracted from the plot.
I have searched many ways, but unfortunately have not found a solution to this problem. The only information I was able to get was the coordinates of the point, but I want to highlight the exact row and column.
Do you have a video to do lookups on tiered pricing by quantity? For example I have one item with 3 prices depending on the quantity. So it would first look at the item # and then the quantity 1000
You might consider concatenating item numbers with a deliminator and the minimum qty. Then do a range lookup.
Sumproduct would work nicely for a small dataset but there is too much overhead for thousands of rows..
I've done tiered pricing in Access using the where clause to join tables. It worked very well.
Instead of sorting the table so to find the correct ID, we can use LOOKUP(1;1/EXACT(A2;$M$2:$M$11);$K$2:$K$11)
I've tried for years to figure out what the algorithm is that Excel is using when it returns funky answers when doing an approximate match AND when the lookup vector is NOT sorted in ascending order. Just curious, has anyone figured that out yet?
why we use -1?
It's so above my level ,I didn't quite understand it 🥺
It is a confusing function. If you need lookups, I'd go with XLOOKUP (if you have Office 365) or VLOOKUP for older versions.
It's interesting that the arguments are described as VECTOR instead of RANGE. This is very accurate because vectors are ordered since they have a origin and direction.
Funny, first thing I think of is that the lower the income the higher the percentage of bonus should be.......
Obrigado 🥰🥰🥰🥰🥰🥰🥰🥰🥰
Yes
Ur Awesome 😎
Hello est'il possible de caluler l'heure comme ceci sur exel : ( 41:20:00) = 1j 17:20:00