Microsoft Office Tutorials and References

In Depth Information

**Excel Functions for Your Data Model**

Adding VLOOKUP formulas to a data model

Using a few VLOOKUP formulas and a simple drop-down list, you can create a data model that not

only delivers data to the appropriate staging table but also allows you to dynamically change data

views based on a selection you make. Figure 11-8 illustrates the setup.

11 Samples.xlsx
workbook. Open that workbook to see the VLOOKUP1 tab.

On the Web

Figure 11-8:
Using the VLOOKUP function to extract data and change data views.

The data layer in Figure 11-8 resides in the range A9:F209. The analysis layer displays in range E2:F6.

The data layer consists of all the formulas that extract the appropriate data. As you can see, if you

select Chevron in cell C3, the VLOOKUP formula extracts the data for Chevron from the data layer.

You may notice that the VLOOKUP formulas in Figure 11-8 specify a table_array

argument of $C$9:$F$5000. So the lookup table that the formulas point to stretches from C9

to F5000. That may seem strange because the table ends at F209. Why would you force

your VLOOKUP formulas to look at a range far past the end of the data table?

Remember that the idea behind separating the data layer and the analysis layer is that

your analysis layer can automatically update when you update your data. So when you

get new data next month, you can simply replace the data layer in the model without

having to rework your analysis layer. Allowing for more rows than necessary in your

VLOOKUP formulas ensures that if your data layer grows, records won’t fall outside the

lookup range of the formulas.

Later in this chapter (in the “Working with Excel Tables” section), we show you how to

automatically keep up with growing data tables by using the Excel table feature.

Note