LOOKUP

Description: Based on a range of one row or one column, returns the value from the same position in a second row or column OR looks in the first row or column of an array, and returns the specified value from the same position in the last row or column of the array. The LOOKUP function has two syntax forms: vector and array.

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 you want to match.
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 you want to match are in the first row or column of the array.

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 (vector): LOOKUP(Lookup_value, L_vector, [Result_vector])

Lookup_value is 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.
L_vector is a range that contains only one row or one column. The values in L_vector can be text, numbers or logical values.

Important: The values in L_vector must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.

Result_vector is a range that contains only one row or column. It must be the same size as L_vector.

Remarks (vector):

If LOOKUP can't find the Lookup_value, it matches the largest value in L_vector that is less than or equal to Lookup_value.
Lookup_value must be greater than or equal to the smallest value in L_vector.

Example (vector):

	A	B
1	Frequency	Color
2	4.14	red
3	4.19	orange
4	5.17	yellow
5	5.77	green
6	6.39 blue	blue

LOOKUP(4.19, A2:A6, B2:B6) = orange (looks up 4.19 in column A, and returns the value from column B that's in the same row)
LOOKUP(5.00, A2:A6, B2:B6) = orange (looks up 5.00 in column A, matches the next smallest value (4.19), and returns the value from column B that's in the same row)

Syntax (array): LOOKUP(Lookup_value, Array)

Lookup_value is 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.
Array is a range of cells that contains text, numbers or logical values that you want to compare with Lookup_value.

Remarks (array):

If LOOKUP can't find the Lookup_value, it uses the largest value in the array that is less than or equal to Lookup_value.
Lookup_value must be greater than or equal to the smallest value in the first row or column (depending on the array dimensions).
The array form of LOOKUP is very similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for 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 Lookup_value in the first row.

If Array is square or is taller than it is wide (more rows than columns), LOOKUP searches in the first column.

With HLOOKUP and VLOOKUP, you can index down or across, but LOOKUP always selects the last value in the row or column.

IMPORTANT: The values must be placed in ascending order: ...,-2, -1, 0, 1, 2, ..., A-Z, FALSE, TRUE; otherwise, LOOKUP may not give the correct value. Uppercase and lowercase text are equivalent.

Example (array):

LOOKUP("C",{"a","b","c","d";1,2,3,4}) = 3 [looks up "C" in first row of the array and returns the value in the last row that's in the same column]