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). What is an Index Match?
INDEX MATCH is the combination of two Excel formulas: INDEX and MATCH.
The generic formula is:
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
What it Looks like in Practice
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.