30 Excel Functions in 30 Days: 10 – HLOOKUP

Icon30DayFor day 10 in the 30XL30D challenge, we’ll examine the HLOOKUP function. Not too surprisingly, this function is very similar to VLOOKUP, and works with items that are in a Horizontal list.

Poor HLOOKUP isn’t as popular as its sibling though, because most tables are set up with the lookup values listed vertically. When was the last time that you wanted to search for a value across a row, and then return a value from that column, in a specific row below?

NOTE: You can have all of the 30 Functions content in an easy-to-use single reference file — the 30 Excel Functions in 30 Days eBook Kit ($10).

Anyway, let’s give HLOOKUP its moment in the spotlight, and take a look at its information and examples. Remember, if you have other tips or examples, please share them in the comments.

Function 10: HLOOKUP

The HLOOKUP function looks for a value in the first row of a table, and returns another value from the same column in that table.

Hlookup00

How Could You Use HLOOKUP?

The HLOOKUP function can find exact matches in the lookup row, or the closest match, so it can:

  • Find the sales total in a selected region
  • Find rate in effect on selected date

HLOOKUP Syntax

The HLOOKUP function has the following syntax:

  • HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
    • lookup_value: the value that you want to look for — it can be a value, or a cell reference.
    • table_array: the lookup table — this can be a range reference or a range name, with 2 or more columns.
    • row_index_num: the row that has the value you want returned, based on the row number within the table.
    • [range_lookup]: for an exact match, use FALSE or 0; for an approximate match, use TRUE or 1, with the lookup value row sorted in ascending order.

HLOOKUP Traps

Like VLOOKUP, the HLOOKUP function can be slow, especially when doing a text string match, in an unsorted table, where an exact match is requested. Wherever possible, use a table that is sorted by the first row, in ascending order, and use an approximate match. You can use MATCH or COUNTIF to check for the value first, to make sure it is in the table’s first row.

Other functions, such as INDEX and MATCH, can be used to return values from a table, and are more efficient. We’ll look at those functions later in the challenge, and see how flexible and powerful they are.

Example 1: Find the Sales for a Selected Region

The HLOOKUP function looks for a value in the top row of the lookup table. In this example, we’ll find the sales total for a selected region. It’s important to get the correct amount, so the following settings are used:

  • a region name is entered in cell B7
  • the region lookup table has two rows, and is in range C2:F3
  • sales total is in row 2 of the table.
  • FALSE is used in the last argument, to find an exact match for the lookup value.

The formula in cell C7 is:

=HLOOKUP(B7,C2:F3,2,FALSE)

Hlookup01

If the region name is not found in the first row of the lookup table, the HLOOKUP formula result is #N/A

Hlookup02

Example 2: Find Rate for Selected Date

Usually, an exact match is required when using HLOOKUP, but sometimes an approximate match works better. For example, if rates change at the start of each quarter, only those dates are entered as column headings. Then, with HLOOKUP and an approximate match, you can find the rate that was in effect for that date. In this example:

  • a date is entered in cell C5
  • the rate lookup table has two rows, and is in range C2:F3
  • the lookup table is sorted by the Date row, in ascending order
  • rate is in row 2 of the table.
  • TRUE is used in the last argument, to find an approximate match for the lookup value.

The formula in cell D5 is:

=HLOOKUP(C5,C2:F3,2,TRUE)

If the date is not found in the first row of the lookup table, the HLOOKUP formula result is the next largest value that is less than lookup_value. The lookup value in this example is March 15th. That value is not in the date row, so the value for January 1st (0.25) is returned.

Hlookup03

Download the HLOOKUP Function File

To see the formulas used in today’s examples, you can download the HLOOKUP function sample workbook. The file is zipped, and is in Excel 2007 file format.

Watch the HLOOKUP Video

To see a demonstration of the examples in the HLOOKUP function sample workbook, you can watch this short Excel video tutorial.

YouTube link: Find Table Data with HLOOKUP Function

_____________

You may also like...

6 Responses

  1. […] Quite the feat, considering since she also cranks out a video tutorial with each one. Anyway, the 10th function she covered was HLOOKUP. And as you may have guessed, poor old HLOOKUP didn’t even get one comment. So I’d like […]

  2. Contextures Blog » 30 Excel Functions in 30 Days: Conclusion says:

    […] HLOOKUP […]

  3. Fred Chidester says:

    Played with it some more, changed the format of the dates to read 01/01/11 etc. Ran back three years across with different rates. When entering the date in the cell it worked very well. It was easier than 1-Jan and I went back to 2009. Now I need to find some use for it, besides a look up. I have a problem with some of the parts in that you now have a cell with the answer, what is the next step so that you can use that answer someplace. Right now you need to re-enter it to make it work in a formula because if you used that answer in that cell to complete a rate, as soon as you changed it to do another computation it would change the ones before it, no? Sorry for such a question but?

  4. Hi Fred,
    I’ll have another HLOOKUP example on the blog tomorrow, to show how it can be used in multiple cells.

  5. Contextures Blog » Excel Function Friday: HLOOKUP Finds Current Price says:

    […] Day 10 of the 30 Excel Functions in 30 Days series, we looked at the Excel HLOOKUP function. It’s similar to VLOOKUP, but looks for values in a horizontal list, instead of a vertical […]

  6. Rory says:

    Wonderful tuto’s will definately make an investment into your book :)

Leave a Reply

Your email address will not be published. Required fields are marked *