Practice free

HLOOKUP function

HLOOKUP searches across the top row of a table for a match and returns a value from a row below it. It is the sideways twin of VLOOKUP, now mostly replaced by XLOOKUP.

Example

Read it as a sentence: look for Qty across the top row of B1:E7, go down to the 3rd row, and accept only an exact match, which gives 7.

Practice this in the app

Syntax

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Arguments in [square brackets] are optional.

lookup_value (required)
Value to find in the top row.
table_array (required)
The table, with the lookup row at the top.
row_index_num (required)
Row of the table to return from, counting the top row as 1.
range_lookup (optional)
FALSE for exact match; TRUE or omitted for approximate (sorted ascending).

Returns: The matching value from the chosen row.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=HLOOKUP(15,{10,20,30;"a","b","c"},2) a Approximate
=HLOOKUP(9.5,{0,1,5,50;"Walk","Short cab","Long cab","Train"},2,TRUE) Long cab Site: approximate match into bands
=HLOOKUP("Qty",Items!B1:E7,3,FALSE) 7 Exact
=HLOOKUP("qty",Items!B1:E7,3,FALSE) 7 Case-insensitive
=HLOOKUP("Qt*",Items!B1:E7,2,FALSE) 12 Wildcard
=HLOOKUP("Qty",Items!B1:E7,"3",FALSE) 7 Text row index
=HLOOKUP(5,{10,20,30;"a","b","c"},2,TRUE) #N/A Below first
=HLOOKUP("Zither",Items!B1:E7,2,FALSE) #N/A Not found

The Items sheet these formulas read

Watch out for

  • Omitting the last argument uses approximate match, which can return wrong answers on unsorted data; use FALSE.
  • Row index is fixed, so inserting rows can break it.
  • Cannot look upward.

Where it works

Excel
HLOOKUP is in every version of Excel from 2007 on.
Google Sheets
Same name, same behaviour for everyday use.

Part of Lookup and reference functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .