Whilst XLOOKUP wins hands down against HLOOKUP and VLOOKUP, the same cannot necessarily be said for LOOKUP.  You may recall LOOKUP has two forms: an array form and a vector form.  As a reminder:

 

• an array is a collection of cells consisting of at least two rows and at least two columns
• a vector is a collection of cells across just one row (row vector) or down just one column (column vector).

The diagram should be self-explanatory:

The array form of LOOKUP looks in the first row or column of an array for the specified value and returns a value from the same position in the last row or column of the same array:

LOOKUP(lookup_value, array)

where:

• lookup_value is the value that LOOKUP searches for in an array.  The lookup_value argument can be a number, text, a logical value, or a name or reference that refers to a value
• array is the range of cells that contains text, numbers, or logical values that you want to compare with lookup_value. 

The array form of LOOKUP is very similar to the HLOOKUP and VLOOKUP functions. The difference is that HLOOKUP searches for the value of lookup_value in the first row, VLOOKUP searches in the first column, and LOOKUP searches according to the dimensions of array.

If array covers an area that is wider than it is tall (i.e. it has more columns than rows), LOOKUP searches for the value of lookup_value in the first row and returns the result from the last row.  Otherwise, LOOKUP searches for the value of lookup_value in the first column and returns the result from the last column instead.

The alternative form is the vector form:

LOOKUP(lookup_value, lookup_vector, [result_vector])

The LOOKUP function vector form syntax has the following arguments:

• lookup_value is the value that LOOKUP searches for in the first vector
• lookup_vector is the range that contains only one row or one column
• [result_vector] is optional – if ignored, lookup_vector is used – this is the where the result will come from and must contain the same number of cells as the lookup_vector.

Like the default versions of HLOOKUP and VLOOKUP, lookup_value must be located in a range of ascending values.

Let me demonstrate with an example:

LOOKUP is a great function to use with time series analysis / forecasting.  Dates are in ascending order and the LOOKUP syntax is remarkably simple. As a modeller, I use it regularly when I am modelling many more forecast periods than I want assumption periods.

Here, you can see I carry assumptions only for 2020 until 2024 (the final value is 2024, just with a “+” in number formatting).  The formula 

=LOOKUP(G$74,$G$67:$K$68)

returns the corresponding value for the period that is either an exact match or else the largest value less than or equal to the lookup_value. LOOKUP uses the top row of the table for looking up its data and the final row for returning the corresponding value. Simple. As for XLOOKUP:

=XLOOKUP(G$82,$G$67:$K$67,$G$68:$K$68,-1)

This formula is longer and requires two additional arguments (match_mode -1 is required to mirror the behaviour of LOOKUP). Indeed, given that an IF statement is required to ensure no errors for earlier periods, e.g.

=IF(G$90<$G$67,$G$68,LOOKUP(G$90,$G$67:$K$68))

it may be argued that LOOKUP is a simpler function to use here than its counterpart.  

This isn’t the only time LOOKUP outperforms XLOOKUP:

Here, we do see a limitation of XLOOKUP.  Whilst the third argument of XLOOKUP, results_array, does not need to be a vector, it cannot be the transposition of the lookup_vector. You would have to transpose it using the TRANSPOSE function, for example. This makes LOOKUP much easier to use – compare:

=LOOKUP(H112,F105:F109,G102:K102)

with

=XLOOKUP(H112,F105:F109,TRANSPOSE(G102:K102))

In this instance, LOOKUP wins.