Excel Tip - Combine Index & Match Function

Lately I have been on a quest, to improve my Excel spreadsheet skills. I have to take 40 hours of class each year to maintain my CPA license so this year I will once again dedicate some of that class time to improving my Excel skills. With the goal of building out your skills to build an awesome dashboard that allows you to pull up data in different ways, stay with me on building out these skills and formulas. The index match function functions similar to a vlookup function, but allows more flexibility in working with your data.

Some simple data to show how you can use Index Match Function

Let's start with the data table section.

The process to create a table is pretty easy. Select a cell in your data, hit "Cntrl" +"T" on a PC, and then follow the prompts. It's that easy and you have a data table. You can format the date table with a variety of pre-formatted designs. You can also filter the data - notice the drop downs next to the headers in Accounts, Dec-24, Jan-25 and Feb-25. One thing to note is the headers will be formatted as text. So this means when you search, your data you are trying to match data, you need to use numbers that are "text" vs treated as a "number."

Combine Index and Match to find your data.

This formula does look intimidating. Until you are comfortable working with really any Excel formula, you can split the formula up into different parts. In this case, you could build the match formulas in a separate cell to test the function is working as you expect it to. All this formula is doing is first, looking at the data table, then it looks to find what row and what column matches your criteria. In this example, for the "Cash" number, the formula looks for what row the Cash information is in. And then it looks for what column the Date you are looking for.

Here is the Final Report

While this particular report is not that impressive, as I stated above, think about how you can use this functionality with your current data. This formula is powerful in looking at large data sets and being able to view the data. For example, you can create a drop down on the date and it would pull the different data.