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 TH-cam.
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.
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
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)
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 😅
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
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.
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.
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.
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
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!
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 :)
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 ...
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)))))
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?
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
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
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.
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
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.
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.
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?
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
Great Video! Anyone knows how to do the following? I have a vlookup function in A1. It brings up an IP Address of a certain place when a number is put into a different field. Each number has a different IP Address. How can I have it so that B1 shows the next IP increment. Example: A1 shows 55.55.55.55. I want B2 to show 55.55.55.56 automatically without programing. What formula can I use in that cell?
1. Please use cell C1 as a helper cell. In cell C1, please write: =SUBSTITUTE($A$1,".","")+1 2. In cell B1, please write: =LEFT(C1,LEN(C1)-6)&"."&MID(C1,LEN(C1)-5,2)&"."&MID(C1,LEN(C1)-3,2)&"."&RIGHT(C1,2) Hope this helps.
@@ankursharma6157 I liked that answer, but IP Addreses actually get larger like so... NNN.NNN.NNN.NNN. I should have used this as an example. Can it be somehow implemented in your answer?
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.
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 TH-cam.
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) 🤗
😭😭😭You make me fall in love with Excel everytime you post a new video. Thank you.
Nice to see old school LookUp getting some attention and love.
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)))))
You explained the logical part beautifully. The simplified lookup function in office 365.
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!
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 SORTING part is the think that I was looking for. Thank you.
You’ve increased my confidence so much. I appreciate you and I hope all is well
Xlookup is a gamechanger...
It really is!
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.
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
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)
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 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 😅
love the "attention guy" !
😁
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 loved the BOM reference!
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
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'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.
I always look forward to seeing your Excel videos. Thank you so much.
Thank you for your support.
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.
Really well explained, using great examples!
Loved this video Leila!
And as always........ Looking great whilst doing it 🥳
Thanks
That's awesome! Thank you Leila!
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.
So good material 🥰 I'm in love 🤓
Salaam Khaneem Leila, could you please upload full class of excel. I want to lean from beginning please upload it. Tashakor!
Nice, Now I want to see it with Grouping of the levels, How can you make the grouping work?
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.
I love your teaching.....
Excellent video! thank you Leila!
Every time Iam trying to learn something from your videos..i end up staring at you and forget what you said..😳
Great technique, I have been looking to build something exactly like this to a parent hierarchy problem 😀
Thank U👍
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
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
That was exceedingly useful and well explained, thanks!
Another great video lesson!
Greatest Help to me...so much Thanks to you !
Glad it's helpful!
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 sharing this😊❤.
How do you dynamically get the row and column headers of a maximum value in a table?
Thanks so much
everyday, excellent for you
I never knew lookup function. Always used vlookup a lot.
Thank you for the BOM example. How would I also apply the% of each component depending on the parent?
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 :)
could u make an other of this solving in diffrent way.
Very well explained. Thanks.
Glad you liked it!
Sometimes I don't understand a thing, but I still watch because it's like watching a superhero do his thing...
😁
Thanks for the share.
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.
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)))))
awsome
thank you Leila
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?
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
Anytime i want to learn something new it’s Leila to the rescue.
🙌🏼
Thank you for recap old 🗝️🗝️
Thanks for watching!
Thank you!!!!
Genius solution.
Thank you 😊
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.
it seems the lookup function will make life much easier rather than writing lots of "if" functions instead.
Hi there! I'm looking to compare two products list and their pricing. Which one of your videos do you recommend me watch?
Gorgeous and a very smart woman you are Leila ;) , thanks for all your great videos ;)
Our pleasure :)
I love your videos!!!!
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
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.
Can I use xlookup to create bin location for my inventory?
Hello Ms. Please tell me how to remove open password in excel .... Great help .... Open password is not the protection password
I Exclusively Use Xlookup !! but I want to know how Xlookup can replace sumif fingers crossed.
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"
😁
hy mam,
in vlookup we can take results from multiple columns at the same time is that possible in xlookup?
Great 👍
why we use -1?
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
Can i use this for my karaoke song book?
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.
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.
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 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.
Yes
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.
Great Video!
Anyone knows how to do the following?
I have a vlookup function in A1. It brings up an IP Address of a certain place when a number is put into a different field. Each number has a different IP Address. How can I have it so that B1 shows the next IP increment. Example: A1 shows 55.55.55.55. I want B2 to show 55.55.55.56 automatically without programing. What formula can I use in that cell?
1. Please use cell C1 as a helper cell.
In cell C1, please write: =SUBSTITUTE($A$1,".","")+1
2. In cell B1, please write: =LEFT(C1,LEN(C1)-6)&"."&MID(C1,LEN(C1)-5,2)&"."&MID(C1,LEN(C1)-3,2)&"."&RIGHT(C1,2)
Hope this helps.
@@ankursharma6157 I liked that answer, but IP Addreses actually get larger like so... NNN.NNN.NNN.NNN. I should have used this as an example. Can it be somehow implemented in your answer?
@@ankursharma6157 Here is another example: 10.41.5.112 -> 10.41.5.113
I am using 365
Omg.... Why have I not been using F9 stuff in the formula bar before??? The amount of headaches that would have saved me, lol
Good
The great explanation, I can't use Xlookup function because I am using Excel 2016. It can be solved using Lookup function
Obrigado 🥰🥰🥰🥰🥰🥰🥰🥰🥰
We have vlook up why need to use lookup
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)
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.
Hello est'il possible de caluler l'heure comme ceci sur exel : ( 41:20:00) = 1j 17:20:00