WorksheetFunction.Lookup(Object, Object, Object) Method

Reference

Definition

Namespace:: Microsoft.Office.Interop.Excel

Assembly:: Microsoft.Office.Interop.Excel.dll

Important

Some information relates to prerelease product that may be substantially modified before it’s released. Microsoft makes no warranties, express or implied, with respect to the information provided here.

Returns a value either from a one-row or one-column range or from an array. The LOOKUP function has two syntax forms: the vector form and the array form.

public object Lookup (object Arg1, object Arg2, object Arg3);

Public Function Lookup (Arg1 As Object, Arg2 As Object, Optional Arg3 As Object) As Object

Parameters

Arg1: Object

Lookup_value - 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.

Arg2: Object

Lookup_vector or Array - In Vector form, a range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values. In array form, a range of cells that contains text, numbers, or logical values that you want to compare with lookup_value.

Arg3: Object

Result_vector - Only used with the Vector form. A range that contains only one row or column. It must be the same size as lookup_vector.

Returns

Object

Remarks

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.

Note: You can also use the Lookup function as an alternative the IF function for elaborate tests or tests for more than seven conditions. See the examples in the array form.

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.

Important: The values in lookup_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.

If LOOKUP can't find the lookup_value, it 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 gives the #N/A error value.

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. 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(Object, Object, Object, Object)or VLookup(Object, Object, Object, Object)function instead of the array form of Lookup. This form of Lookup is provided for compatibility with other spreadsheet programs.

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.

If 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.

The array form of Lookup is very similar to the HLookup(Object, Object, Object, Object) and VLookup(Object, Object, Object, Object) functions. The difference is that HLookup(Object, Object, Object, Object) searches for lookup_value in the first row, VLookup(Object, Object, Object, Object) 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(Object, Object, Object, Object) and VLookup(Object, Object, Object, Object), 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 may not give the correct value. Uppercase and lowercase text are equivalent.

Applies to