VLOOKUP and INDEX MATCH

/, Power BI, Power Query/VLOOKUP and INDEX MATCH

VLOOKUP v INDEX MATCH – You decide

Let’s think of VLOOKUP as a screwdriver and INDEX MATCH as a power drill…

wait…wait…. I’m not saying INDEX MATCH is faster than VLOOKUP that isn’t what this analogy is leading to.

If  I need to screw something together I just pick up the screwdriver and hey-presto done.    Why would I bother unpacking the Power Drill, plugging it in and finding the right toolbit?

Then I need to drill a hole.   In this scenario I need to get the Power Drill out.

For me, something changed several years ago when they invented the cordless drill that never needs charging and made it as easy to carry as a screwdriver.

This came about as the result of 2 things

1. Tables were introduced in Excel 2007 with it’s structured referencing.

2. I came up with an autocorrect trick to help write the INDEX MATCH formula (see below)

So for the last 10 years, regardless of context, I pick up my cordless drill. The screwdriver is just gathering dust.

Once in a while I might need a screwdriver to open a tin of paint though…. 🙂

Here’s a short video showing VLOOKUP v INDEX MATCH in action, including our Auto Correct trick mentioned below

Summing up

It’s not that VLOOKUP is wrong or bad, used in the right context it works great. It’s just that once I discovered the benefits of INDEX MATCH I’ve never had to think about the context as it works really well all the time.

Want to end this debate for future users?

Microsoft are listening to users expressing their views via voting on the UserVoice sites. We’d like to end the debate by having a new formula that takes the best of both, but you need to vote to make it happen. Vote here

So how do you “get on board” with INDEX MATCH?

If you want to see how to write a really quick INDEX MATCH then check out these articles here –

Using Autocorrect to write your INDEX MATCH formula

Clicking a button to write your INDEX MATCH formula with our Great 48 Toolbar (watch the video below before clicking on this link)