Raise your hand if you love VLOOKUP? Me too! We Excel users have grown to love VLOOKUP over the years. But, XLOOKUP offers some advantages. XLOOKUP? That’s not a typo. Depending on your version of Excel and when you are reading this, you may see this function immediately, or it may be a while before it arrives. But either way, it is a goodie πŸ™‚  Thanks Bill J for the scoop on this, and Gary Z for the heads-up!


In summary, it is designed to return a related value, just like VLOOKUP … but it addresses some of the limitations of VLOOKUP and provides many additional options. Here is a screenshot of the Insert Function dialog which provides a description (and proof it exists):

Note: at the time I’m writing this, XLOOKUP is available in O365 w/Insiders Fast update channel. Depending on your version of Excel, you may not see it in your function list.

Let’s dig into the arguments:

=XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode])


  • lookup_value – value to find (same as VLOOKUP)
  • lookup_array – lookup column (different than VLOOKUP)
  • return_array – return column (different than VLOOKUP)
  • [match_mode] – 0 for exact match by default + more (different than VLOOKUP)
  • [search_mode] – 1 for first-to-last + more (different than VLOOKUP)

Let’s talk about these arguments in a bit more detail.

Lookup_value is similar to VLOOKUP, and represents the value we are trying to find.

Lookup_array represents a big change from VLOOKUP. Instead of defining the entire two-dimensional range (or table_array), we can define a single column. We specifically identify the lookup column range. This means that column order doesn’t matter anymore …. yay! The lookup column can be anywhere in the table.

Return_array is another big difference from VLOOKUP. Instead of defining the return column by index number (col_index_num), we can select the return column range. This is huge because it means if we insert a new worksheet column between the lookup and return columns, nothing breaks … yay! Plus, it can be located anywhere in the table, including to the left of the lookup column πŸ™‚

[match_mode] is an optional argument, and defaults to 0 if omitted. That means that if you don’t specify it, it will perform an exact match. This is different than VLOOKUP which defaults to doing a range lookup. With VLOOKUP, we can choose True or False, but XLOOKUP offers more options:

The wildcard character match is more of what we think about when we want to do a partial match. To use this option, we just add a wildcard character like asterisk (*) to the lookup value.

[search_mode] is an optional argument, and defaults to 1 when omitted. This replicates the behavior of VLOOKUP where it tries to find a match by starting at the top and working down.

We can also search from the bottom up, which is a nice option.

XLOOKUP starts with the solid foundation of VLOOKUP, and then enhances it. It is like the VLOOKUP we’ve always wanted and dreamed about πŸ™‚

XLOOKUP will help improve our workbooks by eliminating errors and providing more flexibility. Thank you Microsoft!!

Tip to job-seekers:

Here’s a tip for you if you are interviewing for a job and the interviewer asks you “Do you know Excel? Well, do you know VLOOKUP?”  You answer like this: “Yes I know VLOOKUP … but I prefer XLOOKUP. You do know about XLOOKUP, right?” … and blow their mind!!

If you’d like to learn more about XLOOKUP, I teach a free 20 minute XLOOKUP webinar. More info:

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.


  1. Tom Herrington on August 29, 2019 at 3:25 pm

    I have Office 365, but this function is not in my version of Excel. What is the criteria to receive it?


  2. Shadrack on August 29, 2019 at 4:07 pm

    Lol. I love the last paragraph, “Tips to Job Seekers”
    You answer: β€œYes I know VLOOKUP … but I prefer XLOOKUP. You do know about XLOOKUP, right?” Lol. Excel just got better

    • Jeff Lenning on August 29, 2019 at 4:08 pm


  3. Ben Connelly on August 30, 2019 at 9:21 am

    *Reads article, runs to toolbox, tosses out INDEX-MATCH.*


    • Jeff Lenning on August 30, 2019 at 9:22 am


  4. shimsh on August 30, 2019 at 10:00 am

    This seems fantastic! Which version of Excel is the earliest that this would be available on?

    • Jeff Lenning on August 30, 2019 at 10:02 am

      It is being rolled out over time to O365 subscribers depending on your office update channel preferences. Mine is set to the Insiders Fast channel, so give that a try and see if it gets installed. Hope it works!

  5. Robert Joseph Brown on August 30, 2019 at 11:39 am

    Where do you find the Insiders Fast Channel

  6. David N on September 3, 2019 at 5:56 pm

    I don’t have access to XLOOKUP and therefore can’t test these scenarios yet. So I’ll post them as challenge problems and see what you can tell us.

    Can lookup_array and return_array be literal or derived arrays as opposed to physical ranges, and I’m not talking about the “union” range you get with the age old CHOOSE trick? Say you wanted to locate the first value between 10 and 20. Can XLOOKUP mimic the following?

    Array (CSE) entered: INDEX(return_array,MATCH(1,(lookup_array>=10)*(lookup_array=10)*(lookup_array<=20),0),0))

    And can it return multiple, simultaneous matches? Not multiple columns for a single match as I've seen numerous people asking on other discussions and blogs, but something like the 2nd, 4th, and 7th matches for the name David at the same time.


    And what about the last match?


    I could go on with at least two other challenge problems, but let's see the outcome on these first. If the answer to any one of them is no, then I propose that XLOOKUP is little more than the application of a new coat of paint on a tired and inferior function just because some think the combination of INDEX and MATCH (even in their simplest forms) are too much for anyone to possibly learn.

    • Muhammad Mubashir Aziz on February 6, 2020 at 5:11 am

      Array (CSE) and Aggregate function are best which I am also using but for the last match, Array (CSE) Index+Match
      can be replaced with Xlookup which will bring last match easily.
      But for 2nd, 3rd and 5th match we have to go with Array (CSE).

  7. MJK on September 4, 2019 at 12:31 pm
  8. peter roberts on December 16, 2019 at 8:23 pm

    All of the articles I have read on xlookup focus on a range of features some of which are worthwhile and others that are not likely to be used very often

    None of the articles refer to a speed advantage or disadvantage

    Please advise

  9. Bradlee C Walace on December 21, 2019 at 4:47 pm

    I have O265 V.16.32 (19120802) macOS Catalina V.10.15.1

    I am Unable to run XLOOKUP

    When will it be available? Any suggestions on how / where to update my O365 version?


    Brad Wallace

Leave a Comment