An Easier Way to Understand Excel INDEX/MATCH Formulas

April 24, 2018 by Jamel Cato

At least once a month I use an INDEX/MATCH formula to match and merge patient data from multiple Excel files. I wrote this post because when I first sought to learn the technique I found the other tutorials on the web difficult to follow or understand. This is my attempt at more intuitive explanation.

If you're reading this, chances are you have strong Excel skills and already know what INDEX/MATCH formulas do. For the rest of you, here's a short introduction:

INDEX/MATCH formulas, created by combining Excel's built-in INDEX function and its built-in MATCH function into a single compound formula, are ideal when you need to:

  • Merge data from one Excel list into another Excel list by matching records from the two lists; or
  • Use a common field from two Excel lists to lookup a second (or third or fourth) field by matching records from the two lists.

For instance, suppose you had two Excel worksheets for the same group of customers. The first worksheet contains columns for Customer ID and Email Address. The second worksheet contains columns for Customer ID, Phone Number and Age. With Customer ID as the common column, you could use an INDEX/MATCH formula to add each customer's phone number and age to the email worksheet.

For SQL experts, you can think of INDEX/MATCH formulas as a way to use Excel to do inner joins.

A standard INDEX/MATCH formula is written like this:

Index( value_array, Match( lookup_value, lookup_array, match_type ), column_number )

The MATCH portion returns a position in a list. The INDEX portion returns a value in a cell. So combining them together allows you to lookup a value in a cell based on the position of an item in a list. (What the formula actually does is use a MATCH function as the second argument of an INDEX function.)

Here's the Trick: Instead of trying to digest all of the above, just rewrite the formula in the following way and replace the double-bracketed portions with your actual data or cell references.

 =INDEX( [[find this kind of value]] ,MATCH( [[for this cell value within the first list]],[[with a matching cell value within the second list]] ,0))

A few parting notes that might be additionally helpful:

  • The MATCH portion of the formula is processed before the INDEX portion.
  • If you plan to use AutoFill to copy the formula down a column, ensure that the lookup array is either a named range or an absolute reference to a range.
  • You cannot refer to an entire column as the lookup array for the MATCH function; You must specify an exact cell range.
  • The 0 at the end of the MATCH portion is optional and one of three possible choices (1,0,-1). 0 means find an exact match. 1 means find the highest value that matches. -1 means find the lowest value that matches. If you omit this argument, it defaults to 1, which is almost always what you want.