How to Use the VLOOKUP HLOOKUP Combination Formula

by Matthew Kuo on October 10, 2015

in Database Theory, Excel

To learn more about Excel, go to the organized listing of all my Excel tutorial posts or review the
 

VLOOKUP HLOOKUP Combo

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 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“)

VLOOKUPHLOOKUP_01

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.

VLOOKUPHLOOKUP_02

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

VLOOKUPHLOOKUP_03

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)

VLOOKUPHLOOKUP_04

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

VLOOKUPHLOOKUP_05

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.

VLOOKUPHLOOKUP_06

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.

VLOOKUPHLOOKUP_07

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.

VLOOKUPHLOOKUP_09 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)

VLOOKUPHLOOKUP_10

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)

VLOOKUPHLOOKUP_11

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 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.

{ 3 comments… read them below or add one }

October 11, 2015 at 11:01 pm

Instead of using HLOOKUP for getting the column index, how about using MATCH?
In this way, you don’t need a helper row.

Here’s my blogpost for this combination for sharing:

Reply

Orhun July 14, 2016 at 7:04 am

When you need to write a formula that needs to update data from a certain source within multiple periods, vlookup+match formula seems more convenient since you cannot modify the main source (sometimes it is not possible to modify source excel file from database, i mean column # 2,3 etc..). Anyway thanks for your effort, it has been too helpful!

Reply

July 26, 2016 at 7:31 am

I have a complex table, I want the excel to choose the size of the angle like 25 x 1.61 for 450 mm length and 36 kg (given in column 2 and 3) based on length which is given in left, referring to the weights provided under each column. it would be great if somebody can provide solution for this. Please note i have

Length (mm) 25 x 1.61 25 x 3.20
450 36 68
600 34 68
760 32 68
900 27 59
1060 18 50
1220 − 36

Reply

Leave a Comment

{ 1 trackback }

  • October 12, 2015

Previous post:

Next post:


Related pages


average vbacompare columns in excel for differenceshow to fix formula in excelcheck duplicate in excelexcel duplicate check formulaexcel tracking templateexcel vba new worksheetexcel vlookup testexample of vlookup formuladynamic formula in excelexcel format_texthow to vlookup with multiple criteriapseudonym generator freethe goal by goldratthow to highlight columns in excelforecast calculation in excelexcel v look upquotations in excelfemba uclahow to subtract columns in excelexcel spreadsheet shortcutscomparing columns in excelsample excel macro programslist of random numbers in exceliferror excel formulaisna excel formulaexcel template for inventorynested if and statements in excel 2010check symbol ms wordexcel formula iserrorindex and match function excelvlookup processlist of excel formulas 2007excel duplicate rowlookup command in excelif vlookup exampleexcel replace columnsort duplicatesaxis title excel 2010excel fuzzy matchingvlookup formula in excel 2007excel easy formulaspdf vlookup in excel with examplems excel countifcolumn graphs in excelnested if function excelexcel lottery number generatordefine cell range in excelexcel ticket tracking templatedeloitte consulting vaultfind unique records in excelexcel histogram tutorialwhat is the function of vlookupinsert symbol in powerpointexcel 2010 multiple if statementsexcel countif cell referencehow to use vlookup 2010excel dgetwhere to find the tick symbol in wordreplace excel formulacolumn headings excelexcel duplicate recordshow to subtract rows in excelexcel look up functionhow do i insert a tick in word 2010excel hlookupexcel task tracker templaterandom value generatorbell curve distribution excelticket office uclaexcel keep duplicateswhat is vlookup excelusing v lookupexcel symbol codesexcel nesting
\n