Index Match

In a previous post, I shared a common Excel formula that some of my friends in finance will automatically fail a job candidate for using during an interview. Today, I'm delving into the superior choice for FP&A professionals. (If you love a good Excel debate, check out the Reddit threads later in this post).

INDEX MATCH is the combination of two Excel formulas: INDEX and MATCH.

The generic formula is:

=INDEX(data,MATCH(value,lookup_column,FALSE),column)

Let's break it down:

• MATCH: The relative position of a cell in an array (i.e. a row or column number within an array).
  
• INDEX: The value of a specific cell in an array, based on a provided row and/or column number.
  
• INDEX MATCH: The value found in a specific cell based on its relative position

In the example below, we're using a pretty simple INDEX MATCH to search our Swag inventory to pull the number of size medium hoodies that we have in the inventory. THe image below shows you how the formula is structured.

• Less volatile/Lower maintenance. Inserting or deleting a column won't break the formula — a key selling point when you're collaborating on a file with others in your organization.
  
  
• Faster. Instead of searching across the whole table, it allows you to specify the range for a column or row. This saves a lot of time, especially if there are other formulas embedded.
  
  
• Looks left (and right). No need to duplicate an array in order to find a value in a column to the left of the search range (a workaround some VLOOKUP proponents swear by). INDEX MATCH enables you to look both ways and even on a different sheet! You could even look up a row based on a column. The only requirement is that the range is the same size.
  
  
• Enables lookups in a grid (using INDEX MATCH MATCH). You can use two MATCH functions inside INDEX to perform lookups in a grid by searching for a row and a column match.
  
  
• Enables lookups in multiple columns. Use a formula like =INDEX(M:M,MATCH(T4&U4,B:B&C:C,0)) to match values from multiple columns. In this example, the formula would return a value from column M by looking up a value in the connected columns B and C to match the connected value in the cells T4 and U4.
  
  
• Finds the nth match. When there are multiple matching rows VLOOKUP gives the first match. For example, if there are five rows that match the given criteria, VLOOKUP always returns the first match. This is the default behavior for INDEX, but it can be changed to get the nth match.
  
  

• The formula can be unwieldy. Combining two formulas isn't for the faint-of-heart, Excel user. It can take a few tries to get used to what you're doing.
• The dreaded #REF. One common pitfall of using INDEX MATCH arises when the INDEX range is a different size from the MATCH range. When two ranges are not equal in size, the formula can't identify the specified criteria, resulting in a #REF error. Large data sets are a common culprit, particularly when there are blank cells in a range.

Why INDEX MATCH is the Best Match for FP&A

Even VLOOKUP holdouts can't ignore that the benefits of Index Match clearly outweigh the downfalls when it comes to managing corporate budgets. Corporate budgeting requires managing a lot of moving parts and having reliable data and reports are table stakes. Index Match gives FP&A teams more confidence in their reports, reducing the potential for error, enabling better collaboration, and improving efficiency.


If you're interested in following the debate on Reddit, including the merits of other alternatives like XLOOKUP (available for Office 365 users) and PowerPivot (available via Excel plug-in), here are two links:

• VLOOKUP vs INDEX/MATCH Showdown
  
  
• VLOOKUP vs INDEX-MATCH? 28 experts share their opinion

Of course, you can always reduce the Excel formulas that you need to create, by automating common FP&A reports. Cobbler helps FP&A teams automate BVA and headcount reporting. To learn more, schedule a demo.