Function Reference: HLOOKUP, VLOOKUP

Syntax
=HLOOKUP(lookup_value,table_array,row_index_num,[range_lookup])
=VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])
Purpose / Description
Returns a value from a column in a table or array that from a given row (HLOOKUP) or column (VLOOKUP), where the key value is found in the first row (HLOOKUP) or first column (VLOOKUP).  Can match the key value exactly, or on a threshold basis.
Arguments
Name Type Description
lookup_value any A constant, reference or a nested expression that returns the value to be found.  HLOOKUP will endeavour to match this value in the first row of the range or array passed to  table_array.  VLOOKUP will endeavour to make the match in the first column.
table_array reference or
array of any		 A constant array, reference to a range, or a nested expression that returns a reference to a range or an array of values, within which the match will be made and from which the return value will be retrieved if a match is successfully made.  In the function arguments box, this is incorrectly shown as requiring number value type.
row_index_num   number A constant, reference to a range, or a nested expression that returns a number indicating the row within the table or array the value is to be retrieved from.  Any decimal portion of the value is ignored.  Applies to HLOOKUP only.
col_index_num   number A constant, reference to a range, or a nested expression that returns a number indicating the column within the table or array the value is to be retrieved from.  Any decimal portion of the value is ignored.  Applies to VLOOKUP only.
[range_lookup]   logical An optional constant, reference to a range or nested expression that returns a flag to indicate if a range lookup will be undertaken.  Defaults to TRUE if omitted.
TRUE A range lookup is undertaken.  Described in online help and tooltip as Approximate Match, we prefer the term Threshold Match.
The match is made on the last largest item, less than or equal to the lookup_value.  For this to return reliable results, the table MUST be sorted in ascending order.  The sort order matters, as the function does a binary search, sampling the key values, and if the data is unsorted, it may misconstrue which is the best match.
FALSE An exact match  is undertaken.  The match is made on the first item exactly matching lookup_value.  Sort order of the data is irrelevant, as the function searches exhaustively top to bottom.
Return Values
Type Specific Value Description
any   The value in the specified row (HLOOKUP) or column(VLOOKUP) within table_array which is aligned with the matched value.
error #N/A For a range lookup (threshold matching), the lookup_value < the first entry in table_array.  So long as the lookup_value at least matches the first entry, it will always return an answer, even if it isn't the one expected.
For an exact match lookup, it indicates that having exhaustively searched the key row or column, no exact match was found in the table or array.
error #VALUE! If row_index_num or col_index_num is not a number and cannot be coerced to a number, or range_lookup is not a logical and cannot be coerced to a logical.
If row_index_num is <1 or col_index_num <1
error #REF! If row_index_num >= count of rows in table plus one.
If col_index_num >= count of columns in table plus one.
error   Any errors referenced by arguments are returned due to propagation of error values.
Notes / Comments / Advice / Warnings
Note: Matching always occurs in the first row for HLOOKUP and always in the first column for VLOOKUP.
Note: The value type of the lookup_value is important. As the argument is specified as "any", it will match numbers, text strings and logicals, but only against the same value types. A text_string will notmatch against a number and vice versa.
Comment: This function was widely used in the past, since it allowed for independent lookup and result arrays, but if exact matches were required, some overhead was incurred.
Advice: Replace all reference forms of the HLOOKUP and VLOOKUP function with the improved XLOOKUP.  It allows exact matching (the default), and can work with ascending and descending sorted ranges, and do exhaustive search where data is unsorted.  It also has built-in error handling for when no match is made.  With the advent of XLOOKUP, we have effectively ceased using LOOKUP, HLOOKUP and VLOOKUP altogether.
Caution: Omitting the last argument - range_lookup means that the default value, for a threshold lookup will be used.  This is not what most users expect and leads to some real problems.
Examples: Open / download sample workbook: LOOKUP, HLOOKUP, VLOOKUP.xlsx
 
Tags: