R vs Excel? VLOOKUP vs INDEX/MATCH? Enough with the False Dichotomies!

Eager to learn something new over the holiday weekend, I checked my email for the latest from the business analytics blogosphere.

I was quite disappointed to find posts of this nature:

  • VLOOKUP vs. INDEX/MATCH: Which is Better?
  • Let’s Finally Decide This: Python or R?
  • Why You Should Use R Instead of Excel

These “either/or” posts are unhelpful, false dichotomies. Here’s why.

Tools Should Complement, Not Substitute

R, Excel, Python. VLOOKUP, INDEX, MATCH. These are all excellent, popular tools. I like to say that VLOOKUP is like the duct tape of Excel, while PivotTables are WD-40. 

These tools are meant for very different purposes. Used in tandem, they become even more powerful. A good analyst knows when to use the right tool, and keeps many tools on hand just in case.

Sure, maybe VLOOKUP is not as robust as INDEX/MATCH. But it works! Sometimes you don’t need Gorilla Glue when duct tape will do. 

This is why I loved Oz du Soleil’s response to the VLOOKUP vs INDEX/MATCH debate. Not only did Oz offer more alternatives still between the two, he made it clear that these are all tools.

Further, R is an awesome data processing and analysis tool. But for presentation and visualization, Excel makes a lot of sense. Python and R are both open-source and crazy powerful. I find myself pulled toward R for its statistical capabilities, but will use Python for some batch-processing tasks.

There is no “perfect” data tool. Each could be the best choice, given the limitless circumstances an analyst might find himself in. Which leads me to the next point…

You Have No Idea…

Working with data is complicated. That’s why we have so many tools at our disposal. There are so many quirks and nuances in all of our datasets and workflows. While we can speak generally about what tool might work best given certain circumstances, actual application can vary tremendously. James Altucher makes a great point about advice:

This is why I base my training videos on things I have done in the workplace, and include my real-life examples. I don’t know everything about your data or circumstances. I can only tell you my opinion about what has worked for me. 

You might have noticed I’ve been drifting toward R posts lately — because that is the tool that’s been better for me these days. I will not be telling you to use R instead of Excel. I won’t be contributing to a VLOOKUP vs. INDEX/MATCH opinion roundup. 

I will keep writing what has worked for me and what I think might help others.

Subscribe to my mailing list.

Comments

  1. Good blogpost. There’s much false dichotomy going around but the task and needed outcomes are paramount.

    Back when I had to calculate bonuses for the reps in a call center, not a single one refused their bonus check and claimed the amount was right, but my Excel skills were poor.

    At that point, I didn’t even have VLOOKUP available to me. I was copy-pasting and dragging cells around. It was tedious and slow, but it got done. So …

    VLOOKUP, INDEX/MATCH, Left Outer Join, or manually drag cells around.
    – Which is better?
    – The one that [bonus calculation person] uses to get the bonuses calculated accurately and submitted to payroll on time.

    Thanks for helping cut through the hype.

    • George Mount says:

      Right on. I prefer the tool that gets the job done. And no job is the same. Circumstances vary so much, there is no absolute “best way” to do something.

  2. I would write a refutation of this post, but am too busy working on my article “Hammer or Crescent Wrench: Let’s Settle Which is Better Once and for All.”

Leave a Reply

%d bloggers like this: