Tip of the day – Excel INDEX MATCH in 10 seconds

microsoft_excel_2013_logo-svgI 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:

Ref: https://fiveminutelessons.com/learn-microsoft-excel/how-use-index-match-instead-vlookup

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 🙂

index-match

  • 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

Further tips

  1. 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.
  2. 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!
  3. 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!

Advertisements