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.
To see this effect in action, go to www.wiley.com/go/exceldr to get the Chapter
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.