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

Unlock this course with a free trial

Join today to access over 24,900 courses taught by industry experts.

Multidimensional INDEX function lookups across data sets

Multidimensional INDEX function lookups across data sets - Microsoft Excel Tutorial

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

Start my 1-month free trial Buy for my team

Multidimensional INDEX function lookups across data sets

“

- [Instructor] You know INDEX is a way to pull a value from a data range using a row and column number. But what if I told you there's a version of INDEX that lets you pull from multiple ranges with a single formula? This is one of the most underutilized applications for INDEX in Excel. Not because it's not powerful, but because most people don't even know it exists. It's incredibly useful in FP&A modeling when you're working with multiple scenarios, timeframes, departments, business units, and even disconnected data. Allow me to introduce to you what I call multi-dimensional index. Here's the syntax. Up at the top, you're going to see index array, row number, column number. This is the way that most people use INDEX, but I'm going to walk you through the bottom example, INDEX reference, row number, column number, and then here's the secret, area number. This lets you define a group of many different ranges or arrays, and then choose which one you want to reference. Let's walk through…

Contents