|
Classic file types are no longer available for use as of January 2021. You can transition your classic files or download a PDF. Learn More

Workiva Support Center

HLOOKUP

HLOOKUP

  • Last updated on

Description

Use this function to search for a value in the top row of a table or array and return a value in the same column from a specified row. Supported in Chains. 

HLOOKUP is useful for finding values across the top row of a table or array, and returning corresponding values from other rows.

 

Syntax

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

 

Inputs

This function accepts the following arguments:

Name Required Description Valid input
lookup_value Yes The value to be found in the first row of the table. A cell reference, a cell range, a number, a text string, or a formula which results in any of these. 
table_array Yes The table or array of data in which to search. A cell range.
row_index_num Yes The row number in the table from which to retrieve the value. The first row is 1. A positive integer, a reference to a cell containing a positive integer, or a formula which results in either of these.
range_lookup No Specifies whether to find an exact match or an approximate match. TRUE for approximate match, FALSE for exact match. If omitted, TRUE is assumed. A boolean value (TRUE or FALSE).

 

Examples

Sample data

  A B C D E
1 Item Price Stock Supplier Rating
2 Apple 1.20 150 Fresh Fruits 4.5
3 Banana 0.50 300 Tropical Supplies 4.0
4 Grape 2.00 200 Vineyard Delights 4.7
5 Orange 1.00 250 Citrus World 4.3

Sample formulas

Use case Formula Explanation and Result
Locate a given text string and return the equivalent value from the second row. =HLOOKUP("Price",A1:E5,2,FALSE)

This formula works in the following manner:

  • HLOOKUP(: This is the function itself.
  • "Price": The lookup_value to find in the first row.
  • A1:E5: The table_array to search.
  • 2: The row_index_num. The formula will return a value from the 2nd row of the table.
  • FALSE: Specifies an exact match for the lookup value.
  • The formula finds "Price" in B1 and returns the value from the 2nd row of that column (B2).

Result: 1.20
Locate a given text string and return the equivalent value from the fourth row. =HLOOKUP("Stock",A1:E5,4,TRUE)

This formula works in the following manner:

  • HLOOKUP(: This is the function itself.
  • "Stock": The lookup_value to find in the first row.
  • A1:E5: The table_array to search.
  • 4: The row_index_num. The formula will return a value from the 4th row of the table.
  • TRUE: Specifies an approximate match, but finds an exact match for "Stock" in C1.
  • The formula finds "Stock" in C1 and returns the value from the 4th row of that column (C4).

Result: 200.
Locate a given item in the first row, then return the value from a dynamically determined row position by finding the column that contains the specified header text. =HLOOKUP("Apple", A1:F5, MATCH("Price", A1:F1, 0), FALSE)

This formula works in the following manner:

  • MATCH("Price", A1:F1, 0): The MATCH function first finds the column position of "Price" in the header row A1:F1. It returns 2.
  • HLOOKUP("Apple", A1:F5, 2, FALSE): The outer function then searches for "Apple" in the *first row* (A1:E1) of the table.
  • Because "Apple" is not in the header row (A1:E1), the HLOOKUP function fails to find an exact match.

Result: #N/A.
Locate a specified value in the first row of a table and return the corresponding value from the specified row; if the value isn't found, display a custom message. =IFERROR(HLOOKUP("Pineapple", A1:F5, 3, FALSE), "Fruit not found")

This formula works in the following manner:

  • HLOOKUP("Pineapple", A1:F5, 3, FALSE): Attempts to find an exact match for "Pineapple" in the first row (A1:E1).
  • Because "Pineapple" is not found, the HLOOKUP function returns an #N/A error.
  • IFERROR(...): The IFERROR function catches the #N/A error and returns the specified alternative value, "Fruit not found".

Result: Fruit not found.
Retrieve a value from a specified row of a table by looking up a value in the first row and returning the corresponding entry from the matching row. =HLOOKUP(CELL("contents", B1), A1:F5, 3, FALSE)

This formula works in the following manner:

  • CELL("contents", B1): The CELL function finds the value of B1, which is "Price".
  • HLOOKUP("Price", A1:F5, 3, FALSE): The outer function searches for "Price" in the first row (A1:E1).
  • It finds "Price" in B1 and returns the value from the 3rd row (`row_index_num`=3) of that column (Column B).
  • The value in B3 is 0.50.

Result: 0.5.

 

Notes

  • HLOOKUP is case-insensitive. It doesn't distinguish between uppercase and lowercase letters.
  • If range_lookup is TRUE or omitted, an approximate match is returned. If FALSE, an exact match is returned.
  • If an exact match is not found, and range_lookup is TRUE, HLOOKUP returns the next largest value that is less than lookup_value.
  • If an exact match is not found, and range_lookup is FALSE, HLOOKUP returns #N/A.
  • Ensure that the table_array is sorted in ascending order when using TRUE for range_lookup to ensure correct results.

 

Tips

  • Use HLOOKUP when your data is organized in rows, with the values you want to look up located in the top row of the table.
  • For vertical lookups, consider using VLOOKUP instead.
  • To prevent errors, use IFERROR to handle cases where HLOOKUP returns #N/A.
  • Consider using named ranges to make your formulas easier to read and manage.
  • Combine HLOOKUP with other functions such as INDEX and MATCH for more flexible and powerful lookups.

 

Related functions

DescriptionUse this function to search for a value in the top row of a table or array and return a value in the same ...