LOOKUP Function

The LOOKUP function returns the value in a row (or column) after finding a matching value in a column (or row).
In fact before we try and understand this function we need to make a small modification to the tooltip.

The Vector syntax for this function is the one used in 99% of cases, so lets assume that this is the only one that exists.
The other one was originally provided for backwards compatibility and is rarely seen or used.


Return the Exact Match

When using this function the table has to be sorted.
The table has been sorted by Country into ascending order.


Return the Exact Match (item does not exist)

When a value is not in the list, then #N/A is returned.
When using this function the table has to be sorted.
The table has been sorted by Country into ascending order.


Return the Exact Match (case sensitive)

When using this function the table has to be sorted.
The table has been sorted by Country into ascending order.
The LOOKUP function is not case sensitive.
This formula must be entered as an Array Formula using (Ctrl + Shift + Enter).
The "0" passed to the MATCH function means that it will find the first value that matches the lookup value exactly.


Return the Exact Match (first occurrence)

When using this function the table has to be sorted.
The table has been sorted by Country into ascending order.


Return the Exact Match (last occurrence)

When using this function the table has to be sorted.
The table has been sorted by Country into ascending order.


Return the Exact Match (multiple criteria)

A lookup using Multiple Criteria is when you want to match the values in 2 columns and return the corresponding value in a third column.
This can be achieved using the INDEX.


Return the Next Smallest

When using this function the table has to be sorted.
The table has been sorted by Sales into ascending order.


Return the Next Largest

When using this function the table has to be sorted.
The table has been sorted by Sales into descending order.
The "-1" passed to the MATCH function means that it will find the smallest value that is greater than the lookup value.



Array Form

To use this syntax your data must be sorted.



© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext