Many of you may be familiar with using VLOOKUP to search for data on your spreadsheets; it’s easy to use and easy to remember the arguments for the formulas.  So why should you switch to using the INDEX/MATCH combo instead?  Using INDEX/MATCH means you no longer have to be looking up on the leftmost column of an array and returning a value from somewhere on the right – instead you have the freedom to have your table set up with the columns in any order.  These functions work exactly the same in Excel as it does in Google Sheets.

So let’s jump into how to use it:
Inline image 1
The formula is nested into two parts, for the first part of the INDEX function, just highlight the array you want to get your final result from, then the second part of INDEX (after the comma) is MATCH, just click the cell that contains your lookup value, comma then highlight the array where you can find that lookup value.  The zero after the 2nd comma just signifies that you are looking for an exact match; then don’t forget you need two closing brackets!
If you get into the habit of using the INDEX / MATCH combo instead of the VLOOKUP function for a while, you will become much more familiar and have the confidence to use it when the time comes.  Or save this page into your favourites and come back when you need a reminder!
Don’t forget to share this page with a friend or colleague that needs to make the switch to INDEX/MATCH.

Leave a Reply

Your email address will not be published. Required fields are marked *