How to Use the VLOOKUP HLOOKUP Combination Formula

VLOOKUP and HLOOKUP are two of the most popular formulas in Excel and using them together is one of the first formula combinations that people learn.  While using INDEX MATCH for vertical lookups and INDEX MATCH MATCH for matrix style lookups are superior approaches, it’s still a good idea to learn this formula combination and add it to your toolkit of lookup approaches.  You may find yourself in a situation where you encounter this formula combination after inheriting someone else’s file, or you may just be working with someone who does not understand INDEX MATCH.  Both of these contexts make it worthwhile to learn VLOOKUP HLOOKUP.

Objective and When to Use

There are two key requirements for using VLOOKUP HLOOKUP.  The first is that you need to have your data formatted in a matrix.  Or, to put it in simpler terms:

Your data table needs to have lookup values on both the top and left hand side.

In the example below, we have Country as a potential lookup value down the leftmost column and Year as a lookup value across the headings at the top.  Your goal should be to find the intersection between these two lookup arrays for particular values (for our example, we’ll be looking up the revenue amount for “Brazil” in “2014“)

The second requirement is unique to the VLOOKUP HLOOKUP combination formula:

You’ll need to add an additional row below your column headings to label the column numbers in your table

This requirement is specific to VLOOKUP HLOOKUP and is not a requirement of INDEX MATCH MATCH.  If for whatever reason you cannot insert a row identifying the columns of your data table, use INDEX MATCH MATCH instead.

The Syntax

= VLOOKUP ( lookup_value , lookup_array , HLOOKUP ( lookup_value ,  lookup_array , 2 , FALSE ) , FALSE )

The syntax for VLOOKUP HLOOKUP is fairly simple conceptually.  If you understand both VLOOKUP and HLOOKUP, all you are doing is replacing the column_reference component of VLOOKUP with the HLOOKUP formula.  We’ll go into detail about both formulas below.

The VLOOKUP Formula

VLOOKUP will represent the foundation of your formula combination.  The syntax for VLOOKUP is as follows:

= VLOOKUP ( lookup_value , lookup_array , col_index_num, [range_lookup] )

The HLOOKUP Formula

HLOOKUP has almost the exact same syntax and structure as VLOOKUP, with the exception that the lookup is occurring horizontally and therefore, you need a row index number instead of a column one.

= HLOOKUP ( lookup_value , lookup_array , row_index_num, [range_lookup] )

As mentioned before, the purpose of the HLOOKUP formula in this combination is just to provide the column reference for VLOOKUP to use.

Putting it All Together

Goal:  Assume we want to find the Revenue amount for “Brazil” in the year “2014”

Step 1:  Insert a row right below your field headings that identifies the column numbers of your table.  This was the second baseline requirement mentioned for VLOOKUP HLOOKUP and is highlighted in green below.

Step 2:  Start writing your VLOOKUP formula and select your vertical lookup value, in this case, the country “Brazil”

Step 3:  For the table array, select the portion of your table excluding both the top row and the additional row you just added (Please note: if you were instead to select the whole table, the formula combination will still work; just make sure that you have the left hand side of the your selection lined up with the edge of the table)

Step 4:  Once you get to the column reference, start your HLOOKUP formula and select your horizontal lookup value, in this case the year “2014”

Step 5:  For the table array, select the mini table you created when you inserted a row to identify the column headers.  This table should only be two rows in terms of height.

Step 6:  For the row reference, input 2.  This is because your mini table only has two rows, and you want it to return a value from the second row.

Step 7:  Close out both your HLOOKUP and VLOOKUP formulas with FALSE for the range lookup clause.  This is because we want an exact match for both of our lookups.

What Excel Does

The first thing Excel does is process the formula from the inside out, thus addressing your HLOOKUP formula first.  The HLOOKUP portion is fairly simple because we only have a table with 2 rows.

Moving horizontally across the top of the table array selected (green border table), stop at the lookup value (“2014“) and return the value in the corresponding second row (row_index = 2)

In this case, the formula resolves to 4.  Therefore, 4 will be used as your column reference for your VLOOKUP.

Next Excel will process your VLOOKUP formula.

Moving vertically down the left side of the table array selected (red border table), stop at the lookup value (“Brazil“) and return the value in the corresponding fourth column (result of HLOOKUP = 4)

The resulting value for the entire formula combination is $855.78.

Summary

VLOOKUP HLOOKUP is a widely used formula combination because of the familiarity of the two formulas.  Despite its disadvantages, VLOOKUP is still a more popular formula than INDEX MATCH and understood by a much wider base of users.  For that reason, understanding VLOOKUP HLOOKUP is still a worthwhile exercise.  If you’re ever in a situation where you need to perform a matrix lookup, but either do not understand or plan to hand off your file to someone that does not understand INDEX MATCH, consider using VLOOKUP HLOOKUP as your lookup option.