VLOOKUP v INDEX MATCH - You decide
Wyn Hopkins
Leading a team that solves data challenges for companies and people Power BI | Excel | Fabric | Solver Budgeting & Consolidation. Control costs, reduce risk and assist growth Microsoft MVP ??5 Million+ YouTube views
This is a link to a lively discussion on LinkedIn around VLOOKUP and INDEX MATCH
https://www.dhirubhai.net/feed/update/urn:li:activity:6440106868756058112/
Plus here's the video if you want to skip the discussion...
In the discussion I made this analogy which I think sums up my viewpoint
... lets 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.
That is Oz's context view.
For me, something changed several years ago 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.... :)
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.
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 my 2 articles here
Using Autocorrect to write your INDEX MATCH formula
https://www.dhirubhai.net/pulse/stop-using-vlookup-wyn-hopkins/
Clicking a button to write your INDEX MATCH formula
https://www.dhirubhai.net/pulse/5-reasons-why-i-love-access-analytic-toolbar-wyn-hopkins/
Hope it helps
Even more importantly I'd like to end the debate by having a new formula that takes the best of both, but I need you to vote to make it happen.
Thanks
Wyn
www.accesssanalytic.com.au/blog
Cost Accountant
6 年Use both but prefer index match.
Western Suburbs
6 年Tom Seward Samuel Taylor
SC Cleared Data Analyst/vba developer at Urenco
6 年Think both have their place. However vlookup is probably easier for people to get their heads around in terms of the syntax and is therefore easier to support and maintain while being much less flexible.
Manager at Schellman
6 年Don Tomoff, MBA, CPA what do you think? #indexmatch vs. #vlookup
Digital Supply Chain Development Manager
6 年If you have 5 minutes to get those numbers just this one time... you can’t deny that vlookup is less typing. When building solutions/tools and table has more than two columns, index match all the way. There is one other aspect to consider. Ease of understanding by other users.