← All functions

HLOOKUP

Quirk found

Category: Lookup and reference · Last tested 2026-07-04

Support matrix

Engine	Documented	Live-tested	Verdict
Excel	Yes	Not yet	n/a
Google Sheets	Yes	Not yet	n/a
LibreOffice Calc	Yes	Yes (24.2.7.2, 2026-07-04)	Quirk found

Discovered quirks

=HLOOKUP("a",A1:C2,5,FALSE) on LibreOffice Calc returned #VALUE!, but the documented/expected result is #REF!. MISMATCH vs expected: expected '#REF!', got '#VALUE!'

Executed test cases

LibreOffice Calc 24.2.7.2 (tested 2026-07-04)

Formula	Description	Result	Expected	Verdict
=HLOOKUP("b",A1:C2,2,FALSE)	Exact match (range_lookup=FALSE) on a horizontal lookup table	2	2	Matched
=HLOOKUP(6,A1:E2,2,TRUE)	Approximate match (TRUE) requires the first row sorted ascending; returns the largest header <= lookup_value	50	50 https://support.microsoft.com/en-us/office/hlookup-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f -- approximate match finds the next largest value that is <= lookup_value; 6 falls between 5 and 7, so it matches 5 -> 50	Matched
=HLOOKUP("z",A1:C2,2,FALSE)	Exact match with no match found -> #N/A	#N/A	#N/A	Matched
=HLOOKUP("a",A1:C2,5,FALSE)	row_index_num greater than the number of rows in table_array -> #REF!	#VALUE!	#REF!	Mismatch

Docs & syntax

Excel: official documentation
Google Sheets: official documentation
LibreOffice Calc: official documentation