Cult of the Flying Spaghetti VLOOKUP

Adam VLOOKUP

Sumit Bansal’s post VLOOKUP Vs. INDEX/MATCH – The Debate Ends Here! sparked some great discussion on the merits of VLOOKUP vs INDEX/MATCH, including at Oz du Soleil’s lighthearted rebuttal at The Anti-VLOOKUP Crowd Is Out In The Streets Again!

I especially love Peter B’s comment at Sumit’ post:

My opinion is that VLOOKUP and HLOOKUP are simply over-specialised legacy functions and Excel would be all the better for ‘pruning’ them out. I do use VLOOKUP occasionally when I have a 2-D range; the search array happens to be on the left; I only wish to return a single field; I am sure the data is clean and the match will always succeed. Despite that, I think the value they bring to the bloated zoo of Excel functions is not worth their keep.

Of course, NOTHING can ever be cleaned out of Excel, for good reason…otherwise all the millions of complex black-box spreadsheets that continue to function just fine long after the person who constructed them moved on to another task, job, or incarnation will break. Not to mention all those fantasy football spreadsheets. MS has backwards-compatibility issues that are beyond belief really.

At the same time I agree with Bob Phillips’ point at Sumit’s post:

The biggest selling point to me is that VLOOKUP is easy to teach to people, and it sticks, INDEX/MATCH less so.

But I disagree with Bob’s point that VLOOKUP can be/is just as flexible as INDEX/MATCH, merely because we can do stuff like this with it:
=VLOOKUP(“z”,CHOOSE({1,2},$B$1:$B$10,$A$1:$A$10),2,FALSE)
=VLOOKUP(“g”,$D$2:$H$15,MATCH(“Qtr2”,$D$2:$H$2,0),FALSE)

Just as flexible? Maybe, if you bend it double with brute force. Just as fast after you’ve made it just as flexible? Not likely. Any more understandable than the INDEX/MATCH equivalent? Not in my experience.

In fact, I feel a rude joke coming on:

Young analyst with unlit cigarette in mouth, having just consummated his first VLOOKUP: Has anyone got a match?
Analyst of distinguished years: Yes. Your VLOOKUP and my arse.

If MS were designing Excel from scratch – and I was on the committee that was deciding whether to include a dumbed-down function to do a subset of lookups based on hard-coded input parameters and a fixed data layout – then I’d make a case for not including it. Not just because of those quite reasonable complaints, either. But also because of evolution. A user that is forced to learn INDEX and MATCH due to lack of suitable alternatives is be better placed to evolve into a higher Excel lifeform than one that hasn’t looked beyond VLOOKUP.

(I’d make an exception if a major competitor – say Lotus – had a VLOOKUP function in their beast. But only in that specific case.)

Formulas remind me a bit like DNA: just by stringing a few different base-pairs together in the right order, you can build a mouse. Or a Human, with a few extra tweaks. Similarly, with a few good formula combinations under your belt, you can conquer most problems you’re likely to come across. INDEX and MATCH are not just formulas in their own right, but are the formula equivalent of DNA basepairs: they give users a peek into other formula ecosystems that they can gradually spread into and colonize. VLOOKUP ain’t one of those base pairs. It’s Neanderthal.

Hey, don’t get me wrong: I’m fine that it’s in the fossil record. I’m happy enough to have one in my spreadsheet, just as I’m happy enough to have an appendix that doesn’t burst.

