Excel Magic Trick 1102: VLOOKUP with Three Different Tables to Rank Movies, VLOOKUP & IFERROR
Vložit
- čas přidán 20. 04. 2014
- Download Excel Start File: people.highline.edu/mgirvin/Y...
Download Excel File: people.highline.edu/mgirvin/Ex...
See how to string three VLOOKUPs and IFERRORS together to do a lookup from three tables in order to rank movies.
Excel Magic Trick 1102: VLOOKUP with Three Different Tables to Rank Movies, VLOOKUP & IFERROR
Awesome, you make it look so easy... and it is! just so you know my excel skills have increased thanks to your videos... and your book! "512 Excel Mysteries Solved" I even purchased your "Pivot Table" book... Thanks!!
I did not write those books. Bill Mr Excel Jelen wrote those!
Muito bom Mike!
Before I discovered your CZcams channel, I didn't even know what conditional formatting was. Now, thanks to dozens and dozens of hours of watching your videos over the past several months, I actually landed a job that relies solely on my Excel skills. Thank you for taking the time to share your insights!
Also, I'd like to share a formula that I came up with for this scenario:
IFERROR(INDIRECT(ADDRESS(SUMPRODUCT(($A$1:$J$6=A8)*ROW($A$1:$J$6)),SUMPRODUCT(($A$1:$J$6=A8)*COLUMN($A$1:$J$6))+1)),"")
Glad the videos help! Thanks for the formula!
great sir------------
Glad it is great for you, Khalid : )
cool. thanks, Mike. Wonder if there is a way to name the lookup tables sequentially and use an array formula somehow ... well if anyone can figure that out, it would be you ;)
btw, "" is ZLS (Zero-Length String). Null means the absence of a value. ZLS is a value. Access has a function called NZ for returning a value instead of null but Excel doesn't. In Access, you can test for Is Null and IsNull(). In Excel, you test a cell for "" to see if it is empty but that doesn't mean "" is null ... just Excel's way for you to test for nothing ;) When it comes to functions, Excel has a plethora more than Access built-in without referencing libraries but Null is something Excel does not handle well ... imo of course ~ Warm Regards, Crystal ~ have an awesome day ~
I have been search for a way to "mash" three tables together, one on top of the other in Excel for years, but I have not seen one...
As for "", I have heard it referred to as Null Value, Empty String or a Null Text String. I have not heard Zero-Length String. I just googled it and see that this is common in databasing and SQL. I was part of a big thread at the Mr Excel Message Board many years ago and the consensus was to use Null Text String. I like Zero-Length String, though. This matches how I teach Null Text String (which I just happened to be teaching today in the classroom), because we use ISTEXT function to prove that it is text and the LEN to prove that it has zero length...
When you say "" is ZLS (Zero-Length String), what sort of authority is
When you say "" is ZLS (Zero-Length String), is this the preferred noun for a text string with zero length? I know in the Excel world, there seems to be confusion about this. Or maybe it is just me :)
Thanks Crystal, the new term is REALLY helpful, especially since the noun matches how I teach it.
ExcelIsFun you're welcome, Mike. Many people misuse terms. In Access, Null has significance -- hey that is like saying 'nothing' has significance ;)
re: "mash" three tables together" -- what about naming ranges Lookup1, Lookup2, Lookup3. Then evaluating the combination of beginning of range name and number from array? Well there are a few kinks such as getting it to stop ;) In your example, each value being looked up was only in one place.
Mike - I have a lookup that Index/Match should perform nicely with the exceptions of a couple of wrinkles. I am consolidating income statement line items from a single store template. As I open new stores across periods, the I/S line items layer into the model. The line items change on a monthly basis. I need to layer in multiple opening across different periods AND sum in a single formula so that by the end of the model projection period, the line items in the consolidated I/S include all 100 stores figures. The template income statement for a single store uses generic headings for each period such as Month 1, Month 2, Month 3 etc for 120 months whereas the consolidated income statement uses actual dates across the forecast period. How can I accomplish the consolidation? match will not work because the template period headings (the generic ones) do not "match" the dated period headings of the consolidated periods. Also, how do I write the lookup so that the consolidated I/S incorporates and Sums the individual stores values at the appropriate point in time (once the store is scheduled to open)? Lastly, I have a tab for the market development forecast which is a series of 1s and 0s that represent the dated period in which a single store is projected to open.
working A LOT no time try:
mrexcel.com/forum
ExcelIsFun Thanks for reply
Another way with Index :)
=INDEX($B$2:$I$6,LARGE(IF($A$2:$H$6=A8,ROW($B$2:$I$6)-ROW($B$2)+1),1),LARGE(IF($A$2:$H$6=A8,COLUMN($B$2:$I$6)-COLUMN($B$2)+1),1))
Yet another amazing solution! Thanks!
ExcelIsFun
Thanks :)
the easiest way I can think of would be with VBA, a one-liner
Public Function FindFavMov(R As Integer, mRange As Range) As Variant
FindFavMov = mRange.Find(R, , , xlWhole).Offset(, 1).Value
End Function
and call it like this
=FindFavMov(A8,$A$2:$H$6)
Yes, your formula works. I had already done something similar to this formula, but if the user moves the tables to another location on the spreadsheet, this formula may not work as it should. In the case of the video formula it would work if the user decided to change the location tables. I made a huge formula using the "Choose ()" function, to search for ranking with repeated positions and allowing the table to be moved to another place without the error in the formula, it was very interesting! But it's a lot of work
hello Michael, here is my VBA function in this case:
Excel VBA function - Glookup (lookup to 3 sheets) ENG
I would like to here your opinion about it. thanks in advance
P.S. sorry about my English it is not my native language :))
Thanks for the VBA tip!!! :)
But I do not have an opinion on it as I am not good with VBA... ;(
For VBA opinions, try mrexcel.com/forum
ExcelIsFun Oks! thanks for the advice Mike!!!