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 Office 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. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.
  • lookup_vector    Required. A range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values.

 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    Required. A range that contains only one row or column. The result_vector argument must be the same size as lookup_vector.

Remarks

  • If the LOOKUP function can't 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.

Example

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow do I copy an example?

  1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.Important Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help

  1. Press CTRL+C.
  2. Create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.

Important For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.


 
1
2
3
4
5
6
7


8



9



10



11
A B C
Frequency Color
4.14 red
4.19 orange
5.17 yellow
5.77 green
6.39 blue
Formula Description Result
=LOOKUP(4.19,A2:A6,B2:B6) Looks up 4.19 in column A, and returns the value from column B that is in the same row. orange
=LOOKUP(5.00,A2:A6,B2:B6) Looks up 5.00 in column A, matches the next smallest value (4.19), and returns the value from column B that is in the same row. orange
=LOOKUP(7.66,A2:A6,B2:B6) Looks up 7.66 in column A, matches the next smallest value (6.39), and returns the value from column B that is in the same row. blue
=LOOKUP(0,A2:A6,B2:B6) Looks up 0 in column A, and returns an error because 0 is less than the smallest value in the lookup_vector A2:A7. #N/A

Top of Page Top of Page


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. The lookup_value argument can be a number, text, a logical value, or a name or reference that refers to a value.
    • If LOOKUP can't find the value of lookup_value, it uses the largest value in the array that is less than or equal to lookup_value.
    • If the value of 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.
  • 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 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.

 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.

Example 1

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow do I copy an example?

  1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.Important Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help

  1. Press CTRL+C.
  2. Create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.

Important For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.


 
1




2




3
A B C
Formula Description Result
=LOOKUP("C",{"a","b","c","d";1,2,3,4}) Looks up "C" in the first row of the array, finds the largest value that is less than or equal to it ("c"), and then returns the value in the last row that is in the same column. 3
=LOOKUP("bump",{"a",1;"b",2;"c",3}) Looks up "bump" in the first row of the array, finds the largest value that is less than or equal to it ("b"), and then returns the value in the last column that is in the same row. 2

Example 2

The example may be easier to understand if you copy it to a blank worksheet.

ShowHow do I copy an example?

  1. Select the example in this article. If you are copying the example in Excel Web App, copy and paste one cell at a time.Important Do not select the row or column headers.

Selecting an example from Help

Selecting an example from Help

  1. Press CTRL+C.
  2. Create a blank workbook or worksheet.
  3. In the worksheet, select cell A1, and press CTRL+V. If you are working in Excel Web App, repeat copying and pasting for each cell in the example.

Important For the example to work properly, you must paste it into cell A1 of the worksheet.

  1. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

After you copy the example to a blank worksheet, you can adapt it to suit your needs.


The following example uses an array of numbers to assign a letter grade to a test score.

 
1
2
3
4
5












6












7












8












9











10











11
A B C
Score
45
90
78
Formula Description Result
=LOOKUP(A2,{0,60,70,80,90},{"F","D","C","B","A"}) Looks up the value in A2 (45) in the first row of the array, finds the largest value that is less than or equal to it (0), and then returns the value in the last row of the array that is in the same column. F
=LOOKUP(A3,{0,60,70,80,90},{"F","D","C","B","A"}) Looks up the value in A3 (90) in the first row of the array, finds the largest value that is less than or equal to it (90), and then returns the value in the last row of the array that is in the same column. A
=LOOKUP(A4,{0,60,70,80,90},{"F","D","C","B","A"}) Looks up the value in A4 (78) in the first row of the array, finds the largest value that is less than or equal to it (70), and then returns the value in the last row of the array that is in the same column. C
=LOOKUP(A2,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"}) Looks up the value in A2 (45) in the first row of the array, finds the largest value that is less than or equal to it (0), and then returns the value in the last row of the array that is in the same column. F
=LOOKUP(A3,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"}) Looks up the value in A3 (90) in the first row of the array, finds the largest value that is less than or equal to it (90), and then returns the value in the last row that is in the same column. A-
=LOOKUP(A4,{0,60,63,67,70,73,77,80,83,87,90,93,97},{"F","D-","D","D+","C-","C","C+","B-","B","B+","A-","A","A+"}) Looks up the value in A4 (78) in the first row of the array, finds the largest value that is less than or equal to it (77), and then returns the value in the last row that is in the same column. C+

Top of Page Top of Page

 
 
Applies to:
Excel 2007