Be cautious when using XLOOKUP

Excel Tip - Be cautious when using XLOOKUP

Can you spot the error in the above formula?

It’s not about the new function of XLOOKUP.  It’s about inevitable human error…

If you are an Excel fan, you should be aware of the exciting XLOOKUP function in Office 365.  It is, no doubt,  an awesome function that will beat VLOOKUP, HLOOKUP, LOOKUP, INDEX/MATCH in the future when it is generally available to all Excel users.  You may find more details about XLOOKUP from Microsoft Tech Community.

There are many very well presented videos about XLOOKUP on YouTube already.  Just to name a few (my favorite YouTube channels),

So in this post, I am not going to talk about all the cool features of XLOOKUP.  Instead, I would like to draw your attention to a potential mistake when writing XLOOKUP function.

You may download a Sample File to follow along.

Now let’s look back to the question:  What’s wrong with XLOOKUP? 🤔

Excel Tip - Be cautious when using XLOOKUP

A major difference between VLOOKUP and  XLOOKUP is how they handle the return array.

In VLOOKUP, we need to identify the table_array as the 2nd argument, and specify the column to be returned, as the 3rd argument.  Because of this, VLOOKUP searches from left to right, which is exactly one of its well-known limitations.

In the new XLOOKUP function, the lookup_array and the return_array are now two separate arguments:

Excel Tip - Be cautious when using XLOOKUP1

Thanks to this improvement, XLOOKUP is highly flexible!  XLOOKUP can lookup vertically, or horizontally, regardless of lookup directions.  Nevertheless, it opens a door for potential human error.

What is that potential error?

A picture tells thousand words:

Excel Tip - Be cautious when using XLOOKUP2

In this example, the formula input in E2 is

=XLOOKUP(D2,A2:A11,B3:B12)

With the visual aid above, and given the small data range for the purpose of demonstration, we may spot the error quickly.  BUT when we are dealing with a large range of data, this kind of “careless” mistake could be a fatal spreadsheet error as it’s not easily spotted.

Luckily, Excel is care enough to give us a warning sign for such case.

Excel Tip - Be cautious when using XLOOKUP3.png

Do you see that tiny green triangle?

It means that there may be something wrong in the cell.  Let’s take a further look at the message by selecting the cell, followed by clicking on the “Exclamation mark” next to it.

Excel Tip - Be cautious when using XLOOKUP4

It says: “Formula Omits Adjacent Cells” and it offers a suggestion: “Update Formula to Include Cells” to fix the issue.

Let’s try and see:Excel Tip - Be cautious when using XLOOKUP

Oops… it returns #VALUE error.

Why is that? Because…

Excel Tip - Be cautious when using XLOOKUP5.png

Here’s the revised formula:

=XLOOKUP(D2,A2:A11,B2:B12)

Excel revised the return_array from B3:B12 to B2:B12.

When the lookup_array and the return_array are of different sizes, XLOOKUP returns #VALUE error.

Although Excel is not intelligent enough yet to fix this mistake correctly, it successfully draws our attention to have a closer look at the formula; and hence has a higher chance to fix it manually by ourselves.

By the way, did you ever pay attention to that tiny green triangle? Be honest 😁

Next question in my mind

What about approximate match?

Excel Tip - Be cautious when using XLOOKUP6

Well, it behaves the same as exact match.  It returns a “shifted” result; depending on the range offset between the lookup_array and return_array.

Interestingly, the tiny green triangle does not appear in this case.

By now, if you have followed through, the question should be

WHY IS THAT?

I think it is not uncommon for you to expect XLOOKUP works the way you think, even though there is a shift in the return_array:

Excel Tip - Be cautious when using XLOOKUP

Hey, Excel… Did you see… 2 is in a parallel position to B.

How come you return 3 instead? It doesn’t make sense…

It never happened in VLOOKUP.

Well, I believe it is due to the same issue I discussed in my another post Be cautious when using SUMIF(s).  It is about the position of the matched value in the ranges.  Please read that post for details.

 

How to avoid this?

Now we know there is an issue, is there a way to avoid it?

I said many times in this blog:

If human error can be avoided, it’s not human error at all. 😛

 

Having said that, I strongly suggest we use Structured Reference with Excel Table.

Excel Tip - Be cautious when using XLOOKUP7

This is an easy but highly effective way to avoid careless mistake we discussed so far.

In short, to make XLOOKUP work in the way you think, you need to ensure two things:

  1. lookup_array and return_array must be of equal size
  2. lookup_array and return_array must be aligned perfectly in parallel

 

Why Excel has this kind of bug in such a powerful new function?

Well, I won’t say it’s a bug.  It should be a feature, I guess. 🙂

In rare cases, you may want Excel to perform a VLOOKUP but return the value that is at the next row of the match.

Consider a table layout like this:

Excel Tip - Be cautious when using XLOOKUP8

What you want is to lookup the Qty by Salesperson.  In this case, the traditional VLOOKUP would not work.  INDEX/MATCH comes to recuse.  But now, if you have XLOOKUP, you may consider using it to solve this scenario too! 😉

Watch this:

Excel Tip - Be cautious when using XLOOKUP1

Just pay attention to the shift in the return_array you need.  That said, it may not be an efficient formula when you want to return “Value”, or even better to make “Value” and “Qty” a parameter for user to select from.  INDEX/MATCH would be a better choice then.

That’s it.

Do you have other practical use case of XLOOKUP where you would like to return a shifted value in the return_array?  If you do, please share with us by leaving comment below.

You are more than welcome to share your use cases of XLOOKUP too. 😉

About MF

An Excel nerd who doesn’t work on Excel most of the time at current job...... 🤔 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Formula and tagged , , , . Bookmark the permalink.

4 Responses to Be cautious when using XLOOKUP

  1. David N says:

    Everyone needs to be very careful with claims that XLOOKUP will beat, outperform, or be generally better than INDEX-MATCH. If we’re just talking about basic, common lookups, then sure, it’s essentially a tie, especially if you consider using INDEX-XMATCH. But I can easily name 3-4 types of “lookups” that can be accomplished with INDEX-MATCH that can’t be done with XLOOKUP. And if you bring INDEX-AGGREGATE to the table, then XLOOKUP pales even more.

    So let’s sing the praises of XLOOKUP — because it is unquestionably superior to VLOOKUP in every imaginable way — but not at the expense of a functional pair that’s already been winning that same superiority contest for a couple of decades……especially because INDEX-MATCH stills triumphs over XLOOKUP when the chips are down.

    Like

  2. Oz du Soleil says:

    AMAZING! Thanks for pointing this out.

    Like

Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.