Excel HLOOKUP Function and Formula with Example


How to use the HLOOKUP Function and Formula in Excel

In this tutorial, you'll learn how to use the HLOOKUP function and formula in Excel. HLOOKUP() is a function to search for a value in the top row of a table and then retrieve data from the same column by a row number in the table.

HLOOKUP Syntax

The syntax of the HLOOKUP function is as follows:

=VLOOKUP(lookup_value, table_array, row_index_number, [range_lookup])

Return Value

The matched value from a table.

Arguments

  • lookup_value: The value to look up, located in the first row of the table
  • table_array: The table in which to look up data
  • row_index_number: The row number from which to retrieve data
  • range_lookup: Optional. TRUE or FALSE. Set it to FALSE if you want an exact match, and TRUE for an approximate match.

Excel HLOOKUP Examples

The following is a demo list of item information we're going to use in the examples:

Table 1:

 ABCDE
1Item CodeItem NameQtyUnit Cost ($)Unit Price ($)
2Item111ASUS Desktop PC 11110300330
3Item112ASUS Desktop PC 11220150750
4Item113ASUS Desktop PC 11325210234
5Item114 ASUS Desktop PC 1142010001100
6Item115 ASUS Desktop PC 1153010001110
7Item116 ASUS Desktop PC 11648250280
8Item117ASUS Desktop PC 11740270300

Table 2:

ABCD
1Item CodeItem111Item112Item113
2Item NameASUS Desktop PC 111ASUS Desktop PC 112ASUS Desktop PC 113
3Qty102025
4Unit Cost ($)300150210
5Unit Price ($)330750234

Example 1: Search for an Exact Match

In this example, let's say you want to retrieve the value of a cell located at row number 5 of column A, and the lookup value in the first row is Item Code (in Table 1) . Then the HLOOKUP formula is as follows:

=HLOOKUP("Item Code", A1:E8, 5, FALSE)

Here, the 4th argument (rang_lookup) set to FALSE, so HLOOKUP() searches for the exact match and returns Item114.

Similarly, if you want to retrieve the value from the 6th or 7th row of the same column, change row_index_number to 6 or 7.

As you can see, HLOOKUP returns the item name, but you can not retrieve the other associated data. 

In the case of Table 1, if you want to retrieve the other associated data of the item, such as item name, unit price, or unit cost, use the VLOOKUP function.

Now, let's use Table 2 and take a look at another example:

=HLOOKUP("Item111" ,B1:H5, 2, FALSE)

Here, HLOOKUP() searches for Item111 in the range B1:H5, and returns the item name if found.

The above formula returns ASUS Desktop PC 111.

Similarly, if you want to retrieve the unit price of this item, simply change row_index_number to 5.

Example 2: HLOOKUP with Array

In this example, I show how to use the HLOOKUP function to search for a value in an array. 

Let's take a look at the following example:

=HLOOKUP("B", {"A","B","C","D";10,20,30,40;230,240,250,260}, 2, TRUE)

Here, the 2nd argument is an array of three rows and four columns. HLOOKUP searches for letter B, located in the first row and second column, and returns the value (20) at row number 2.

Example 3: HLOOKUP with the Wildcard Character (*)

HLOOKUP also allows you to perform a partial matching.

In this example, you'll learn how to use the wildcard character (*) in a lookup value to perform a partial matching. 

Now let's take a look at an example:

=HLOOKUP("Item*", A1:E8, 8, FALSE)

Here, lookup_value, the first argument, set to Item* and range_lookup,  the 4th argument set to FALSE

HLOOKUP performs an exact matching and searches for any string that starts with Item and ends with any sequence of characters. 

The above formula returns Item117.

Note that the wildcard character (*) matches any sequence of characters. For example, lookup *home matches any text string that comes before home and ends with the word home.

Example 4: HLOOKUP with the Wildcard Character (?)

In this example, you'll learn how to use the wildcard character (?) in lookup_value to perform a partial matching.
The following illustrates an example of using both ? and * in the lookup value:

=HLOOKUP("??em*???e", A1:E8, 8, FALSE)

The lookup_value  ??em*???e matches any two characters string or text before em, and any string or text with at least four characters between em and e

The above formula returns Item117.

Note that the wildcard character (?) matches any single character. For example, the lookup value ??home matches any two characters that come before home.

Example 5: Find an Actual Asterisk (*) or Question Mark (?)

In this example, I show you how to find an actual asterisk (*) or question mark (?).

For example, let's say you have lookup_values Item* and Item~*. The former matches any sequence of characters after Item, and the latter matches Item*.

Note that you can use the wildcard characters in lookup_value only if range_lookup is FALSE, and the lookup_value is a text string. If you want to search for value with an actual asterisk (*) or question mark (?), use a tilde (~) before the character.

In this tutorial, you've learned how to use the Excel HLOOKUP function and formulaHLOOKUP() is a function used to search for a value in the top row of a table or array, and retrieve data from the same column by a row number.

Leave a Reply

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