skip to Main Content

I believe that written posts are better than videos most of the time – written posts allow people to skim or read in depth – or surreptitiously read a blog post during a meeting without tipping everyone off that you’re not paying attention Smile Written is also better for search engines.

I’m not changing my mind about that.  But there are some things that you just can’t show in a written post.  This one falls under that heading.

The other thing different today is that rather than show off some clever or powerful technique, I’m “going retro” and focusing on something very fundamental about PowerPivot, and how PowerPivot actually makes Excel a less scary place, and not just a more powerful place.

That’s what this short video is about. Oh, and Star Wars. And my sister, who I recently discovered is a Pivot Pro but hadn’t told me.  That traitor!


For the benefit of the search engines in our lives, let me explain that VLOOKUP is used for merging or blending two tables of data in Excel.  It’s the original mashup tool!  But it’s a scary function with arguments like “lookup_value” and “table_array” and my personal favorite “[range_lookup]” which really should be named “[always_set_me_to_FALSE]”.

It turns out that VLOOKUP is harder for some people to use than pivots themselves!  I’ve long viewed PowerPivot’s relationships capability as a far superior alternative to VLOOKUP, but purely from a speed and elegance standpoint.

It turns out that PowerPivot is also a much friendlier and more approachable alternative as well.

Bill Jelen was right about that.  But he’s still wrong about compact axis in pivots.  (Let’s see if he picks up on this bait).

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 24 Comments
  1. “[range_lookup]” should NOT really be named “[always_set_me_to_FALSE]”. It should be named “[always_set_me_to_FALSE_unless_the_workbook_has_many_formulas_and_performance_is_impacted_because_of_this_comma_in_which_case_sort_the_lookup_array_and_set_me_to_TRUE]”.

    1. Yes. May I extend this to the following?


  2. I couldn’t contain a laugh at [always_set_me_to_FALSE]. I did a presentation vlookup to a large group of coworkers with a long explanation of what [range_lookup] means, and when to use which option. My final statement was: “Because none of the last five minutes made any sense, just set it to FALSE”.
    You would be amazed how many people will show up to eat their lunch in a conference room if you offer to teach them Excel tricks.

    1. Shocking that VLOOKUP isn’t more broadly used huh? 😛

      True story: I once produced a laptop at a party and started teaching VLOOKUP over drinks. You would be amazed at how many people flee from you at a party when you do that 🙂

      That happened within the past year 🙂

      1. Whereas if you had started teaching PowerPivot, the girls would have been tearing at your clothes? 🙂

        1. I don’t think my partygoers distinguish between PowerPivot and VLOOKUP. It’s all nerdery to them 🙂

    2. At my last corporate job we did Brown-Bag-Lunches where someone presented. I did Excel sessions, but my conclusion was that people attended just so that they had a comfy room away from the office where they could eat their lunch.

  3. My experience has been similar to your sister’s; I have used VLOOKUP sparingly but have written calculations in pivot tables. But of course, now that you have enlightened me to the powers of powerpivot, this is the path I am starting to pursue to solve these problems. But thanks for the VLOOKUP review…and reminder why powerpivot is so much better.

  4. Your VLOOKUP formula is so Excel 2003 🙂

    if you convert both your ranges to Excel tables (and give the table names like Years and Movie_Grosses), then the VLOOKUP formula becomes much simpler.


    You get the following benefits:
    – The overhead is lower because you don’t need to bring the PowerPivot data back into Excel proper.
    – The formula auto-completes to complete the column in the current table
    – Whenever more data is added to the lookup table, the formula will adjust and consume the additional data. No need for absolute cell references!

    Given what I stated above, I do like PowerPivot for mashing data from different data sources.

    1. Vincent I couldn’t agree more! While I had little to do with the actual details and design, I was in fact the person who caused the Table feature to happen. The idea of “Structure” was the topic of a whitepaper I wrote at Microsoft while we were planning the Excel 2007 release, and included the ideas of structured reference, autofill, etc. Others took it from there and did a great job with it.

      Thing is, very few people adopted it when 2007 first came out. It’s nice to see now that people are using it and throwing it back at ME. 🙂

        1. You need many autographs. Joe Chirilov, Charlie Ellis, Juha Niemisto, Andrew Becker, Matt Androski, and about 10 other people who I’m omitting due to poor memory 🙂

          All of those people had more to do with the feature than I did. My part was the easy part.

          Quite stoked to hear you love it this much.

          1. Can I blame you because I ca’t use structured references in conditional formatting?

    2. Ditto. you beat me to it.

      The other problem I have with using powerpivot in this particular situation is that it is gratuitously unforgiving when working with data that is not perfect. As soon as I have a table that has a year listed twice (even if the related attributes are the same) Powerpivot rejects it where vlookup doesn’t. This kind of nuance is commonplace in regular business data, and while I always make sure I know what the data is doing, sometimes duplicates just don’t hurt anything, and that makes vlookup a faster and more flexible option for the fast and gritty.

      Vlookup also happened to be the first formula I learned after sum(). It was my gateway formula.
      (“That’s handy. I wonder what else Excel does that I didn’t know about.”)

  5. 1. “You have to put false for the last part – just trust me on that”.
    2. “Why?”
    3. [explanation]
    4. “I still don’t understand”
    5. Go to 1.

    -Pretty much every time I’ve explained vlookup to somebody.

    But no joke, I was demonstrating to a colleague the other day how powerpivot reduce your two hour vlookup project into something that could be done over a cup of coffee. I think she got it, but she was a little put off by the diagram view. “It looks like access” which for somebody who spends 40 hours a week doing vlookups, it’s very ‘dragons be here’.

  6. Cool vid….would just add that vlookup true is still very useful for “banding data”. A data set, a banding table, a vlookup true column added to the data set as an added column, plus a simple pivot table is a great way to stratify or band data. Way better than nested and sumifs. Of course I now do all this in power pivot but for all my staff who have 32 bit machines…..

  7. Really top class presentation Rob. I think the scarier thing is how easy that was and how urgent it is now for Excel 2003ers to catch up with these 2 new realities, structured references and PP and introduced them to their work.

    You need a another table of movies that just dam well should be there no matter what they grossed, like The Great Escape!

Leave a Reply

Your email address will not be published. Required fields are marked *