Marketing Analysis with Excel – Index Match
FIRM FIXED IDEAS
Index Match Function
A walk-thru of marketing analysis with Excel using the Index Match functions for up to date and dynamic data across cell ranges.
“[The files] they’re *in* the computer?” – Hansel in Zoolander
Excel is a wonderful marketing analysis tool and is one of those universal tools to work with data. When I was working for an agency with multiple clients we struggled to find an affordable business intelligence solution for an agency with multiple clients. Everything kicked off in Excel.
My favorite tool in Excel (as of right now) is the Index Match function. Actually it uses both the Index and the Match functions together to make a range of data dynamically update. There are multiple use cases but I will walk you through how I was taught and still use this function today:
Unless you already have data to work with I would set up some sample data to play with and see if you can get this function to work for you before throwing it into official sheets. The function works by setting a code found using the index function, gets the position by the match function, and always returns the last cell in the range.
Disclaimer, I use this function to return data only, not words/phrases/etc.
For the Index function we will be using the first block to indicate the Row number (ROW#) as pictured above. The Row should correspond to the row where your data range exists, from left to right. Each ROW# should be the same row number (ex: Row 20 is 20:20).
Traditionally, you would use the first block to indicate the column where your data exists, and where you will be returning a final value from.
The second block in the Index function will be used to specific the updating data in the range, or the last cell with data inside it, from left to right. You have the large number, and then the same Row you used before, exactly as you used it before in the second block of the Match.
Traditionally, you would use the final block to indicate the column/row you want to look up against.
The Large Number:
The large number (9.999999E) must be there, and as I am not an excel specialist, I can only say that it must be large to correctly narrow to the correct numerical data. It represents the lookup value but in our usage it reflects the numerical range. I believe it could be replaced in order to narrow to words/phrases if desired.
The image above is a sample of one of the Index Match functions I am currently using in my Analysis Sheets. I will show you exactly how I am using this function now that you know how it works. Remember I am using this for number data, not words, so test your version accordingly.
The A highlight shows the function that I am using, on Row 20.
The B highlight shows the Row that I am referencing data from, Row 20.
The C highlight references Column I, or the final range of data included in the function. Only the furthest right data will be referenced, in this range, which would be C, the value is 537. The bottom 2/3rds of my sheet represents the Deep Dive section – where all the micro data lives, period after period.
The D highlight references the top 1/3rd and macro data view of the data in the Deep Dive. At the top, for easy access is what is used most by Management or when sharing the sheet. The data marketers care about is in the deep dive – it’s not pretty down there but it’s where we see opportunities.
The Index Match function dynamically pulls the most recent (furthest right) data in the sheet and displays it in the top area automatically. This helps us to limit how much data we are inputting and manually updating. There are ways to automate even that to some extent.
All in all I use these tools to maximize the most important work that I do and minimize the tedious and unimportant work. Automation is vital for the mundane tasks. Once you have sheets set up you can jump into the optimization.
If you have any questions let me know?