Skip to content

Excel function · Lookup & reference

Excel HLOOKUP function

HLOOKUP finds a value in the top row of a table and returns the value from a row you choose in the same column: sales for a month name, a shipping fee for a weight band.

In Turkish Excel: YATAYARA

What it does

HLOOKUP is VLOOKUP turned on its side. Use it when your data runs across rather than down: months in columns, line items in rows. The function searches the top row of the range from left to right and, in the column where it finds a match, returns the row you asked for.

The last argument sets the match type. FALSE or 0 accepts only an identical value. With TRUE or nothing at all, Excel looks along a top row sorted from smallest to largest and takes the last value that is less than or equal to the lookup value. That is what you want for weight, amount or score bands.

In Microsoft 365 and Excel 2021, XLOOKUP searches both across and down and does not make you count rows. To flip a wide table into a tall one, use TRANSPOSE; for a lookup that works in every version, INDEX with MATCH does the same job.

Syntax

English Excel=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Turkish Excel=YATAYARA(aranan; tablo; satır_no; [aralık_bak])

Arguments in square brackets are optional. English Excel separates arguments with commas (,) and uses a dot for decimals; Turkish Excel uses semicolons (;) and a decimal comma. Excel shows function names in its own language when it opens the file.

Arguments

ArgumentRequiredDescription
lookup_valueRequiredThe value to find in the first row of the table, or the cell that holds it (e.g. G2). Text lookups are not case-sensitive.
table_arrayRequiredThe range to search. The lookup value must be in the first row of this range. Lock it, like $B$1:$E$4, if you will copy the formula.
row_index_numRequiredThe position of the row to return, counted inside the range: the first row of the range is 1. If the range starts at the header row, the first data row is 2.
range_lookupOptionalFALSE or 0: exact match. TRUE, 1 or omitted: approximate match, which needs the first row sorted from smallest to largest, left to right.

Examples

1Get one month's figures from a monthly table

Months run across B1:E1 and line items sit in the rows below. The sales for the month typed in G2 should appear in H2 and the costs in I2. Sales is row 2 of the range and costs row 3; we look up a month name, so the last argument is FALSE.

H2fx=HLOOKUP(G2,$B$1:$E$4,2,FALSE)
Get one month's figures from a monthly table: example table
ABCDEFGHI
1ItemJanFebMarAprMonthSalesCosts
2Sales182,000167,500201,300194,800Mar201,300133,900
3Costs121,000118,400133,900127,250
4Staff14141516
  • H2=HLOOKUP(G2,$B$1:$E$4,2,FALSE)
  • I2=HLOOKUP(G2,$B$1:$E$4,3,FALSE)

Mar is in the third column of the header row, so the formulas return rows 2 and 3 of that column: sales 201,300, costs 133,900. Type Apr in G2 and both figures switch to April.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

2Find a shipping fee from a weight band

The rate table sits across F2:I3: the lower bound of each band on top, the fee below. The bounds are sorted from left to right, so the last argument is TRUE; HLOOKUP finds the largest bound that is less than or equal to the weight.

C2fx=HLOOKUP(B2,$F$2:$I$3,2,TRUE)
Find a shipping fee from a weight band: example table
ABCDEFGHI
1ParcelWeight (kg)Shipping FeeRates
2PCL-3013.085.00From (kg)051020
3PCL-3027.5120.00Fee85120165240
4PCL-30312.0165.00
5PCL-30425.0240.00
6PCL-3055.0120.00
  • C2 (filled down to C6)=HLOOKUP(B2,$F$2:$I$3,2,TRUE)

PCL-302 weighs 7.5 kg, between 5 and 10, so it pays 120.00; PCL-305 at exactly 5 kg falls into the same band: 120.00. Anything from 20 kg up is charged the last band: 240.00.

We recalculated this example in real Excel (Microsoft 365, 16.0) and compared the results: they match.

Common errors and fixes

#N/A

Cause: The lookup value is not in the first row of the table. With approximate match, the lookup value is smaller than the smallest value in that row.

Fix: Compare the spelling with the header and remove extra spaces with TRIM. To show a message instead, wrap the formula in IFERROR.

#REF!

Cause: row_index_num is larger than the number of rows in table_array, for example row 5 of a four-row range.

Fix: Extend the range down so it includes the row you want, or correct the row number.

#VALUE!

Cause: row_index_num is less than 1 or is not a number.

Fix: Use a whole number of 1 or more.

Wrong result

Cause: The last argument was left empty, so Excel used approximate match on unsorted headers and returned a value from another column.

Fix: When looking up a month name or a code, always pass FALSE as the last argument.

Good to know

  • Instead of counting rows by hand, let MATCH find the row: =HLOOKUP(G2,$B$1:$E$4,MATCH("Costs",$A$1:$A$4,0),FALSE). The formula keeps finding the right row when rows are inserted.
  • If every new record is added at the bottom of the table, your data is really vertical; VLOOKUP or XLOOKUP is the more natural choice.

In Nexell

Get Nexell to do it from a sentence

Type this in Nexell

Add the shipping fee for each parcel from the horizontal rate table, based on its weight

Convert to table

Bring the parcel list and the rate table into the tool and type this. Nexell builds the match with a real Excel lookup formula and shows each parcel's fee in the preview; in the downloaded file, the fees update when the rates change.

Nexell uses AI to understand the request and builds the result with real Excel formulas; the formula may differ from the example on this page. You see a preview first and nothing changes until you press Apply; formulas stay live in the downloaded .xlsx file.

Free account · no card needed · 30 credits a month

Frequently asked questions

What is the difference between HLOOKUP and VLOOKUP?

They work the same way in different directions. VLOOKUP searches the first column and returns a column to its right; HLOOKUP searches the first row and returns a row below it. Use HLOOKUP when months run across the sheet and VLOOKUP when records run down it.

Can HLOOKUP return a value above the lookup row?

No; it only returns rows below the lookup row. For a row above it, use XLOOKUP or INDEX with MATCH.

Related functions

All lookup & reference functions →

Stop memorising formulas. Describe them.

Bring your table into the tool and say what you need; Nexell builds the formula and shows it in a preview.

Try it free