Excel HLOOKUP Function
HLOOKUP Function
The HLOOKUP function is a premade function in Excel, which searches the top row of a table and returns a value from a row below it.
HLOOKUP is the horizontal version of VLOOKUP. VLOOKUP searches down the first column of a table. HLOOKUP searches across the top row.
It is typed =HLOOKUP and has the following parts:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Note: The row which holds the data used to lookup must always be the top row of the table.
Note: The different parts of the function are separated by a symbol, like comma , or semicolon ;
The symbol depends on your Language Settings.
Lookup_value: The value to search for. This is usually the cell where the search value is entered.
Table_array: The table range. HLOOKUP searches the top row of this range.
Row_index_num: The row to return a value from, counted from the top of the table range. The top row is row 1.
Range_lookup: Optional. FALSE (0) for an exact match, or TRUE (1) for an approximate match. Approximate match needs the top row sorted from smallest to largest. If you leave it out, TRUE is used.
Tip: Use FALSE (0) when you look up names or items, like in the example below. It finds the exact value, and it works whether the data is sorted or not.
How to use the HLOOKUP function:
- Select a cell (
B6) - Type
=HLOOKUP - Double click the HLOOKUP command
- Select the cell where the search value will be entered (
B5) - Type (
,) - Mark the table range (
B1:H3) - Type (
,) - Type the number of the row, counted from the top (
2) - Type (
,) - Type
FALSEfor an exact match - Hit enter
- Enter a value in the search cell (
B5)
Let's have a look at an example!
Here is a price list from the Poke Mart. The items are in the top row, with their price and category in the rows below. Copy it and paste it into cell A1 to follow along:
Example data
A B C D E F G H 1 Item Poke Ball Great Ball Ultra Ball Potion Super Potion Hyper Potion Revive 2 Price 200 600 1200 300 700 1200 1500 3 Category Balls Balls Balls Medicine Medicine Medicine Medicine
Type the labels Search Item in A5 and Price in A6.
Use the HLOOKUP function to find the Price of an item. Type Ultra Ball in B5:
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Item | Poke Ball | Great Ball | Ultra Ball | Potion | Super Potion | Hyper Potion | Revive |
| 2 | Price | 200 | 600 | 1200 | 300 | 700 | 1200 | 1500 |
| 3 | Category | Balls | Balls | Balls | Medicine | Medicine | Medicine | Medicine |
| 4 | ||||||||
| 5 | Search Item | Ultra Ball | ||||||
| 6 | Price | 1200 |
=HLOOKUP(B5, B1:H3, 2, FALSE)
HLOOKUP searches the top row of B1:H3 for Ultra Ball, and finds it in column D. Row 2 of the table holds the prices, so it returns 1200 from D2.
Column A only holds the labels, so it is left out of the table range.
Return a Different Row
Change row_index_num to return a different row. Row 3 of the table holds the category.
Type the label Category in A7, and the formula below in B7. Then type Revive in B5:
| A | B | |
|---|---|---|
| 5 | Search Item | Revive |
| 6 | Price | 1500 |
| 7 | Category | Medicine |
=HLOOKUP(B5, B1:H3, 3, FALSE)
Revive costs 1500, and it is in the Medicine category.
When There is No Match
With FALSE, HLOOKUP returns #N/A when the value is not in the top row. The price list has no Max Potion:
| A | B | |
|---|---|---|
| 5 | Search Item | Max Potion |
| 6 | Price | #N/A |
=HLOOKUP(B5, B1:H3, 2, FALSE)
With TRUE, HLOOKUP looks for an approximate match instead. The top row here is not sorted, so it could return the price of the wrong item, without any error.
HLOOKUP or XLOOKUP?
The XLOOKUP function also works horizontally. Give it the row to search in, and the row to return a value from:
=XLOOKUP(B5, B1:H1, B2:H2)
With Ultra Ball in B5, this also returns 1200. XLOOKUP needs no row number, and it uses exact match by default.
Note: XLOOKUP is only available in Excel for Microsoft 365, Excel 2021, Excel 2024 and Excel for the web.
In older versions, use HLOOKUP, or INDEX and MATCH:
=INDEX(B2:H2, MATCH(B5, B1:H1, 0))
This also returns 1200 for Ultra Ball.