23 thoughts on “Cult of the Flying Spaghetti VLOOKUP

  1. The debate continues. LOL!

    I’m glad you mentioned Bob Phillips’ point: VLOOKUP is significant in the learning of Excel.
    And why this means so much to me is because of the students and their learning. But it goes further than that: for a lot of people VLOOKUP is all they need. Their data isn’t in large volume and isn’t overly complex.

    So often, someone comes to me with an issue that takes up many hours per week. They might need:
    – Text-to-Columns
    – A tutorial in Absolute/Relative References
    – IF
    – VLOOKUP

    BOOM! And they’re back to their real job. It’s only developers who think VLOOKUP is a waste.
    The coordinator at a nonprofit who’s managing 200 kids, field trips, lunches, permission forms and parents has enough to deal with. He’s the one who needs VLOOKUP unless there’s a really doggone good reason to bring him into the syntax of two nested functions. And there are way more of him than us developers.

    I like how you describe DNA. That’s exactly how I see the beautiful entirety of Excel. And I’d vote to keep the VLOOKUP molecule.

  2. Hi Oz. Good points. Re Their data isn’t in large volume and isn’t overly complex.…well, it isn’t in large volumes nor overly complex until it is. Sooner or later, they will have to handle more and more data. They might even start to feel confident that they can handle large data. And they know it’s the exact same VLOOKUP that they’re going to use, so it’s not like there’s any barrier to them pointing a VLOOKUP at a hundred thousand rows as opposed to just a hundred. So those ranges will get bigger. Gradually perhaps…but I’ve never seen spreadsheets get smaller and less complex. Only bigger and more.

    And when their data and/or requirements starts to grow in volumes – and they start to do more and more complex things as they grow their capability – they’ll continue to use their outdated paradigm, because that’s all they know. If they’re lucky enough to know that that these things called ‘Help Forums’ exist, then maybe they’ll finally post a question there, after years of ignoring niggling, increasingly frustrated symptoms. And if they’re lucky, when they do post a fairly non-specific question like “Excel Can’t Handle It”, hopefully they’ll get someone like us that helps them address underlying symptoms, rather than some well intentioned deckchair shuffling.

    Re It’s only developers who think VLOOKUP is a waste.. True. But only because it’s only developers that know this stuff. We need to pass this knowledge from developers to users, because there’s more users than developers, and so users experience the bulk of the pain. We need to give users the knowledge to reclaim fast spreadsheets. We need to help users become ‘developer grade’ in those things they do often that could be done much, much better with just small tweaks in their behavior, and that save them much, much grief.

    I’m working on a little manifesto that covers this, called Excel for Superheroes and Evil Geniuses.
    BookCover

  3. And the fun is that we (people who consider themselves Excel devs) can then make a livelyhood of helping people who have gotten into trouble using many VLOOKUPs causing their models to grind to a calculation halt :-)
    Dozens of columns of VLOOKUPs getting data from the same row of the same table. Why is my spreadsheet slow? Well, because you made it slow.

  4. Good points Jeff. I am one of those who learnt Vlookup and Index/Match at the same time, and with more flexibility in Index/Match, I got biased towards it. I am sure when someone is learning Vlookup, he/she thinks it’s a complicated formula with many arguments. May be it’s time they feel the same and learn Index/Match as well.

    Note: The link to Excel for Superheroes and Evil Geniuses is broken.

  5. Jan Karel: The sad thing is that too often, people don’t know they’ve got an easily solvable problem. They just think that Excel can’t handle their genius, and so they switch to manual.
    Sumit: I wish whoever taught you had taught me. My thoughts exactly on VLOOKUP…sure, it might be less complicated for some people than learning INDEX/MATCH…but surely only marginally less complicated.

    Link fixed…cheers for that.

  6. I don’t disagree that there’s a time for VLOOKUP. Once, I inherited a spreadsheet that had at least 1000 VLOOKUPS in it and sometimes it wouldn’t even open.

    My point is that all of this is driven by context. The anti-VLOOKUP case is often made by painting a particular context: when the data gets huge; when you need to look left instead of right. Ok. No disagreement. But you have to be careful about the assumptions that you smuggle in. In my blogpost I presented 3 scenarios and mentioned that this whole debate is a false dichotomy.

    The choices are: do nothing, VLOOKUP, INDEX/MATCH, manual

    How do we answer the question of which option is best? If there are 2 items to retrieve, go manual: COPY/PASTE.

    If a person is trying to complete a list and they’re going to Paste-As-Values when it’s done: VLOOKUP.
    If your look up value is in the middle of the lookup range and you need to look both left and right: INDEX/MATCH. No question.
    If you’re trying to complete a list of fax numbers that you’ll never send a fax to: leave it alone.

    It’s all context.

    And one thing has gotten clear to me after teaching a lot: not everyone is trying to master Excel. The travel agent is trying to get people onto cruise ships. So, by giving them what they need–no more and no less–it’s a show of respect for what’s important to them. And believe me, I warn people if I see a problem.

    I went into a small company and the owners wanted me to teach them some things that a person could only get if they’re elbow-deep in data every day. We eventually agreed that training wasn’t a good option because the guys wore too many hats. Everything from finding new business to driving the forklift.

    In that case, neither VLOOKUP nor INDEX/MATCH were worth much.

    Again: it’s the context.

  7. Agreed, context. Only problem is, you can’t foretell in what context people will be using things in future. This reminds me of the OFFSET/INDEX Volatile/Non Volatile debate, where my advice is summed up at a blog post I did over at Chandoo some time back that you commented on, Oz. My salient point was this:

    Too much reliance on volatile functions *might* trigger large parts of a model to be recalculated needlessly. But it’s worth remembering that this is only going to be noticeable in particularly big spreadsheets. However, if you know of an alternative formula combination that does exactly the same thing as a volatile formula, then I’d suggest that you get into the habit of using that instead whenever you can. That way you won’t inadvertently have issues when it really matters. And I’d suggest that if you don’t have much experience of functions and performance, then perhaps it’s safest to simply err on the side of caution and steer clear of volatile functions altogether.

    Not only do I see little down side to avoiding volatile formulas, but I see a significant upside: I’ve seen plenty of large models built by the likes of the big 4 accounting/consulting firms that make heavy use of volatile functions, and that consequently have recalculation times so long that they are effectively unusable. Stripping out the volatile formulas from these models has resulted in delays from data entry falling from upwards of two minutes to well under a second. Not to mention that users can now work on other files while these models are open, without fear of triggering an avalanche of unnecessary and pointless recalculation. Had these model builders known to avoid volatile functions, they would have saved users a lot of grief.

  8. Oz,
    Do you really believe that Vlookup is easier to learn or is just easier to teach? Have you ever tried to teach Match/Index first?

    Thanks

  9. @ Jeff, not doubt about it!

    It would have been great if I had learned the concept of match first, then learn VLOOKUP would have been a lot easier. At the end match is the strongest part of Vlookup, isn’t?

    Regards

  10. WOW! I don’t recall the debate over at Chandoo’s house. This thing is getting around.

    Because you emphasize the word *might*, we probably agree more than we disagree. When you talk about the Big 4 accounting firms, you’ll get no disagreement from me. And it’s the rare situation where I even leave VLOOKUPS in a live workbook. A huge spreadsheet with a lot of volatile functions is indeed a sign of poor development. Even if it’s not huge right now but continues to grow, it’s a ticking time-bomb.

  11. So did any of you wonder about the significance of the title of this blog post. It’s a nod to another interesting debate. (Not that I intend to start a debate on that other debate here. I’m just foolin’ around)

  12. Elias,

    I think it’s easier to teach someone the components of VLOOKUP:
    1. What do you want me to look for?
    2. Where should I look?
    3. What do you want if I find it?
    4. Exact match, or not?

    Nesting MATCH inside INDEX means explaining the syntax that we’re replacing one component of INDEX with a function called MATCH. Where the context warrants it, we have to suck it up and get through it. Where VLOOKUP works, we go with it.

    Again, I’m typically dealing with smaller entities. A guy was trying to match up 3 lists of maintenance records of several vehicles and merge them into a single clean list. They were all less than 100 rows each. VLOOKUP, copy, paste-as-values, look for any duplicates.

    We, as power users, could do that without thinking. But he was trying to do it manually and it wasn’t going so well.
    It was an infrequent need on a dataset that wasn’t growing very fast.

    Elias, one thing that I do agree with you on is that I would like to have known MATCH way before I ever used it.

  13. Yeah! That was a hilarious title.
    I did think of the Flying Spaghetti Monsters but I don’t know what they are. Was that a movie?

  14. Oz,

    Beside point 3 is not the same way that you would teach match? Then with another small effort you could leave a student with 3 functions instead of 1. I believe we tend to think that Vlookup is easier to teach/learn because we learned it first. :)

    Regards

  15. Jeff, thanks for drawing my attention back to your title, otherwise I had assumed it was just you being you. The Spaghetti Monster cult is great, and I’m pointlessly proud that the founder lives just down the freeway from me.

    As to the main topic of VLOOKUP versus INDEX/MATCH, I don’t think it matters. As much as I love Excel, I think of it as a vast unstructured wilderness where most users do whatever works until it breaks, and then figure out how to make it work again, or not. The idea of somehow guiding them to what they need before they need it is laudable, but generally ineffective. The best we can do is leave directions out of the wilderness for those in need, as Sumit did. Then they’ll do a search on “Vlookup to the left” and find many helping hands, such as yours, to pull them out of the morass.

    I think :)

  16. Doug: Spot the difference:
    Theirs
    Mine

    In fact, the cult has the perfect blend of art and science:
    pirates

    I love your prose:

    … a vast unstructured wilderness where most users do whatever works until it breaks, and then figure out how to make it work again, or not.

  17. Elias, I don’t know the point you’re trying to make.

    I’ve acknowledged that INDEX/MATCH has its place and that VLOOKUP has weaknesses. Excel and data management are so much bigger than this. There are over 300 functions and countless features, and at least 3 ways to do almost anything–including doing everything in VBA.

    This is like a bunch of jazz musicians sitting around discussing Locrian mode. Meanwhile, the fans are clamoring for Lady Gaga. Lady Gaga is talented and delivers what FANS want and can feel. Jazz musicians play music for jazz musicians.

  18. Oz, My only point is that Vlookup is not easier to teach/learn than Match & Index. It is just an easy statement to justify the preference of Vlookup. I bet 50% of the Excel forums question won’t exist if we push the use of Index/Match instead of Vlookup.

    Regards

  19. Very interesting debate VLOOKUP Vs INDEX/MATCH. During my early days, to make Excel to look left, I made col_index_number a negative. It didn’t work. But now still learning, I doubt that I have to make even the Excel range to look Right to Left, if I have to do what INDEX/MATCH does. If Excel can be forced to see a “reverse range”, B:A instead of current A:B, we have chances of finding values on the left. This will bring everlasting glory to VLOOKUP!


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.