Director SumProduct
Share this content
Tags:

Useful features of XLOOKUP

Microsoft has just added two new functions, XLOOKUP and XMATCH. In this article, Liam Bastick considers the most useful features of the former function.

18th Sep 2019
Director SumProduct
Share this content

This article considers a number of the most useful features of XLOOKUP. For an introduction to the new feature, click here.

XLOOKUP can be used to perform a two-way match, similar to INDEX MATCH MATCH:

INDEX MATCH MATCH

Many advanced users might use the formula

=INDEX(H40:N46,MATCH(G53,G40:G46,0),MATCH(G51,H39:N39,0))

where:

  • INDEX(array, row_number, [column_number]) returns a value or the reference to a value from within a table or range (list) citing the row_number and the column_number
     
  • MATCH(lookup_value, lookup_vector, [match_type]) returns the relative position of an item in an array that (approximately) matches a specified value. It’s most commonly used with match_type zero (0), which requires an exact match.

Therefore, this formula finds the position in the row for the student and the position in the column of the subject. The intersection of these two provides the required result.

XLOOKUP does it differently:

=XLOOKUP(G53,G40:G46,XLOOKUP(G51,H39:N39,H40:N46))

Welcome to the wonderful world of the nested XLOOKUP function! Here, the internal formula

=XLOOKUP(G51,H39:N39,H40:N46)

demonstrates a key difference between this and your typical lookup function – the first argument is a cell, the second argument is a column vector and the third is an array – with, most importantly, the same number of rows as the lookup_vector. This means it returns a column vector of data, not a single value. This is great news in the brave new world of dynamic arrays.

In essence, this means the formula resolves to

=XLOOKUP(G53,G40:G46,J40:J46)

as J40:J46 is the resultant vector of =XLOOKUP(G51,H39:N39,H40:N46). This is a really powerful – and virtually new – concept to get your head around, that admittedly SUMPRODUCT exploits too. Once you understand this, it’s clear how this formula works and opens your eyes to the power of nested XLOOKUP functions.

I can’t believe I am talking about the virtues of nested functions here! Let me change the subject quickly...

To show you how dynamic arrays can make the most of being able to create resultant vectors, consider the following example:

XLOOKUP

The formula

=XLOOKUP(G77,I65:L65,I66:L72)

again resolves to a vector – but this time is allowed to spill as a dynamic array. Obviously, this will only work in Office 365, but it’s a very useful tool that might just make you think it’s time to drop that perpetual licence.

Once you start playing with the dynamic range side, you can start to get imaginative. For example:

Example

In this illustration, I want to calculate the sales between two periods:

start month

This might seem like a simple drop-down list using data validation (ALT + D + L), but XLOOKUP has been used in determining the list to be used for the end months.

Let me explain. I have hidden the range of relevant dates in cell H101 spilled across

XLOOKUP

XLOOKUP can return a reference, so the formula

=XLOOKUP(G100,H94:S94,H94:s94):S94

evaluates to the row vector N94:S94 (since the start month is July). This spilled dynamic array formula is then referenced in the data validation:

Data validation

