November 19, 2016 Leave a comment
I originally meant to write this post last summer the first time I used the magic of INDEX MATCH but for some reason never got around to it. I did however leave myself a template spreadsheet but even that took a bit of time to decipher what I’d done so this time around I’ve decided to make the post happen!
The need to delve back into my archives came about when a colleague in the HR department asked me if there was a way to look up information from one set of data against another in Excel and mentioned VLOOKUP as an option.
That got me thinking about a similar scenario I’d had the previous summer when I needed to so something similar with user accounts after some fun with Office 365 DirSync experiences: synced OUs and user deletion
I also remember swiftly dropping VLOOKUP in favour of the lesser-used but (imo) more flexible INDEX MATCH formula. Some of the advantages of the latter include:
- lookup columns from anywhere in the sheet
- no need to worry about messing up the formula if you insert \ move columns around
Of the websites I’ve looked at this one gives the best explanation and real-world examples so give it a read for further background:
What’s the answer?
However I wanted to write the formula out in even simpler plain-English so it would take me no longer than 10 seconds to remember how it works should my future self need a quick reminder.
Initially I went with the classic method of a post-it note but to save anyone needing to decipher my typically IT-techie scrawl here’s a much nicer version I made earlier 🙂
- In the example I’m using a value in cell A2 of Sheet1 to find an equivalent value in Sheet2 column A
Once found the formula returns a related record for the item in question from Sheet 2 column D
- You can fill the formula downwards if you have multiple inputs that need matching (e.g. a list of IDs that each need a value against them)
- To help illustrate I’ve made a sample file that uses a fictional student’s ID number to return their grade and date of birth from another sheet.
- If the value isn’t found in the data source Excel returns an N\A value
- As always the file is available in my Public OneDrive folder
- to save having to define exact cell ranges for the data just use D:D (or whichever column you require) to search the whole lot, handy if you’re likely to replace the data source with a refreshed version at some point.
- If you’re typing this formula in manually and selecting columns across tabs make sure you don’t follow your natural instinct to click back in the formula cell to complete it; if you do you’ll end up changing the tab’s reference back to the one the cell exists in, which will play havoc with your results!
- if you want to use the INDEX MATCH to return multiple values from the source data I find it easier to copy the formula into notepad, adjust the first cell reference then paste it back. Sometimes Excel tries to be too clever when copying \ filling across formulas and ends up causing more errors than it helps to solve!
In the end INDEX MATCH did the trick perfectly and earned me a Freddo chocolate bar for my troubles, which at the current ever-increasing price of chocolate these days is a pretty fair trade!