# An Easier Way to Understand Excel INDEX/MATCH Formulas 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))``