Dynamic Indexing in Excel

Dynamic indexing in Excel allows you greater flexibility when building a spreadsheet. This skill will enhance formulas such as VLOOKUP(), SUMIF(), and INDEX(MATCH)). It will also provide more options for pivot tables, charts, and analysis.

What is a Dynamic Index?

First, what is an index? An index is a structured set of data that can be quickly evaluated by a computer program. If you have a chart of data with row or column headings, then you have an index.

In this example, a basic Profit and Loss statement is an index. It is a table of financial information (structured data) organized by month (column header) and type of revenue/expense.

To evaluate the index, you can use VLOOKUP() on the revenue/expenses, HLOOKUP() on the months, SUMIF() on either category and INDEX(MATCH()) on the entire data set. As you might guess from the name, INDEX(MATCH()) is the most powerful tool to evaluate an index since it’s, you know, in the name.

Dynamic Indexing in Excel, Example Part 1

Now that you know what an index is, what is a dynamic index? A dynamic index allows you to change the parameters of a data set without having to manually change the index. You can use a dynamic index with static formulas and data to evaluate different scenarios, time periods, expenses, or really anything.

Creating a Dynamic Index

To create a dynamic index, we will add formula-driven row headers. You can hide the row headers later to maintain the formatting, but this will drive the functionality we need. After we add the row headers, we will use SUMIF() to evaluate the index.

For this example, we want to evaluate our data set of Profit and Loss by month. We would like to understand the profitability month-to-date (MTD) and year-to-date (YTD) for different time periods.

Step 1: Add Dynamic Headers to Index

First, add an input field to select the month you would like to evaluate. This should be a numerical value. In larger spreadsheets, you can use data validation to create a drop-down, but we will keep it simple here. I always color code input fields to make it easy for the user to recognize.

Second, add a MTD and a YTD field over the headers. You can group them to hide later. MTD is a static field where 1=January, 2=February, and so forth. YTD is a formula driven field using the IF() formula. If the month above is less than or equal to the month in the input field, display a 1. Otherwise, display a zero. The formula is =IF(C4<=$C$2, 1, 0).

Dynamic Indexing in Excel, Example Part 2

Step 2: Add Formulas to Evaluate Dynamic Headers

Once the dynamic index has been constructed, we need to add formulas to evaluate the data. We will use SUMIF() for this simple index. For MTD, evaluate using the Month set in the input field. Then, evaluate the MTD row as the range and Profit/(Loss) as the sum range. The formula would be =SUMIF(C4:H4, $C$2, C12:H12).

For YTD, evaluate using 1 or 0 as shown in the YTD field. If a month is a 1, it is within the YTD period. The formula would be =SUMIF(C5:H5, 1, C12:H12).

Dynamic Indexing in Excel, Example Part 3

Step 3: Change Input Field to Evaluate the Index

Once the formulas are constructed, change the input field to make sure everything is working. In step 2, using month 4, we had a MTD profit of $175 and a YTD profit of $1225. Adjusting the input field to month 6 gives us a MTD profit of $950 and a YTD profit of $2,600 as expected.

Dynamic Indexing in Excel, Example Part 4

Bonus Tip: Use a dynamic index together with the Index Match formula to really create some cool spreadsheets

Have any questions on dynamic indexing in Excel? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.