LOOKUP

General lookup.

Syntax:

LOOKUP( lookup_key_value, lookup_data:Range or Array, result_data:Range or Array {, all:Number} )

Returns the value in the result_data range of cells (or array) corresponding to the position that lookup_key_value holds in the lookup_data range of cells (or array).

lookup_key_value can be a date, string or number. If lookup_key_value is a string, it can include the wildcards ^? (any single character) or ^# (multiple characters).

If an exact match for lookup_key_value is found in the lookup_data range of cells (or array), the function returns the corresponding element from the result_data range of cells (or array).

If lookup_key_value is not found in the lookup_data array, an error message is returned.

If the optional fourth parameter all is supplied and set to 1, the function returns an array containing all the rows of the result_data array for which the key was found in the lookup_data array.

Example:

LOOKUP("Mark", B1:B10, B1:E10, 1)

returns an array which is all of the rows of B1:E10 for which the value in column B is "Mark".