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


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.


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.


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:


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!


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


Leave a Comment

{ 1 trackback }

  • October 12, 2015

Previous post:

Next post:

Related pages

histogram bin excelvlookup exerciseexcel spreadsheet vlookupoffset macro exceldelete duplicate excelms excel vlookup and hlookuphow to make a 100 stacked bar chart in excelvlookup for dummiesinsert check mark in excelformula in excel sheetexcel stdevbins in excelhow to use hlookup in excel 2013alt shortcuts excelnested if formulasexcel tick marksexcel bar chart multiple seriescommon excel commandsexcel remove duplicate recordscompare column values in excelfind duplicate values in excel 2010how to make a bar graph in excel machow to use vlookup & hlookup in excelnested if function excel 2010 examplewhat is a nested function in excelcombine hlookup and vlookupmicrosoft excel learning guideindent excelwhy do we use vlookup in excelconditional filter in exceladding excel columnsexcel counting formulainsert icon in excelexcel lookup not workingmaking histograms in excelformatting numbers in excelif not blank in excelascending order on excelexcel unique records onlyzeros in a crorevlookup with if statementshortcut for symbols in excelwingdings check markmultiple if then statements in excel 2010excel vba cell containsvlookup excel worksheethistogram function excelvlookup example spreadsheethow to remove duplicate values in excelhow to display zeros in excelname picker classroomexcel compare two lists of namesexcel paycheck templatecompound if statements excelw4 calculationsvlookup & matchhow to do cumulative sum in excelexcel matching columnswhat is tax liabilities on w4formulas in excel not workingjob tracking excel templatefind duplicates in excel vbaexcel column comparison formulamultiple conditions in excelpivot queryformula of vlookuprandom name picker for teacherscomparing two columns in excelde dupe excelif statements on exceltick marks excelexcel hlookupkeyboard shortcuts for excelhow to find duplicate entries in excel 2007excel shortcut deleteucla anderson stshow to use indirect function in excel 2010ucla course listingexcel formula definitionscharacter code for checkmarkvlookup excel sheet downloadcell reference excel definitionvlookup 2 columns