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 INDEX MATCH. 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:
- You are working with a relatively small data set and don’t expect to perform more than one or two lookups
- 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”.
Syntax
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 INDEX MATCH.
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:
- Starting in the top header row, it searches from left to right until it finds your lookup value
- Once the lookup value is found, it moves down vertically based on the row index number you provided
- 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.
Summary
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 INDEX MATCH or VLOOKUP.
Courtesy: mbaexcel.com