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


pivot table vlookuphow to make a stacked column chart in excelvlookup limitexcel check for duplicatewhat is symbol in excel formulashort cut excelhow to delete lines on excelhow to use the vlookup function in excel 2010excel formula cheat sheet 2013ucla student discountmatch rows in excelisna in excelinsert column in excelucla grading systemprimopdf portableduplicate excel worksheetvlookup for multiple valuesinverse normal distribution excelhow to write vlookup formula in excelexcel random number generator no repeatsisna matchvlookup example in excelmultiple vlookup functionhighlighting in excelucla anderson parkerhide formulas excelexcel column graphhow to extend trendline in excelexcel remove repeatsexcel stacked bar chart multiple seriesrandbetween without duplicatesp&l model templateexcel compare lists for differencestutorial for excel spreadsheetsvlookup and if function togetherexcel formula for alphabetical orderfrequency histogram generatormicrosoft office 2014 free trialnormal plot in excelremove duplicates excel vbacheck duplicate excelduplication formula in excelfilter out duplicates in exceldeduplicate in excelgraph chart excelexcel spreadsheets formulas and functionsindex and match function in excelvlookup formula in excel 2010 with exampleexcel offset columnmacro excel open filecountif matchexcel formula basicsexample of countifwhat feature is used to delete a conditional formatting ruleexcel match lookupdelete duplicate excelexcel vba cell referencesimple tax refund estimatorexcel greater than formulaexcel random number generator no repeatsexcel formula inputwhat is hlookup in excel used forexcel offset columnexcel chart titleinventory calculation in excelstacked bar chart total labelexcel formula macrovba vlookuphlookup in excel examplevlookup multiple columnsexcel pay stubucla bruin storehow to stop excel from roundingexcel formula to delete duplicatesplanner format for excelcool tricks in excelgraph charts in excelexcel planner templategoogle spreadsheet tick boxinsert histogram in excelhow to use vlookup functionduplicate formula excelpirate microsoft office
\n