Using HLOOKUP in Dashboard Models

Debra D. is currently on a mission at the Contextures Blog to cover 30 Excel Functions in 30 days. Quite the feat, considering since she also cranks out a video tutorial with each one. Anyway, the 10th function she covered was HLOOKUP. And as you may have guessed, poor old HLOOKUP didn’t even get one comment. So I’d like to give you my take on why I use the HLOOKUP function is some of my dashboard models.

.

If you’re new to the HLOOKUP function, you can get the specifics at Debra’s post. But the gist is that HLOOKUP finds a single value from a row of data where the column label matches a given criterion.

For example, the table in C3 requires quarter-end numbers (March and June) for 2010. HLOOKUP uses the column labels to find the correct month columns and then locates the 2010 data by moving down the appropriate number of rows. In this case, 2010 data is in row 4, so the number 4 is used in the formulas.

.

This is fine and dandy, but we all know that HLOOKUP is the less popular cousin of the cool VLOOKUP function (mainly because Excel data is typically vertically oriented). So in most situations, we would use some other formula to do this kind of thing.

.

But like I said, I personally use the HLOOKUP when I’m building dashboard models where I need to separate the data layer and the analysis layer.

In a dashboarding model, you don’t want your data to become too tied into any one particular way of presenting that data. So you separate the data layer from the analysis layer. The analysis layer consists primarily of formulas that analyze and shape data from the data layer into formatted tables (commonly referred to as staging tables). These staging tables ultimately feed the reporting components in your presentation layer. In short, the analysis layer is where data is summarized and shaped to feed the reporting components.

.

There are a couple of benefits to this setup. First, the entire reporting model can easily be refreshed by simply replacing the raw data with an updated dataset. The formulas in the analysis tab continue to work with the latest data. Second, any additional analysis can easily be created by using different combinations of formulas on the analysis tab. If you need data that doesn’t exist in the data sheet, you can easily append a column to the end of the raw dataset without disturbing the analysis or presentation sheets.

.

HLOOKUPs are especially handy for shaping data in the analysis layer into structures appropriate for charting or other types of reporting. Take the simple example demonstrated here.

In this example, HLOOKUP formulas pull and reshape data without disturbing the raw data table. The data shown in the raw data table at the bottom of the figure is reoriented in a staging table at the top. When the raw data is changed or refreshed, the staging table captures the changes.

.

In this example, HLOOKUP formulas pull and reshape data without disturbing the raw data table. The data shown in the raw data table at the bottom of the figure is reoriented in a staging table at the top. When the raw data is changed or refreshed, the staging table captures the changes.

.

Well, I’ve done my good deed for the day. It’s time to go make myself a Bacon Egg and Cheese Sandwich.

.

Oh and I might as well throw in a crass commercial plug for “Excel Reports and Dashboards“.

If you want to learn more about building effective Dashboard models, check it out.

8 thoughts on “Using HLOOKUP in Dashboard Models

  1. Ed Ferrero

    Sorry Mike,

    You’re using HLOOKUP to replace TRANSPOSE?!

    Why not get TRANSPOSE to replace HLOOKUP? After all, {=VLOOKUP(C3, TRANSPOSE($B$7:$H$10),4,FALSE)} is equivalent to =HLOOKUP(C3, $B$7:$H$10,4,FALSE), if you learn to love those curly brackets.

  2. Charlie Hall

    I agree with Amolin, your example would be better suited for index and match from a performance perspective – why have hlookup do the hard work for every cell, when it would be faster to use match to find the appropriate row and column once, and then use that row and column via index for every formula in the analysis table

    Hlookup is very useful, just not as much in your example

  3. mark

    How can you automate this to nchange the range for the additional years that populate columns in your table. I think it might be a mapping issue. After the year changes my dashboards have to all be re-linked for the change of year. I now I am just missing a small tip here to make it work….

Leave a Reply

Your email address will not be published. Required fields are marked *