## 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 **Example**s

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

Table 1:

A | B | C | D | E | |

1 | Item Code | Item Name | Qty | Unit Cost ($) | Unit Price ($) |

2 | Item111 | ASUS Desktop PC 111 | 10 | 300 | 330 |

3 | Item112 | ASUS Desktop PC 112 | 20 | 150 | 750 |

4 | Item113 | ASUS Desktop PC 113 | 25 | 210 | 234 |

5 | Item114 | ASUS Desktop PC 114 | 20 | 1000 | 1100 |

6 | Item115 | ASUS Desktop PC 115 | 30 | 1000 | 1110 |

7 | Item116 | ASUS Desktop PC 116 | 48 | 250 | 280 |

8 | Item117 | ASUS Desktop PC 117 | 40 | 270 | 300 |

Table 2:

A | B | C | D | |

1 | Item Code | Item111 | Item112 | Item113 |

2 | Item Name | ASUS Desktop PC 111 | ASUS Desktop PC 112 | ASUS Desktop PC 113 |

3 | Qty | 10 | 20 | 25 |

4 | Unit Cost ($) | 300 | 150 | 210 |

5 | Unit Price ($) | 330 | 750 | 234 |

**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 I**tem117**.

Note that the wildcard character (*) matches any sequence of characters. For example, lookup*homematches any text string that comes beforehomeand ends with the wordhome.

**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 I**tem117**.

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

**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 inlookup_valueonly ifrange_lookupis 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 formula**. **HLOOKUP**() 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.