Why does the hlookup function return 0?

Question

Why does this hlookup function return 0?

A1 = 2, B1 = 0, C1 = 0

=hlookup(3,A1:C1,1,true)

Reading the description here: https://support.microsoft.com/en-us/office/hlookup-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f, it says for the range parameter (the last parameter):

"If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned"

The next largest value is 2 (in A1) which is less than the lookup value (3).

Using MS Office 365 Apps for enterprise

Answer 1

The HLOOKUP function returns 0 because the value 3 is not found in the lookup range A1:A3. The HLOOKUP function performs a horizontal search, i.e., it searches for the lookup value vertically in the first row of the lookup range.

Since the lookup value 3 is not found in the range A1:A3, which only contains the values 2, 0, and 0, the HLOOKUP function returns the value specified in the result_vector argument, which in this case is 1. When the value is not found and the range_lookup argument is set to True (which it is in this case), the HLOOKUP function returns the closest match that is less than the lookup value. In this case, since there is no match, it returns 0.

Answer 2

HLOOKUP will require that the values be sorted when using TRUE:

https://support.microsoft.com/en-us/office/hlookup-function-a3034eec-b719-4ba3-bb65-e1ad662ed95f#:~:text=If%20range_lookup%20is%20TRUE%2C%20the%20values%20in%20the%20first%20row%20of%20table_array%20must%20be%20placed%20in%20ascending%20order

But we can use MAXIFS if they are numbers:

=MAXIFS(A1:C1,A1:C1,"<="&3)

OR XLOOKUP which does not care if sorted or not.

=XLOOKUP(3,A1:C1,A1:C1,"",-1)