Buzz
The LOOKUP() function is an advanced search function in Excel, surpassing the capabilities of both VLOOKUP() and HLOOKUP().
This function can intelligently distinguish whether the lookup range is in row or column format for optimal processing.
This article provides a detailed overview of the LOOKUP() function in Excel.
Description
The LOOKUP() function retrieves a value from a data range consisting of either a single column or a single row, or from an array.
LOOKUP() is an advanced function derived from both VLOOKUP() and HLOOKUP() because it can distinguish whether the lookup range is in row or column format.
LOOKUP() function comes in two forms:
Array Form:
The function searches for the specified value in the first column or row of the array and returns the value at the same position in the last column or row of the array. Use the array form when the list has few values, and the values remain constant.
Vector Form:
The function searches for a value in a data range consisting of either a row or a column and returns a value at the same position in a second row or column. Use the vector form when the list to be searched contains multiple values or values that may change.
You can replace the IF() function with LOOKUP().
ARRAY FORM
Syntax
=LOOKUP(lookup_value, array)
In which:
- lookup_value: the value to search for in an array; it can be a number, text, logical value, name, or a reference to a value.
- array: the search range containing cells with text, numbers, or logical values you want to find the lookup_value within.
Note
- If the lookup_value is not found in the array, the function will use the largest value that is less than or equal to the lookup_value.
- If the lookup_value is smaller than the smallest value in the first column or row of the array, the function will return an error.
- If the array has more columns than rows, the LOOKUP() function will search for the lookup_value in the first row. Conversely, if the array is square or has more rows than columns, the function will search for the lookup_value in the first column.
- Values in the array must be sorted in ascending order for the most accurate results.
- LOOKUP() function is case-insensitive, meaning it does not differentiate between uppercase and lowercase text.
Example
1. Directly input the array into array.
2. Reference array to an existing array.
VECTOR FORM
Syntax
=LOOKUP(lookup_value, lookup_vector, [result_vector])
Where:
- lookup_value: the value to search for. Lookup_value can be a number, text, logical value, name, or a reference to a value.
- lookup_vector: the range containing the values to be searched, only containing a single row or column. Lookup_vector can be text, number, or logical values.
- result_vector: the range containing the result values, only containing a single row or column, and result_vector must have the same size as lookup_vector.
Note
- Values in the lookup_vector must be sorted in ascending order for the most accurate results.
- The function is case-insensitive.
- If lookup_value is not found in lookup_vector, the function uses the largest value in lookup_vector that is less than or equal to lookup_value.
- If the lookup_value is smaller than the smallest value in the lookup_vector, the function returns an error.
Example
This article provides a detailed description of the various forms of the LOOKUP() function in Excel. We hope that through this article, you will understand and know how to use the LOOKUP() function. Wish you success!
