The LOOKUP function

The LOOKUP function is a lookup and reference function which locates a requested variable, and returns the value within the relative cell from the original range. This function takes on two forms. The first requires two ranges have at least one dimension which is a maximum of one cell. The second takes one large array and returns the value from the last row or column of the array depending on its size.

* One very important note – and the reason LOOKUP is not frequently used – is because if your search array is not sorted in alphanumeric order the function will return an error *

This is the format of the LOOKUP function:

=LOOKUP(LOOKUP VALUE, SEARCH ARRAY, RETURN ARRAY)

=LOOKUP(LOOKUP VALUE, ARRAY VALUE)

Here is an example of the LOOKUP function:

=LOOKUP(“A”, B3:B5, C3:C5)

=LOOKUP(“A”, B3:C5)

This function is valuable when you have a data set which is always sorted, and has few parameters to consider. Ideally this function is not used on a permanent basis, but rather for quick situations whose format suits the LOOKUP function.

An example of a functional implementation of this would be when an array of values consists of dates and results, where modifying the current order of the rows/columns does not impact the information stored. After ensuring the information is properly organised, the LOOKUP function enables a quicker retrieval of information than a similar VLOOKUP or HLOOKUP would (less characters to enter when familiar with all three functions).

=LOOKUP(2020-01-19, A1:A40, B1:B40)

=VLOOKUP(2020-01-19, A1:B40, 2, FALSE)

=HLOOKUP(2020-01-19, A1:Z2, 2, FALSE)

* Note that while this function is still supported, and has marginal benefits in terms of speed compared to other lookup functions, it is still recommended to use a VLOOKUP or a combination of INDEX/MATCH to complete any lookup requests due to their increased reliability and the fact that in most use cases the speed efficiency from LOOKUP is negligible at best. *