Excel Magic Trick 1102: VLOOKUP with Three Different Tables to Rank Movies, VLOOKUP & IFERROR

preview_player
Показать описание

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 

excelisfun
Автор

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!!

fabianfranco
Автор

Before I discovered your YouTube 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)), "")

clabzzz
Автор

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 ~

LearnAccessByCrystal
Автор

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.  

coreyhansen
Автор

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 :))

giorgikavtaradze
Автор

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))

livio