## 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 associated data from the same column by a row number.

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

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

### Excel HLOOKUP **Example**s

Demo data:

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 |

**Difference between Table 1 and Table 2**

**Difference between Table 1 and Table 2**

In this example, you'll learn the difference between *Table 1* and *Table 2* when you use the HLOOKUP function.

The following example searches for *Item Code* located at the intersection of row number 5 and column A in *Table 1*:

=

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

The above formula returns *Item114.*

However,** HLOOKUP** can't retrieve the other associated data of *Item114*, such as *Item Name*, *Qty*, *Unit Cost*, and *Unit Price*.

To retrieve the associated data, you need to use the **VLOOKUP** function.

The following example searches for *Item Code* ** Item111** in the range B1: H5 in Table 2 and returns

*Item Name*ASUS Desktop PC 111

*:*

=

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

Similarly, you can get the *Unit Price* by changing ** row_index_number **to 5.

**HLOOKUP**() can return the associated data of a lookup value in Table 2

**Searching for a value in an array**

**Searching for a value in an array**

You can use the HLOOKUP function to search for a value in an array, as the following example shows:

=

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

There are three rows and four columns in the array specified above.

**HLOOKUP**() searches for letter ** B**,

**located in the first row and second column.**

The above formula returns 20.

**Partial Matching (*)**

**Partial Matching (*)**

**HLOOKUP** allows wildcard characters (*) and (?) in a lookup value.

You can perform a partial matching using HLOOKUP, as the following example shows:

=

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

**HLOOKUP**()** **searches for *Item Code* that begins with *Item* and ends with *any sequence of characters. *

The above formula returns *Item117*.

Note that the wildcard character (*) matches any sequence of characters.

**Another example of Partial Matching**

**Another example of Partial Matching**

Here is another example of a partial matching:

=

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

Here, we've used both (?) and (*) in the lookup value.

The above example returns *Item117*.

Note that the wildcard character (?) matches any single character.

In this tutorial, you've learned how to use the **Excel HLOOKUP function and formula**.

**HLOOKUP()** searches for a value in the top row of a table or array and retrieve the associated data from the same column by a row number.