LOOKUP(lookup_value, lookup_vector, result_vector)
Array Form - LOOKUP(lookup_value, array)
Returns the value in a row (or column) that matches a value in a column (or row).
|lookup_value||The value you want to look for.|
|array||(Array Form Only) The array of values that contain the value you want to look for.|
|lookup_vector||The range of cells that contains the value you want to look for.|
|result_vector||The range of cells that contains the value you want to return.|
|* For an illustrated example refer to the page under Lookup Functions.|
* XLOOKUP was added in Microsoft 365 to replace this function.
* This function is not case sensitive when searching for text strings.
* This function can return a value either from a one-row or one-column range or from an array.
* This function does not have an argument to indicate exact match or closest match.
* This function will always return the Closest Match when there is no Exact Match.
* The "lookup_value" can be a number, text, logical value, or a name or reference that refers to a one of these.
* If "lookup_value" is smaller than the smallest value in "lookup_vector", then #N/A is returned.
* If "lookup_value" cannot be found it uses the largest value that is less than "lookup_vector". Example 12
* If "result_vector" is not the same size as "lookup_vector", you might still get the correct answer.
* The vector form of this function looks in a one-row or one-column range for a value and returns a value from the same position in a second one-row or one-column range.
* The array form of this function looks in the first row or column for a value and returns a value from the same position in the last row or column.
* The "array" can be positive or negative (i.e. rows up or down).
* The "array" argument must be sorted into ascending order (-1, 0, 1, A-Z, FALSE, TRUE).
* You can use the XLOOKUP function to return a value in the same row (or column).
* You can use the HLOOKUP function to search for a value and return a different item from the same column.
* You can use the VLOOKUP function to search for a value and return a different item from the same row.
* For the Microsoft documentation refer to support.microsoft.com
|1 - (array) If the array is 1 dimensional and a match is found then the lookup value is returned.|
2 - (array) If the array is 1 dimensional and a match is not found then the first value from the array is returned.
3 - (array) This is the same as 1 and shows that this function is not case sensitive.
4 - (array) Always returns the value from the same position in the last row (or column).
© 2021 Better Solutions Limited. All Rights Reserved. © 2021 Better Solutions Limited Top