# LOOKUP function

This article describes the formula syntax and usage of the **LOOKUP** function (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.) in Microsoft Excel.

## Description

The **LOOKUP** function returns a value either from a one-row or one-column range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) or from an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.). The **LOOKUP** function has two syntax forms: the vector form and the array form.

If you want to |
Then see |
Usage |
---|---|---|

Look in a one-row or one-column range (known as a vector) for a value and return a value from the same position in a second one-row or one-column range | Vector form | Use the vector form when you have a large list of values to look up or when the values may change over time. |

Look in the first row or column of an array for the specified value and return a value from the same position in the last row or column of the array | Array form | Use the array form when you have a small list of values and the values remain constant over time. |

** Notes **

- You can also use the
**LOOKUP**function as an alternative to the**IF**function for elaborate tests or tests that exceed the limit for nesting of functions. See the examples in the array form. - For the
**LOOKUP**function to work correctly, the data being looked up must be sorted in ascending order. If this is not possible, consider using the VLOOKUP, HLOOKUP, or MATCH functions.

## Vector form

A vector is a range of only one row or one column. 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. Use this form of the **LOOKUP** function when you want to specify the range that contains the values that you want to match. The other form of **LOOKUP** automatically looks in the first column or row.

### Syntax

LOOKUP(lookup_value, lookup_vector, [result_vector])

The **LOOKUP** function vector form syntax has the following arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

**lookup_value**Required. A value that**LOOKUP**searches for in the first vector.can be a number, text, a logical value, or a name or reference that refers to a value.*Lookup_value***lookup_vector**Required. A range that contains only one row or one column. The values incan be text, numbers, or logical values.*lookup_vector*

** Important ** 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.

**result_vector**Optional. A range that contains only one row or column. Theargument must be the same size as*result_vector*.*lookup_vector*

### Remarks

- If the
**LOOKUP**function can't find the, the function matches the largest value in*lookup_value*that is less than or equal to*lookup_vector*.*lookup_value* - If
is smaller than the smallest value in*lookup_value*,*lookup_vector***LOOKUP**returns the #N/A error value.

## Vector examples

The workbook below shows examples of this function. Inspect them, change existing formulas, or enter your own formulas to learn how the function works.

To work more in-depth with the example data in Excel, download the embedded workbook to your computer, and then open it in Excel.

## Array form

The array form of **LOOKUP** looks in the first row or column of an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) 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. Use the other form of **LOOKUP** when you want to specify the location of the column or row.

** Tip ** In general, it's best to use the **HLOOKUP** or **VLOOKUP** function instead of the array form of **LOOKUP**. This form of **LOOKUP** is provided for compatibility with other spreadsheet programs.

### Syntax

LOOKUP(lookup_value, array)

The **LOOKUP** function array form syntax has these arguments (argument: A value that provides information to an action, an event, a method, a property, a function, or a procedure.):

**lookup_value**Required. A value that**LOOKUP**searches for in an array. Theargument can be a number, text, a logical value, or a name or reference that refers to a value.*lookup_value*- If
**LOOKUP**can't find the value of, it uses the largest value in the array that is less than or equal to*lookup_value*.*lookup_value* - If the value of
is smaller than the smallest value in the first row or column (depending on the array dimensions),*lookup_value***LOOKUP**returns the #N/A error value. **array**Required. A range of cells that contains text, numbers, or logical values that you want to compare with lookup_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 ofin the first row.*lookup_value* - 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.

** Important ** 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.

## Array examples

The examples on Sheet2 in the workbook show the array form used with the function.

The workbook below shows examples of this function. Inspect them, change existing formulas, or enter your own formulas to learn how the function works.

To work more in-depth with the example data in Excel, download the embedded workbook to your computer, and then open it in Excel.

Sheet3 in the same workbook uses an array of numbers to assign a letter grade to a test score.

The workbook below shows examples of this function. Inspect them, change existing formulas, or enter your own formulas to learn how the function works.

To work more in-depth with the example data in Excel, download the embedded workbook to your computer, and then open it in Excel.