Function Reference: LOOKUP
| Syntax | ||
| Reference Form =LOOKUP(lookup_value,lookup_vector,[result_vector]) Array Form =LOOKUP(lookup_value,array) |
||
| Purpose / Description | ||
| For the reference form, it matches the lookup_value in lookup_vector and returns the corresponding value from result_vector (if given), otherwise the value in lookup_vector upon which the match is made. For the array form, if array is wider than it is deep, matches the entry in the first row and returns the corresponding value from the last row. If array is square or deeper than it is wide, matches the entry in the first column, returning the result from the last column. |
||
| Arguments | ||
| Name | Type | Description |
| lookup_value | any | A constant, reference or a nested expression that returns the value to be found. In the reference form, the match will be made in lookup_vector, in the array form, it will be made in the first row if the array is wider than it is tall, otherwise the match will be made in the first column. |
| lookup_vector | reference | A reference to a range, or a nested expression that returns a reference to a range, within which the match will be made. Applies to the reference form of the function only. |
| [result_vector] | reference | An optional reference to a range, or a nested expression that returns a reference to a range, from which the result will be returned. Applies to the reference form of the function only. If omitted, the lookup_vector is used. |
| array | array of any | A constant, constant array or nested expression that returns an array of values within the which the match will be made and from which the result is returned. Applies to the array form of the function only. |
| Return Values | ||
| Type | Specific Value | Description |
| any | For the reference form of the function, returns the value in lookup_vector which is in the corresponding position within that range that the matched value is within the lookup_vector. If lookup_vector is omitted, it will be the matched value itself. For the array form, returns the value from the last row or column (depending upon array dimension) of array that corresponds to the matched value in the first row or column. If array is only one row deep or one column wide, it will be the matched value. |
|
| error | #N/A | No valid match is made. This will always occur if lookup_value < the first entry in lookup_vector or lookup_value < first entry in array. It may also occur if the lookup_vector or first row / column of array is not in ascending order. |
| error | Any errors referenced by arguments are returned due to propagation of error values. | |
| Notes / Comments / Advice / Warnings | ||
| Note: | Matching is only done on a threshold basis where the match is made on the last largest entry, less than or equal to the lookup_value. Exact matches cannot be made with LOOKUP. | |
| 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 LOOKUP function with the improved XLOOKUP. It allows exact matching, 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 stopped using LOOKUP, HLOOKUP and VLOOKUP altogether. | |
| Examples: | Open / download sample workbook: LOOKUP, HLOOKUP, VLOOKUP.xlsx | |