(You may recall $H$101# means the spilled range starting in cell H101.) It should be noted that the formula =XLOOKUP(G100,H94:S94,H94:s94):S94 may not be used directly in the ‘Data Validation’ dialog, but this is a neat trick to ensure you cannot select an end month before the start month (assuming you are a rational human being that selects the start before the end!).

The formula to sum the sales then is

=SUM(XLOOKUP(G100,H94:S94,H95:S95):XLOOKUP(G101,H94:S94,H95:S95))

Again, this uses the fact XLOOKUP can return a reference, so this formula equates to

=SUM(N95:Q95)

Easy! Now I am combining two XLOOKUP formulae with a colon (:) to form a range. This joins other illustrious functions used this way such as CHOOSE, IF, IFS, INDEX, INDIRECT, OFFSET, SINGLE, SWITCH and TEXT. First nesting, now joining – what’s next?

Partial and Exact Matching

Seeking partial matches (sounds like an unfussy dating agency!) suddenly became a lot easier too. You can use wildcards if you want to – just set the match_mode to 2:

Example

Here, I am searching for J?n*n* - which is fine as long as you know what the wildcard characters mean:

  • ? means “any character”, but just one character. If you wanted to make space for two and only two characters you would use ??
  • * means “any number of characters’ – including zero.

For example, M?n*m* would identify “Manmade”, “minimum” and “Manikum” but would not accept “millennium”. Here, our formulae

=XLOOKUP(G184,H174:H179,I174:I179,2)

=XLOOKUP(G184,H174:H179,I174:I179,2,-1)

would locate the first and last items that satisfied the condition J?n*n* (i.e. “Jonathan” and “Jonny” respectively).

But what if you wanted an exact match with case sensitivity? You just have to think a little but outside of the proverbial box:

Example

Here, we use another feature of XLOOKUP – its ability to search a virtual vector, i.e. one that has been constructed in memory, rather than physically within the spreadsheet cells. Consider the formula

=XLOOKUP(TRUE,EXACT(H145:H154,G159),I145:I154)

Here, the interim calculation =EXACT(H145:H154,G159), looks at the range H145:H154 and deduces whether the cells are an exact match for the selection ‘Sum Product’ in cell G159. This would evaluate as

{FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE}

Therefore, the formula coerces to

=XLOOKUP(TRUE,{FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE; TRUE; FALSE},I145:I154)

and then the formula becomes simple to understand.

No doubt there are many more great things you can do with XLOOKUP, but hey, it’s just arrived and we are only getting started!

XMATCH

As I said at the beginning, XLOOKUP did not land in isolation. In addition to XLOOKUP, XMATCH has arrived with a similar signature to XLOOKUP, but instead it returns the index (position) of the matching item. XMATCH is both easier to use and more capable than its predecessor MATCH.

Example

XMATCH has the following syntax:

XMATCH(lookup_value, lookup_vector, [match_mode], [search_mode])

where:

  • lookup_value: this is required and defines what value you want to look up
  • lookup_vector: this reference is required and is the row or column of data you are referencing to look up lookup_value
  • match_mode: this argument is optional. There are four choices:
    • 0: exact match (default)
    • -1: exact match or else the largest value less than or equal to lookup_value
    • 1: exact match or else smallest value greater than or equal to lookup_value
    • 2: wildcard match. You should use the special character ? to match any character and * to match any run of characters.

Again, for certain selections of the final argument (search_mode), you don’t need to put your data in alphanumerical order

  • search_mode: this argument is also optional. There are again four choices:
    • 1: search first to last (default)
    • -1: search last to first
    • 2: this is a binary search, first to last (requires lookup_vector to be sorted)
    • -2: another binary search, this time last to first (and again, this requires lookup_vector to be sorted).

As you can see, it’s a fairly straightforward addition to the MATCH family. It acts similarly to MATCH – just with heaps more functionality.

Word to the wise

XLOOKUP and XMATCH open up new avenues for Excel to explore, but it must be remembered they are still in Preview and may only be accessed by a lucky few on the Insider track. Feel free to download and play with the attached Excel file, but don’t be too perturbed if your version of Excel does not recognise these functions yet.

Liam Bastick is author of an Introduction to Financial Modelling, which provides a simple walkthrough of the common perils and pitfalls of financial modelling. Presently, this book is only available on Amazon Australia but if anyone would like to buy a copy directly contact Liam via this link. AccountingWEB readers receive a 10% discount plus p&p (including tracking and insurance at cost).

Tags:

Replies (1)

Please login or register to join the discussion.

avatar
By AndrewV12
24th Sep 2019 12:06

Its looks a brilliant article, regrettably I am a tech numb-tie and its all over my head :(

Thanks (0)