- Advanced Excel Functions Tutorial
- Advanced Excel Functions - Home
- Compatibility Functions
- Advanced Excel Functions - Cube
- Database Functions
- Date & Time Functions
- Engineering Functions
- Financial Functions
- Information Functions
- Advanced Excel Functions - Logical
- Lookup & Reference Functions
- Math & Trignometric Functions
- Statistical Functions
- Useful Resources
- Quick Guide
- Useful Resources
- Discussion
Lookup and Reference - LOOKUP Function
Description
Use LOOKUP function when you need to look in a single row or column and find a value from the same position in a second row or column. Use the LOOKUP Function to search one row or one column.
Use VLOOKUP Function to search one row or column, or to search multiple rows and columns (like a table). It is a much improved version of LOOKUP.
There are two ways to use LOOKUP −
Vector form − Use this form of LOOKUP to search one row or one column for a value. Use the vector form when you want to specify the range that contains the values that you want to match.
Array form − Microsoft strongly recommends using VLOOKUP or HLOOKUP instead of the array form. The array form is provided for compatibility with other spreadsheet programs, but its functionality is limited.
An array is a collection of values in rows and columns (like a table) that you want to search.
To use the array form, your data must be sorted.
Vector form
The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range.
Syntax
LOOKUP (lookup_value, lookup_vector, [result_vector])
Arguments
Argument | Description | Required/ Optional |
---|---|---|
lookup_value | A value that LOOKUP searches for in the lookup_vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value. |
Required |
lookup_vector | A range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values. The values in lookup_vector must be placed in ascending order: ..., -2, -1, 0, 1, 2... A-Z, FALSE, TRUE. Otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent. |
Required |
result_vector | A range that contains only one row or column. The result_vector argument must be the same size as lookup_vector. |
Optional |
Notes
If the LOOKUP Function cannot find the lookup_value, the Function matches the largest value in lookup_vector that is less than or equal to lookup_value.
If lookup_value is smaller than the smallest value in lookup_vector, LOOKUP returns the #N/A error value.
If the lookup_vector is not in ascending order, LOOKUP returns the #N/A error value.
If the LOOKUP Function is attempting to reference cells that do not exist, LOOKUP returns the #REF! Error value. i.e. when
Cells being deleted after the Lookup function has been entered.
Relative references in the Lookup function, which become invalid when the function is copied to other cells.
If the contents of the cells that are being compared have different data types, LOOKUP might not return the correct value.
If there are unseen spaces at the start or end of either the lookup_value, or in the cells of lookup_vector, LOOKUP might not return the correct value.
Array form
You can consider using VLOOKUP or HLOOKUP instead of the array form. The array form of LOOKUP is provided for compatibility with other spreadsheet programs, but its functionality is limited. The array form of LOOKUP looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the array.
Use this form of LOOKUP when the values that you want to match are in the first row or column of the array.
Syntax
LOOKUP (lookup_value, array)
Arguments
Argument | Description | Required/ Optional |
---|---|---|
lookup_value | A value that LOOKUP searches for in an array. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value. |
Required |
array | A range of cells that contains text, numbers, or logical values that you want to compare with lookup_value. The values in array must be placed in ascending order: ..., -2, -1, 0, 1, 2... A-Z, FALSE, TRUE. Otherwise, LOOKUP might not return the correct value. Uppercase and lowercase text are equivalent. |
Required |
Notes
If the LOOKUP Function cannot find the lookup_value, the Function matches the largest value in the array that is less than or equal to lookup_value.
If lookup_value is smaller than the smallest value in the first row or column (depending on the array dimensions), LOOKUP returns the #N/A error value.
If the LOOKUP Function is attempting to reference cells that do not exist, LOOKUP returns the #REF! Error value. i.e. when
Cells being deleted after the Lookup function has been entered.
Relative references in the Lookup function, which become invalid when the function is copied to other cells.
If the contents of the cells that are being compared have different data types, LOOKUP might not return the correct value.
If there are unseen spaces at the start or end of either the lookup_value, or in the cells of array, LOOKUP might not return the correct value.
The array form of LOOKUP is very similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for the value of lookup_value in the first row, VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of array.
If array covers an area that is wider than it is tall (more columns than rows), LOOKUP searches for the value of lookup_value in the first row.
If an array is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column.
With the HLOOKUP and VLOOKUP functions, you can index down or across, but LOOKUP always selects the last value in the row or column.
Applicability
Excel 2007, Excel 2010, Excel 2013, Excel 2016