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

Unlock this course with a free trial

Join today to access over 25,600 courses taught by industry experts.

INDEX and XMATCH for referencing information and extracting data

INDEX and XMATCH for referencing information and extracting data - 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

INDEX and XMATCH for referencing information and extracting data

“

- [Instructor] INDEX MATCH is a widely used functional combination, and it's been used by millions of people for more than a generation. But I personally believe we may be able to retire MATCH altogether. Say hello to XMATCH. It's a lot like MATCH, but completely reimagined. It does a whole lot more and addresses some of the deficiencies of traditional MATCH. So let's demystify piece by piece what INDEX XMATCH does. In this example, you'll see that I have 12 months of data. I have eight employees across two different account types. Down below, I have three data validations, Account, Employee, and Month. What I want to be able to do is to pull the sales information for Jill Christensen in the month of June. Down here in C33, I'm going to type index D4 to P20, selecting the entirety of my data. I'm going to put in ,3,6 and then close this up. Why 3 and 6? Because Jill is located in the third row and June is in the sixth column. Now this is okay, but the problem is that I've hardcoded 3…

Contents