HLOOKUP

Summary

HLOOKUP searches horizontally and returns the matching column's value from row_index_num.

Remarks

• row_index_num is 1-based and must be within the table height.
• range_lookup defaults to TRUE, matching Excel and LibreOffice.
• When range_lookup=TRUE, approximate match logic is used against the first row.
• In exact mode, a blank candidate never matches. A blank lookup value matches a real numeric zero, but not blank, text, or boolean candidates.
• Approximate matching assumes the first row is sorted ascending. Unsorted data is detected and returns #N/A rather than a silently wrong column, matching LibreOffice Calc (#283).
• Numeric range_lookup values use logical coercion: zero is exact and nonzero is approximate.
• If the lookup value is not found, returns #N/A.
• If row_index_num is invalid, returns #REF! (or #VALUE! if non-numeric).
• A matched empty target cell is materialized as numeric 0.

Examples

Related functions

• VLOOKUP
• XLOOKUP
• MATCH
• CHOOSE
• CHOOSECOLS
• CHOOSEROWS

FAQ

Does HLOOKUP default to exact or approximate matching?

It defaults to approximate matching because range_lookup defaults to TRUE; pass FALSE or 0 for exact matching.

How are invalid row_index_num values reported?

If row_index_num is outside table height HLOOKUP returns #REF!; if it is non-numeric it returns #VALUE!.

Runtime metadata