Creating Excel Reports Part 4 – Referencing Raw Data Using HLOOKUP

In the last tutorial, we were introduced to the VLOOKUP and were shown how powerful it can be when making reports in Excel. We created data tables and then reduced manual data entry by programming the report to use the tables to look up reoccurring values. As discussed previously, this makes it extremely easy to update certain types of information in your reports, such as phone numbers or hourly rates; once changed in the table the value can be automatically updated across all older data. In this tutorial we will be covering the other lookup command, HLOOKUP (Horizontal Look-up). HLOOKUP works very similar to VLOOKUP, but we will be using it in a completely different way. Rather then using it to check a table for a value and display associated information, we are going to use it to check the report for the current date, and then retrieve all of the information stored in “Misc” sheet for that date. This tutorial will be a little more advanced then the VLOOKUP one, I highly suggest you read through and review it first if you are new to lookup commands.

Step 1 – Set-up Your Data Table

We want the main report sheet to look-up data stored on the “misc” tab. Just like last time with VLOOKUP, we need to specify a data table for the function to search through. In this case it is the entire “Misc” sheet, so we first open the sheet, and then click the button where the row numbers and column letters intersect, see below:

Now everything is selected.  Just as before we click the name box just above the select-all button and enter a name for our data table, I chose “MiscData”:

Step 2 – The HLOOKUP Function

The anatomy of HLOOKUP is nearly identical to VLOOKUP, if you type =HLOOKUP( into any cell, the popup bubble will show you this:

If you remember the formula structure of VLOOKUP, they are almost identical, the only difference being that we now have “row_index_num” in place of “col_index_num”. As you may have guessed, HLOOKUP will search horizontally through the table, and once it finds the specified lookup_value it will display the data in the corresponding row from that column. Since we have selected the entire sheet for our data table in step 1, the row_index_num is identical to the row number on the sheet:

If you recall this is a typical column in Joe Transport’s “misc” sheet, note that when the column with the current day is found, it will display the information stored in the cell with the corresponding row index number. For example, let’s start with the “high” temperature field on the report:

So we want Excel to grab the date that is to be entered in cell B4 and look it up in the data table we defined earlier as MiscData then display the high temperature recorded in the “misc” sheet on the report page in cell N4. Now we define the variables in =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) as follows:

Lookup_value: B4 (the cell that contains the date)

Table_array: MiscData (as already defined)

Row_index_number: 3 (if you don’t see where this comes from look at the typical column image above)

[range_lookup]: FALSE (Just as the last tutorial, we will only be using false for our purposes)

When we put the formula in the cell it should look like this:

Now try it out, fill in a date on your report sheet and ensure the value is correctly selected. If you cannot get it to work for you, check Excel’s built-in Help Files.

Step 3 – Use HLOOKUP to Fill-out All Other Misc Data

Now that you have filed out one of the fields in the report, the same formula can be used to populate the other cells that reference raw data stored on the “misc” sheet, the only difference being the row_index_num specified. For filling out the large Work Activates box on the report, we would use the following formula:

=HLOOKUP(B4,MiscData,9,FALSE)

Similarly, for the number of cars under the Fleet Count section we would use

=HLOOKUP(B4,MiscData,17,FALSE)

The only fields that require a little extra trick to get working are the ones that require data to be referenced from the previous day, that is, Yesterdays workforce and vehicle fleet counts. For this to work, all we need to do is set the lookup_value equal to the current date minus one, or B4-1. Therefore, yesterday’s count of cars would be as follows:

=HLOOKUP(B4-1,MiscData,17,False)

We can go ahead and put the formula in all other cells that reference sheet “misc”. Note that it is important to not drag down formulas in this case, and to instead copy and paste them as text into adjacent cells. The reason for this is that Excel will not change the row_index_num when dragged, instead it will remain constant and all cells will display the same information. Here is a screenshot of my report page after I was done filling them in along with some arbitrary raw data, I also included some basic SUM() formulas:

This brings us to the end of this tutorial. We have now automated every piece of raw data stored in “misc” onto our main report page. We can change the displayed data from a previous date by simply changing the date on the report (in cell B4 for this example). In the next tutorial we will focus on entering shift data that will calculate and display revenue, expenses, and profits.