Photo of magnifying glass.

What is HLookup

/ Tutorials / By

What is HLookup?

HLookup searches for a value in the top row of a table and then returns a value in the same column from a row that you specify. HLookup is useful when comparison values are located in a row across the top of a table, and the desired values are in a specified number of rows below.

HLookup is useful if your data is organized in a by-column table. See Lookup Functions in Excel for examples of data table organization methods, and which lookup functions are appropriate for each.

Syntax

  • HLookup([Lookup Value],[Table],[Row Index],[Approximate Match])
    • Lookup Value is required. It is the value to be found in the first row of the table. It can be a number, a reference or a text string.
    • Table is required. It is a table of information in which data is to be looked up. The function will try to find Lookup Value in the first row of the table.
    • Row Index is required. It is the row number of the table whose value will be returned. IMPORTANT: this is the row number of the table, not the row number of the cell. For example, if the table range is D4:G7, then row 2 of the table would be row 5 of the cell in the worksheet.
    • Approximate Match is optional. If approximate match is omitted, the default is TRUE. Alternatively, you can enter 1 for TRUE and 0 for FALSE.
      • FALSE: an error will be returned if Lookup Value is not found in the first row of Table.
      • TRUE: (or omitted):
        • The first row of Table must be sorted in ascending order, or HLookup could give an incorrect result. See example 5, below.
        • If no exact match is found, HLookup will return the last value in the table that is less than the Lookup Value.

Examples

Excel worksheet excerpt illustrating use of HLookup function
Examples of HLookup Function

In each of the examples, the lookup value is in column C, the table is defined in column D, the row index is in column E, the approximate match value is in column F and the returned result appears in column G. The full HLookup formula appears in column H.

Notes to Examples

  1. In example 1, HLookup is looking for the value “Toyota” in row 4, the top row of the table. Since “Toyota” appears in column F, and looking at row 2 of the table (cell F5) we see the value Japan. Even though Approximate Match was true, there was an exact match in this example.
  2. HLookup is looking for the value “Mazda” in row 4 and will accept an approximate match. Because alphabetically, Mazda falls between G.M. and Toyota in the top row of the table, HLookup selects the last value that is smaller than Mazda, which is G.M. The function returns U.S., the value in row 2 of the table in column E.
  3. Example 3 is identical to example 2, except now we are looking for an exact match. Since “Mazda” does not appear in the top row of the table, the function returns a #N/A error.
  4. In Example 4 we are searching for “Fiat” in the table. Since “Fiat” is less than the smallest value in the top row of the table (“Ford”), even though we have approximate match enabled, the function returns a #N/A error.
  5. In Examples 5 and 6 we have changed the top row of the table to row 5 of the worksheet, the Headquarters row. Since row 5 is not sorted in ascending order, HLookup does not work properly for approximate matches. The first value in the top row of the table is “U.S.” which alphabetically falls after “Japan”. Similar to Example 4, HLookup stops searching at the first column and returns a #N/A error.
  6. Example 6 is identical to Example 5, except now we are looking for an exact match. Since “Japan” occurs in column F, the function correctly returns the value in row 2 which is 340,000.
  7. Example 7 is identical to Example 6, except now we are looking for row 4 of the table. Since there are only 3 rows in the table for this example (rows 5, 6 and 7) the function returns a #REF! error.

Common Error Conditions

  • #N/A – The Lookup Value could not be found in the top row of the Table. If Approximate Match is True, then the Lookup Value is smaller than the value in the first column of the top row of the Table. If Approximate Match is False, then the Lookup Value does not occur in the top row of the table.
  • #REF! – The Row Index value is either less than zero, or is greater than the last row of the table. See example 7 above.

What are alternatives to HLookup?

  • XLookup – always preferred if you are using latest version of Excel.
  • Index, possible combined with Match. Consider this if the row that contains the value that you want is above the first row of the table. In our examples above, if we were looking for which company had 340,000 worldwide employees HLookup would not work. Our alternative would be to use XLookup or Index.

Privacy Policy