LOOKUP function
This article describes the formula syntax and usage of the LOOKUPfunction in Microsoft Excel.
Description
The LOOKUP function returns a value either from a onerow or onecolumn range or from an array. The LOOKUP function has two syntax forms: the vector form and the array form.
If you want to 
Then see 
Usage 
Look in a onerow or onecolumn range (known as a vector) for a value and return a value from the same position in a second onerow or onecolumn range 
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 
Use the array form when you have a small list of values and the values remain constant over time. 
Note

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 onerow or onecolumn range (known as a vector) for a value and returns a value from the same position in a second onerow or onecolumn 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:

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, ..., AZ, 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. 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.
Examples
Copy the example data in each of the following tables, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.
Example 1
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.75, A2:A6, B2:B6) 
Looks up 5.75 in column A, matches the nearest smaller value (5.17), and returns the value from column B that is in the same row. 
yellow 
=LOOKUP(7.66, A2:A6, B2:B6) 
Looks up 7.66 in column A, matches the nearest smaller 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 (4.14) in column A. 
#N/A 
Example 2
These formulas show the array form used with the function.
Formula 
Description 
Result 
=LOOKUP("C", {"a","b","c","d";1,2,3,4}) 
Looks up "C," and finds the nearest value that is less than or equal to "C." In this case, it finds an exact match, because LOOKUP is not casesensitive. Returns the value (3) in the last row that is in the same (third) column. 
3 
=LOOKUP("bump", {"a",1;"b",2;"c",3}) 
Looks up "bump," and doesn't find an exact match. Finds the largest value that is less than or equal to "bump," which is "b" (in row 2), and returns the value (2) in the last column that is in the same row as "b." 
2 
Example 3
These formulas use an array of numbers to assign a letter grade to a test score.
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+ 