Like the newer XLOOKUP function, LOOKUP can look up values in either rows or columns.
However, unlike XLOOKUP, LOOKUP can only perform an approximate match.
The second argument,lookup_vector, is the range or array to search.
The third argument, result_vector, is the range or array from which to return a result.Result_vectoris optional.
Ifresult_vectorisnotprovided, LOOKUP returns thevalueof the match found inlookup_vector.
The LOOKUP function has two forms: vector and array.
Most of this article describes thevectorform, but the last example below explains thearrayform.
In the screen below, the formula in F6 is:
Note the use of afull column reference.
This is not an intuitive formula, but it works well.
Example #4 - array form
The LOOKUP function has an array form as well.
In either case, LOOKUP returns a value at the same position from thelastrow or column in the array.
The example below shows how the array form works.
Note: Microsoft discourages the use of the array form and suggestsVLOOKUPandHLOOKUPas better options.
you’re free to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
XLOOKUP supports approximate and exact matching, wildcards (* ?)
It is a more robust and flexible successor to the MATCH function.
XMATCH supports approximate and exact matching, reverse search, and wildcards (* ?)
The output from FILTER is dynamic.
If source data or criteria change, FILTER will return a new set of results.
This makes FILTER a flexible way to isolate and inspect data without altering the…
