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

Unlock the full course today

Join today to access over 22,700 courses taught by industry experts or purchase this course individually.

Advanced dynamic mapping using INDEX, XMATCH, and INDIRECT

Advanced dynamic mapping using INDEX, XMATCH, and INDIRECT - Microsoft Excel Tutorial

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

Start my 1-month free trial

Advanced dynamic mapping using INDEX, XMATCH, and INDIRECT

“

- [Instructor] Let's do another approach to data mapping that exclusively relies upon Excel's functions, this time, with INDEX, XMATCH, and INDIRECT. If we take a look down at the bottom in data mapping, let's again look to marketing expenses. What many FP&A professionals will do when looking up data, is they will utilize a physical lookup. That is, they directly link to the data that they want. Now in this example, I'm going to show you how you can use XMATCH to do this. In E92, I want to map to the row within my marketing expenses worksheet, the row that contains the value for total marketing activities. Let's take a look at the formula. It says XMATCH, A92, marketing expenses 2017 column A to A, comma zero, comma negative one. Let's talk through each one of these elements. The syntax for XMATCH reads: lookup value, within a lookup array or a range, a match mode and then a search mode. So what this formula is doing is…

Contents