From the course: Excel for Financial Planning and Analysis (FP&A)

Unlock the full course today

Join today to access over 23,700 courses taught by industry experts.

Using INDEX XMATCH for dynamic ranges

Using INDEX XMATCH for dynamic ranges - Microsoft Excel Tutorial

From the course: Excel for Financial Planning and Analysis (FP&A)

Start my 1-month free trial Buy for my team

Using INDEX XMATCH for dynamic ranges

“

- [Instructor] In addition to dynamic tables, as I've shared with you INDEX and MATCH, and INDEX plus XMATCH, continue to be among the easiest, cleanest, and fastest ways to build dynamic ranges. So what does a dynamic INDEX range allow you to do? Well, it allows for the dynamic referencing of expanding and contracting data sets, prompted by user inputs and toggles. As you can see over here in cells N2 and N3, I have a couple of data validation dropdowns. Well, how did I do this? If I go up to my data ribbon and I go over here to data validation, I click this icon, this window pops up, and it says allow a list, and my source is specifically going to be B6 all the way over to M6, it's the range of dates. Let's go back over. Now, when I select this dropdown, you can see that I have my list of dates all the way from January down to December. Now, if I put my cursor on each one of these formulas, you'll recognize the…

Contents