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