Related Functions:

The LOOKUP function returns a value from a range, one row or one column, or from an array. There are two forms of the LOOKUP function: the vector form and the array form.

If you want to  Then see
Search one row or one column for a value Vector form
Want to match values in the first row or column of an array Array form

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
lookup_value A value that LOOKUP searches for in the first vector
lookup_vector A range that contains only one row or one column

  The values in lookup_vector must be in ascending order: …, -2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value
  The function is not case sensitive, i.e. uppercase and lowercase text are equivalent
[result_vector] Optional. A range that contains only one row or column

  The [result_vector] argument must be the same size as lookup_vector
  If the [result_vector] is omitted, the result is returned from the lookup_vector

Vector Examples

  A B C D E
1 OrderID Quantity Unit Cost Total Cost  
2 10567 2 $3.50 $7.00  
3 10568 4 $5.00 $20.00  
4 10569 3 $7.23 $21.69  
5 10570 12 $2.99 $35.88  
6          
7 Formula Result Notes
8 =LOOKUP(10568,A2:A5,C2:C5) $5.00 Looks up 10568 column A, and returns the value from column C that is in the same row
9 =LOOKUP(10571,A2:A5,B2:B5) 12 Looks up 10571 in column A, matches the nearest smaller value (10570), and returns the value from column B that is in the same row

Array Form

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.

Note: Using VLOOKUP or HLOOKUP instead of the array form is strongly recommended. The array form of LOOKUP is provided for compatibility with other spreadsheet programs, but it’s functionality is limited.

Syntax

=LOOKUP(lookup_value,array)

Arguments

Argument Description
lookup_value A value that LOOKUP searches for in an array

  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 lookup_value is smaller than the smallest value in the first row or column, LOOKUP returns the #N/A error value
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 in ascending order: …, -2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE; otherwise, LOOKUP might not return the correct value
  The function is not case sensitive, i.e. uppercase and lowercase text are equivalent

Array Examples

  A B C D E F G
1 Lower Upper Grade   Student Grade  
2 0 44 F   Chris 83%  
3 45% 54% E        
4 55% 64% D        
5 65% 74% C        
6 75% 84% B        
7 85% 100% A        
8              
9 Formula Result   Notes
10 =LOOKUP(F2,A2,C7) B   LOOKUP searches for the lookup_value in the first column of the supplied array, and returning a value from the last column

Common Function Error(s)

Problem What went wrong
#N/A Error indicates the LOOKUP function failed to find the closest match to the supplied lookup_value. Occurs if either:

  the smallest value in the lookup_vector,or first column/row of the array, is greater than the supplied lookup_value
  the lookup_vector, or first column/row of the array, is not in ascending order
#REF! Error indicates the formula is attempting to reference cells that do not exist. Occurs if either:

  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