How to Use HLOOKUP

by Matthew Kuo on June 24, 2013

in Database Theory, Excel

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

HLOOKUP is essentially the horizontal version of VLOOKUP.  The primary reason for using HLOOKUP in Excel is for when you have key data points arranged horizontally across the top of your table.  This usually happens when you are dealing with time series data; people have a tendency to list lookup values horizontally with this type of data.


Before You Read Any Further

If you have any experience working with databases, you’ll know that you generally want to list all of your lookup values vertically, under one column heading.  While it may be more visually appealing to arrange them horizontally, this goes against the basic rules of database theory.  The horizontal arrangement of lookup values should only be used when you are creating outputs.

Therefore, if you do come across a large database with the primary lookup values arranged horizontally, your best course of action is to transpose the data so that it’s in the proper format.  (The transpose function is relatively easy and can be found within the Paste Special menu)  Then, instead of using HLOOKUP, perform all the lookups you need with VLOOKUP or .  The basic point here is that you should only use HLOOKUP if you absolutely have to.

However, there are situations when using HLOOKUP is permissible:

  1. You are working with a relatively small data set and don’t expect to perform more than one or two lookups
  2. You are unable to edit or transpose your data set for some reason

Understand the Objective

HLOOKUP is used to return a value from a data set using a reference from a horizontal array

In the example below, we have a lookup value for the Year “2015” circled in blue and a table data set circled in green.  The cell highlighted in yellow is the return value, where we are missing a value for Interest.  Therefore, we want to know what the Interest amount was in the Year “2015”.



Now that we understand the objective, we will focus on the syntax, which are the inputs required to write out the HLOOKUP formula.

The syntax for HLOOKUP is very similar to that of VLOOKUP, except the lookup values are arranged horizontally and instead providing a column reference, we must input a row reference.  We will review each component of the syntax individually.

=HLOOKUP ( lookup value, table_array, row_index_num, [range_lookup] )

Lookup value

The key to using HLOOKUP is having your lookup values on the very top row of your data set.  If this is not the case with your data, you should consider using a different lookup formula.  The lookup value represents the element that we need more information on.  In the example below, we want this additional information for the Year “2015”.

We will start by typing our formula in the blank cell where we want to return the missing value.  Select the cell with your lookup value for the first component of the syntax and follow it with a comma.


Table array

The table array is the larger data set where your return value exists.  To select your table array, simply click and drag around the entire data set that is relevant to your lookup formula.  Then complete the entry of this component by inputting a comma.


Row Index Number

The row index number is where you indicate which row you want to pull your data from.  This component is where you are most likely to make a mistake and why you should consider using .

The minimum number for this input is 2 – you cannot look up and return the same value.  The maximum value depends on how large your table array is.  In the example below, we only have 7 total rows.  Therefore, our maximum input for row index number is 7.  Inputting any number higher than 7 will return an error value.  Finally, when you are counting rows to come up with your row reference, make sure that you include the header row.

In the example below, we want to return the Interest value, which is the fifth row within the data set.  Therefore, for the row index number, we input the number “5”.


Range Lookup

Most people don’t really know what a range lookup does because it is rarely ever used.  The range lookup basically tells your lookup formula to look for values that are close to your lookup value, not ones that are exactly the same.  The rules on what’s “close to” your lookup value are vague and if you’re working on a project where you need some level of precision, you can see why this functionality can be problematic.  You basically have three options to deal with the range lookup portion of the syntax:

1.  Choose not to use range lookup

To choose this option, input either a “0″ or the word “FALSE”.  Many of us who’ve used this formula for years are used to typing in FALSE; the option to type in 0 is relatively new.

2.  Choose to use a range lookup

To turn on the range lookup functionality, simply do the reverse of the prior inputs, and enter either “1″ or “TRUE”.

3.  Ignore it

If you simply close off the parenthesis and leave out this final piece of syntax, the formula will default to “TRUE” and will have your HLOOKUP perform a range lookup.

Because we want an exact match for this particular example, we will input “0” so the HLOOKUP does not perform a range lookup.  After you’ve written your formula, and put in the required inputs, close the formula with a “)” and press ENTER to finish writing the formula.


What Excel Does

After you’ve written all of your parameters, the formula performs the horizontal lookup:

  1. Starting in the top header row, it searches from left to right until it finds your lookup value
  2. Once the lookup value is found, it moves down vertically based on the row index number you provided
  3. After moving down by the specified number of rows, it returns whatever value it lands on

If you’ve written the formula properly, the value you were looking for will be returned in the lookup result cell.



As I mentioned before, HLOOKUP is something you should only use if you absolutely need to.  HLOOKUP isn’t a common formula because people generally don’t arrange large data sets to have the lookup values on the top.    The best approach when facing such a situation is to transpose your data so that you can perform a vertical lookup with either or VLOOKUP.

{ 5 comments… read them below or add one }

venkat September 7, 2015 at 10:47 pm

hi this i learned from this web..thanks


November 3, 2015 at 7:37 pm

Thanks, this one really help me. 😀


km June 4, 2016 at 9:54 am

Very good and easy to understand the examples


Mary Connolly July 8, 2016 at 8:39 am

Many thanks. I have been pulling my hair out with frustration on this one. Finally found this which gives very clear notes on how to identify the row for return value in the formula. I have been using the row number in the side scroll bar, where I should be using the row number of the selected data set. All very simple when you know how! Cheers


Santosh November 17, 2016 at 7:27 pm



Leave a Comment

Previous post:

Next post:

Related pages

testing normality in excelexcel hlookupexcel random data generatorexcel navigation shortcutsunderpayment penalty calculatorucla anderson gmathistogram excel exampleexcel check for duplicatehorizontal axis excelthe goal a process of ongoing improvement sparknotesiferror function excel 2010stack bar graphinsert check mark in excel 2007random selection in excelinterview padfoliovlookup 2 conditionsquery tables in excelexcel countifexcel symbol codesusing pivot in excelthe goal by eliyahu goldrattexcel macro worksheetexcel formula errorsucla winter schedulevlookup and isnaexcel 2010 if statementstesting normality in excelmr excel powerpivotmyuclsexcel bracket templateexcel vba vlookup examplehow to randomly select in excelinsert tick symbol excellookup across multiple sheetstracking inventory in excelmicrosoft excel find duplicatesformula countifmaking graphs with excelif statements in excel 2010excel if statement with multiple conditionstick on excelshort cut keys for excelmatch function in excel 2010vlookup in spreadsheetname picker for contestexcel vlookups and pivot tableshow do i compare 2 columns in excelvlookup for multiple valuesnested if and functionscount duplicates excelpaste special values shortcutstacked column chart with multiple seriesampersand excelawesome excel templateshow to remove conditional formatting in excelucla femba costhow to make bar charts in excel 2010excel sheet formulasask mr excelucla anderson rankingscan you delete duplicates in excelexcel navigation shortcutsinsert tick in wordthe goal eliyahu m goldrattexcel vlookup exercisesclass tools randomiserexcel formulas greater thanall excel formulas in excel sheethow to create bar graphs in excelexcel formula formattingrandom number list generator excelcalendar planner template excelexcel lookup function examples