HLOOKUP in Excel

HLOOKUP is one of Excel’s flexible and powerful lookup functions. It helps you search for a given value in the first row of a table, and then return a selected value from another row within that table. The formula is easy to set up, and helps you work with data quickly. Let’s learn how to use HLOOKUP in Microsoft Excel.

How to Use HLOOKUP in Excel

HLOOKUP is a horizontal lookup inside your spreadsheet. If you’ve ever used Excel’s VLOOKUP (Vertical Lookup) function, you’ll find that the formula works in a similar way. However, instead of the lookup value referencing a range’s first column, an HLOOKUP references the first row.

Imagine a column of data like this one. Your Location # column corresponds to a region, which you’ll find in the table placed in the range E1:H2. You want to populate column C, cells C2:C6, with the name of the region.

Of course, for a limited-sized dataset, you could perform these steps manually. But it’s far better to use HLOOKUP. It does the work for you, saving you precious time when you’re working with large datasets.

Before diving in, it pays to understand how the HLOOKUP formula works. HLOOKUP is a function consisting of four inputs:

• Lookup Value: the value you want to find in the first row of your table. Here, the lookup value is any value in column A, the Location #.
• Table Array: the range of cells containing the data table you want to search inside.
• Row Index Number: the row in the table containing the value you want returned in the cell containing your HLOOKUP formula.
• Range Lookup: this value is FALSE for an exact match and TRUE for an approximate match when the lookup is performed. For most applications, FALSE is the preferred input.

Now that you’re familiar with the inputs to the HLOOKUP formula, it’s time to start working to build the formula itself. In this example, you’ll want to place your cursor inside cell C2. Keep in mind, you’ll only have to type the formula once – when finished, you can copy it downward to run the lookup for the remaining cells in column C.

With cell C2 selected, type in an = sign. This is standard practice, telling Excel that you’re inserting a formula in the selected cell. Then, add the HLOOKUP( function. Your formula now reads:

=HLOOKUP(

Now, you’ll need to type in your lookup value. Remember, this is the value you want to find in the first row of the table. Compare it to your dataset. The lookup value is found in column A, cell A2: the Location #. Remember, you want to take the location # and find out what region it corresponds to. Click on cell A2, then type a comma.

=HLOOKUP(A2,

Then, click and drag to select the table array. This is the range of cells in your table. You can leave out the row headers. Click and drag to select F1:H2, then type another comma.

=HLOOKUP(A2,F1:H2,

Next comes the row index number. Remember, this is the row in the table from which you want to return a value. You want the region, which is found on the second row of your table. Thus, your index number is 2:

=HLOOKUP(A2,F1:H2,2,

Finally, type FALSE). Your complete HLOOKUP formula is:

=HLOOKUP(A2,F1:H2,2,FALSE)

Finally, in the formula bar, click and drag to select F1:H2 inside the formula. Then, press F4 on your keyboard. This locks the cell references so that you can copy the formula down.

Hit Enter on your keyboard, and cell C2 will populate with the region: North. Then, simply click and drag the lower right corner of cell C2 downward to copy the formula across column C, to return the remaining regions.

As you can see, HLOOKUP is a quick and clean data lookup inside Microsoft Excel.