I’ve known about combining =index() and =match() for a few years now. It even shows up in a textbook I use:
But honestly, I’ve always been perplexed about why to use this.
One thing I’ve learned is that it is useful for “sorting” data that you can’t actually sort. I know it sounds weird, but students have told me that their employers do have worksheets of data that they don’t want sorted into different orders. And the workaround for that is these two functions.
Now Access Analytic’s The Barrow gives me three more reasons:
Here are 3 reasons why you should use INDEX/MATCH instead of VLOOKUP:
a) You can “lookup to the left”
b) You don’t get incorrect results when a column is inserted or deleted from your data
c) When used in conjunction with Tables, the formulae are a lot more meaningful.
- See more
Now this is interesting. The Barrow is a pretty regular contributor of cool Excel tricks for my bag. And I have had to deal with all three of these problems.
I may just put a post-it note on my PC that says “Do it with Index/Match”, and see how I fare over the next few months.