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 RU-vid.
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) 🤗
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.
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 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 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.
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 😅
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.
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
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)
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 ...
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!
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?
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
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
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
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
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.
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.
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
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)))))
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.
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.
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.
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?
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?