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


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:


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


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 just transition into a role related to data analytics at current company......😊 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.

10 Responses to Be cautious when using XLOOKUP

  1. Gino Larsen-Giacalone says:

    Problem when arrays are external references. Works when same data is in named ranges


  2. anands06 says:

    Why lookup and return array has tot be of the same length (size). I tried xlookup when arrays are not the same size, at times it worked well for me but sometimes it didn’t. There should be some way out of it? No one has addressed this issue
    Regards. Anand


  3. Eric says:

    XLOOKUP works great, and the “error” can also be a benefit. XLOOKUP beats VLOOKUP in that VLOOKUP was restricted to y-axis returns. XLOOKUP allows x- and y-axis returns through the precise method the error demonstrates. Or more precisely, XLOOKUP allows searching diagonally, which opens up the landscape.

    When a spreadsheet is created from an export report they are not clean – data is all over the place. In fact I’ll bet that a report or statement that is exported in roughly the same way it appears on screen will rarely ever fall into the row/column neatness appreciated by Excel users. Information is not presented by row/by column. Information is presented diagonally.

    Below is how real-world exports appear. They’re compartmentalized, blocks of data instead of 1-row-per data. VLOOKUP is not going to work. And sure, you can use INDEX-whatever combo functions to get the job done.

    For me, if the piece if unlabeled information I want is 4 columns and 3 rows adjacent to “CHARLES T”, that potential error in XLOOKUP becomes the very tool that allows me to find it.
    XLOOKUP(“CHARLES T”,A1:A100,E3:E103) = “A5-123”
    XLOOKUP(“SANDY E”,A1:A100,E3:E103) = “J27-E5”

    Now I have tracking numbers for those deliveries.

    CHARGES $200 $350 $155
    PAID $140 $350 $100 A5-123

    CHARGES $10 $200 $20
    PAID $5 $170 $20 J27-E5


  4. myjunkmailaddress says:

    why doesnt anyone or even microsoft for that matter tell you that the arrays must be of the same length when working with named ranges in this formula? Simple fix, stupid problem to allow to happen.


    • anands06 says:

      i agree, there are million examples where lookup and return array will not be of same length (size). I tried xlookup when arrays are of not same size, at times it worked well for me but sometimes it didn’t. There should be some way out of it? Regards. Anand


  5. 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.


  6. Oz du Soleil says:

    AMAZING! Thanks for pointing this out.


Leave a Reply to MF Cancel reply

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

You are commenting using your 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.