ICAEW.com works better with JavaScript enabled.
Exclusive

XLOOKUP Part 2 - Comparisons with LOOKUP

Author: Liam Bastick

Published: 03 Apr 2023

Exclusive content
Access to our exclusive resources is for specific groups of students, users, subscribers and members.

The second part of a series of articles exploring comparisons with LOOKUP

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:

excel file

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:

excel file

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_valueLOOKUP 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:

excel file

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.

Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250