What is XLOOKUP? 13 formula examples to really understand it


Share on facebook
Share on twitter
Share on linkedin

Office 365 now boasts a powerful successor to the VLOOKUP function – XLOOKUP formula. Think of XLOOKUP as VLOOKUP 2.0. In this article, learn all about the function, syntax, optional parameters and 13 xlookup examples.

What is XLOOKUP, how to use it and 13 examples to really understand it.

What is XLOOKUP?

XLOOKUP is the newest member of Excel lookup function family. You may already know its siblings – VLOOKUP, HLOOKUP, INDEX+MATCH, LOOKUP etc.

XLOOKUP allows us to search for an item in a range (or table) and return matching result. In a way, it is similar to VLOOKUP, but offers so much more.

When using XLOOKUP, you just provide 3 basic parameters (and 3 more optional parameters) and Excel does the rest.

  • The value you are looking for
  • The list where this value should be found
  • The list from which you want the result
  • [optional] value if not found

XLOOKUP example:

=XLOOKUP(“Jamie”, Sales[Sales Person], Sales[Net Sales])

Returns [Net Sales] for Jamie if the name exists in [Sales Person] column.

If you compare this with VLOOKUP, then you see that we no longer need to specify column_number or true/false to perform the search.

This also means, unlike VLOOKUP, XLOOKUP can actually look anywhere in the data and find the result, not just on the left.

No more complex INDEX+MATCH formulas or weird VLOOKUP concoctions.

How is XLOOKUP better?

  • XLOOKUP makes the most used formula dynamic in Excel straight forward and less error prone. You just write =XLOOKUP(what you want to find, the list, the result list) and boom, you get the answer (or #N/A error if the value is not found)
  • Looks up exact match by default: One of the annoyances of VLOOKUP is that you must mention FALSE as last parameter to get correct result. XLOOKUP fixes that by doing exact matches by default. You can use match mode parameter to change the lookup behavior if you want.
  • 4th parameter to support value not found scenario In most business situations, we are forced to wrap our lookup formulas with IFERROR or IFNA formulas to suppress errors. XLOOKUP offers 4th parameter (read more about it below) so you can tell what default output you want if your value is not found.
  • XLOOKUP offers optional parameters to search for special situations. You can search from top or bottom, you can do wild card searches and faster options to search sorted lists.
  • It returns reference as output, not the value. While this may not mean much for normal users, pro Excel user’s eyes light up when they discover a formula that can return refs. That means, you can combine XLOOKUP outputs in innovative ways with other formulas.
  • It is so much cooler to type, you just type =XL. I am not sure if this is a happy coincidence, but saying =XL to get this formula is just awesome.


Simple case:

=XLOOKUP(what you want to look, lookup list, result list)

=XLOOKUP("Jackie", sales[Sales Person], sales[Net Sales]) 

returns Jackie’s [Net Sales] if the name can be found in [Sales Person]

Optional parameters:

By default, you just need 3 parameters for XLOOKUP, as shown above. But you can also use 4th, 5th and 6th parameter to specify how you want the lookup to be done.

4th parameter for XLOOKUP: IF not found (no more IFERROR!!!)

The newly introduced XLOOKUP has an even newer feature. It now supports if not found option. This is the 4th parameter.

For example, use:

=XLOOKUP(“Chandoo”, sales[Sales Person], sales[Net Sales],”Value not found”) to return “Value not found” if the lookup value is not available in the search column – sales[Sales Person].

5th parameter for XLOOKUP: Match mode or type:

Use this to tell Excel how you want your MATCH to happen. The default is 0 (exact match) but you can also use these other options, shown below.

xlookup 5th paramter - match options

6th parameter for XLOOKUP: Match direction

Try this if you want to search from bottom to top. The default direction is top down (1).

xlookup match direction - 6th parameter

XLOOKUP Introduction & Examples – Video

Here is a video that explains XLOOKUP in no nonsense terms. Watch it below or go to my YouTube channel.

13 XLOOKUP examples to really understand it

My computer received XLOOKUP update just 90 minutes ago and I have been playing with it ever since. Here are 13 different, fun & creative examples to really understand it. Have a read. If you already have XLOOKUP, feel free to download the sample workbook and have a play.

For all of these examples, we will use the [Sales] table data shown below. XLOOKUP works just as well with cell references too, but I am using structural references for ease of understanding.

sample data for xlookup formulas - sales table

XLOOKUP Examples – Table

All the examples are listed in this table. Browse it and feel free to copy the formulas to test.

Note about references in the formula:

  • Input or search values are in column H. The value used for searching is shown in first column. I used references rather than hardcoded values to make the formula relateable.
  • Search is done against Sales Table as shown above.
JackieWhat is the net sales?1610XLOOKUP(H5,sales[Sales Person],sales[Net Sales])Nice and simple. Finds H5 (Jackie) in the sales[Sales Person] list and returns macthing [Net Sales] if found.
2133Whose sales are this?JamieXLOOKUP(H6,sales[Net Sales],sales[Sales Person])This time, we lookup in the middle but return the name at front. Normally we would use INDEX+MATCH for something like this, but XLOOKUP just kills it.
Who has most sales?JamieXLOOKUP(MAX(sales[Net Sales]),sales[Net Sales],sales[Sales Person])Of course, we can mix formulas too. MAX() finds the maximum sales and then XLOOKUP does the rest. Try other formulas like MIN(), SMALL(), LARGE() too.
8Who has this many customers?JosephXLOOKUP(H8,sales[No. Customers],sales[Sales Person])Another example with find the middle, return from front.
8Who is the last person to have this many customers?JamieXLOOKUP(H9,sales[No. Customers],sales[Sales Person],,0,-1)Now we are talking!!!, you can use the optional parameters for XLOOKUP to specify match type (0 is for exact match) and match direction (-1 is for bottom to top).
1800Whose sales are closest to this number, but not more?JagjitXLOOKUP(H10,sales[Net Sales],sales[Sales Person],"value not found",-1)We can search for a value that is closest but not more by using match type -1.
JuWhat is the profit of person whose name begins with this?1023XLOOKUP(H11&"*",sales[Sales Person],sales[Profit / Loss],,2)You can do wild card searches too. * for any number of letters and ? for single letter.
Who has least sales?JimmyXLOOKUP(0,sales[Net Sales],sales[Sales Person],,1)Time for a trick!!! When searching fields like [Net Sales] which will usually have just positive values, you can look for 0 with match type as 1 (next highest value).
What is the sales for very last person?1415XLOOKUP("*",sales[Sales Person],sales[Net Sales],,2,-1)Another trick! Search for "*" from end to get the last value's matching sales.
Who is the very last person?KarlXLOOKUP("*",sales[Sales Person], sales[Sales Person],,2,-1)Of course, you can search and return from the same column to find out the last person's name.
Net SalesWhat is H11 for Johnson?1540XLOOKUP("Johnson",sales[Sales Person],XLOOKUP(H15,sales[#Headers],sales))2 way lookups by nesting XLOOKUP. Remember, inner XLOOKUP returns a list of [Net Sales]
JamieWhat is the Net Sales for second person with this name?2724XLOOKUP(H16&"2",FILTER(sales[Sales Person],sales[Sales Person]=H16)&SEQUENCE(3),FILTER(sales[Net Sales],sales[Sales Person]=H16))You can combine XLOOKUP with other new formulas like FILTER() to create something crazy and fun too. Try it yourself.
ChandooWhat is the net sales?Value not foundXLOOKUP(H17,sales[Sales Person],sales[Net Sales],"Value not found")And of course, when the data can't be found XLOOKUP simly #N/As. To prevent this, you can use the 4th parameter to specify output value.

How can I get XLOOKUP?

You need Microsoft Office 365 to use XLOOKUP. You can purchase it from here.

I am using different version of Excel, Can’t get XLOOKUP…

Don’t beat yourself up if you can’t lay your hands on that sweet sweet XLOOKUP yet. Excel already has 100s of powerful functions to perform magic on your data. See below list for alternatives.

Scenario? XLOOKUP Alternative
Regular lookupsUse VLOOKUP or INDEX+MATCH
Lookup in the middle, get value from elsewhereUse INDEX+MATCH
Get the last item (lookup from last)Lookup last value trick
Wild card lookupsVLOOKUP does wildcard search too
Nearest value lookupsSee this example
2-way lookups (lookup row & column intersection)Use INDEX MATCH MATCH
IF not found optionUse IFERROR

Download XLOOKUP examples workbook

Click here to download the 13 XLOOKUP examples file. Kindly note that it will not work if you do not have XLOOKUP. So upgrade your office first.

Here is another Example workbook, this is used in the video above.

Got XLOOKUP yet? Share your thoughts

Have you played with XLOOKUP yet? I have it for just under 3 hours now and I am super excited to use it. What about you? Please share your thoughts and creative uses of it in the comments section.

Share on facebook
Share on twitter
Share on linkedin

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.

– Brenda

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

42 Responses to “What is XLOOKUP? 13 formula examples to really understand it”

  1. Ron says:

    Sure it's a nice new command. It would be useful if everyone had access to it. But if there is any chance you will be sharing the file with someone who has a onetime payment Office license, or an older version of Office you can't use it.

    • Chandoo says:

      That is my biggest gripe with many new features MS is launching. With such vast userbase and existing spreadsheet "systems", all of these formulas are going to create more trouble than imagined. That said, we should learn new things, especially if you move to a new job chances are you will be using a different version of Excel there.

      • Ron says:

        I love to learn new things, like this new command. But I can't afford, literally don't have the money, to keep paying for 365.

        This is the thing that especially offends me about the Office 365 pricing scam/scheme. Sure, if they want to milk more money from users using the rental scam, fine I know I don't have to fall for it. But restricting new "features", like new commands to 365 is offensive. It makes one-time payment users "second class" customers, especially anyone who has paid for Office 2019. At least in the past new features/commands came only came out every few years, with new versions so there was some logic to the separation. But now the new features are coming every few months and there is no real separation between 2019 and 365, but still they limit the new features to 365. Even 2016 is close enough. MS "accidentally" pushes a few new features to 2016, when they feel like it or when they are too lazy to do the extra work to prevent them from going to 2016.

  2. Narendra says:

    Yes, of course this is the latest and excellent update from Microsoft but this feature will take years to come in the market because most of the people or offices are still using Office 2007 or 2013.

  3. Dear Chandoo Sir

    Thank you for updating latest idea this idea is centralized lookup formula all about.

    this idea is realy impressive and samart

  4. I couldn't observe any benefit, over MATCH+INDEX.

    • Chandoo says:

      Hmm, the base scenario is similar to index+match, but XLOOKUP makes life simple with single formula and default "exact match" setup. Plus I find the "lookup from last" and "less than" "greater than" options very useful and less cryptic than MATCH options.

  5. Nicole says:

    Thanks for sharing, it added some excitement to my Friday morning! I don't have 365 but am still excited to be aware of the existence of these features! I know that vlookup on larger sets of data can really take up some resources--it makes sense, it's performing a lot of operations for us while we sit and sip on coffee. 😉 However, I'm wondering if you've you noticed a difference in performance with xlookup? Is it slower, faster, or pretty much the same in terms of calculation speed?

    • Chandoo says:

      I haven't tested it against VLOOKUP or INDEX+MATCH. If anything, I would guess that the performance should be similar as they could all use same logic internally. I will try this and share some outcomes later.

      • Kate says:

        I would love to know the results. We're crunching a ton of data and I love the simplicity of XLOOKUP, but we can't handle the sluggishness of VLOOKUP. I hope XL is faster!!!

  6. Fernando Navarro says:

    A nice addition to the function list. Very usefull and easier to use then INDEX + MATCH.
    Since XLOOKUP is in beta testing, it would be great if Microsoft development team added a 5th. argument: if_na. That is: if XLOOKUP returns #N/A, an alternate value could be returned instead. Therefore, it wouldn't be necessary to do =IFNA(XLOOKUP(...), value_if_na).

  7. Abdul Kader Salaymeh says:

    Although I don't have and expecting to be around soon in EXCEL 2019, my question is there a way to work around the new function "xlookup" but not the old ones.
    However it is appreciated tip,thanks

  8. Hui... says:


    You can also use XLookup like

    Refer the example 4 at:

    This makes it hugely powerful as it is returning an address like Index can do

    • Chandoo says:

      Great point Hui. I am yet to find a practical use case for summing between lookups, but I am pretty sure others will find this useful.

      • Peter Bartholomew says:

        Here is an idea.
        If you wish to analyse data for a given month, the relevant portion of the Sales table (sorted by date) is given by
        = XLOOKUP( EOMONTH(month,0), EOMONTH(+sales[Date],0), sales,0,1 ) :
        XLOOKUP( EOMONTH(month,0), EOMONTH(+sales[Date],0), sales,0,-1 )
        which can be referred to as a named formula 'selected'. Being a reference to the original table, range intersection with columns works. Hence
        = XLOOKUP( MAX(selected sales[Net Sales]),
        selected sales[Net Sales], selected sales[Sales Person] )
        provides an answer to
        Who had most sales for February?

        Caution: The formula requires 7 separate searches of the data but they are very fast.

  9. Hamish Waugh says:

    I use VLOOKUP a lot with named ranges, are you able to reference those in XLOOKUP?

    • Chandoo says:

      @Hamish... you should be able to use any reference styles that work with other formulas in XLOOKUP. So yes for names, structural, cell and references to other sheets / workbooks.

    • Peter Bartholomew says:

      Hamish, Yes it all works perfectly. That includes cases in which the data table does not comprise raw data but rather is made up of dynamic arrays. Naming the anchor cell of each dynamic array allows expressions such as
      = XLOOKUP( MAX(selectedNetSales#), selectedNetSales#, selectedSalesPerson# )

      Conversely, if the returned field is comprised of anchor cells for separate dynamic lists (e.g. employment data for the specified salesman) then the list can be returned by adding '#'
      =XLOOKUP(0,sales[Net Sales],EmployeeInfo,1)#

  10. kphagen says:

    Since the documentation says it returns a reference array, could you write formulas that could answer questions that need to perform a function upon a result set that contains multiple rows such as:

    1. What is the total Profit/Loss for SalesPersons named [Jamie]?
    2. What is the MAX/MIN Net Sales for SalesPersons named [Jamie]?
    3. What was the Average Net Sales for everyone that had exactly [8] Customers?

    • Peter Bartholomew says:

      I think the answer to your question is 'no' unless you are willing to sort the table so that the records you wish to aggregate form a continuous range. That is, the formula
      = SUM(
      XLOOKUP(salesPerson,sales[Sales Person],sales[Profit / Loss],,,1):
      XLOOKUP(salesPerson,sales[Sales Person],sales[Profit / Loss],,,-1))
      only works if the data is sorted by Sales Person.

      Otherwise it looks like SUMIFS (and similar) offers the best solutions with FILTER a close second.
      = SUMIFS( sales[Profit / Loss], sales[Sales Person], salesPerson )
      = SUM( FILTER(sales[Profit / Loss], sales[Sales Person]=salesPerson ) )

  11. Duncan Williamson says:

    XLOOKUP allows us to look for a variable in a column and return a value from a row: combining VLOOKUP ad HLOOKUP in essence.

    I watched a video last night in which the presenter showed an example that returned an error. The solution that the presented was using is this: =XLOOKUP(A4,B7:B9,C6:E6)

    To see the problem in action, put a b c in the range B7:B9 and 1 2 3 in the range C6:E6 and in A4 enter a or b or c

    I solved this problem in this way:

    I have also set up a financial analysis example in which I wanted to find, for every line item in an income statement, which month was exactly equal to the mean of that row or which was immediately below the mean or immediately above it. Or Median, or Standard Deviation ...

    I used XLOOKUP() and IFS() together with Data Validation (although that is optional) and while the formula is a little unwieldy, again I am effectively combining vertical and horizontal lookups.

  12. Rohit Tiwari says:


    Can you please tell me if there is any way to return multiple values with a single match.
    Thanks in Advance

  13. Abdul Kader Salaymeh says:

    when will be in excel 2019

    • Ron MVP (2012-2018) says:


      "New features" like the XLookUp() command are only added to Office 365. They will never be added to Office 2019. They may show up in Office V-Next, when ever it comes out, in the near future. MS has not yet announced a new version. If they follow the pattern in the last few versions that would be fall 2021. But that is only a guess.

  14. wschloss says:

    I downloaded your sample spreadsheet and three of your first seven examples are incorrect. Then I stopped.

  15. Sherry says:

    Is there an easy way to handle if the cell is blank in the data table to prove the result of a blank? With VLOOKUP, previously to get this result, I had to do:


    I am hoping that I don't have to resort to the same lengthy format. I did try the "Value Not Found" example you provided (love it). However that is when the search value is not listed, not when the search value is found and the result value is a blank cell.

    Thanks for everything you do!!!!

    • Chandoo says:

      Hi Sherry,

      Are you using the IF formula to show "" instead of 0 ?

      If so, you can use this structure

      =XLOOKUP($B$2, data[col1], data[col6]) & ""

      This will force 0 to convert to empty space. It won't impact other results though, (assuming column 6 is text)

  16. Peter Bartholomew says:

    A bit longer, but to force the 'value not found' you could remove the entry from the lookup array
    = XLOOKUP(lookupValue,
    IF(data[col6]"", data[col1]),
    data[col6], "Missing data")

  17. Stuart says:

    Hi Chandoo,

    I've been waiting for this function for months so that I could replace all my INDEX / MATCH / MATCH statements. However, I have hit a snag with using nested XLOOKUPs as replacements. If the inner XLOOKUP can't find a value, then whatever value I specify as the [if not found] value causes the outer XLOOKUP to fail and return #VALUE. So the [if not found] functionality works if a single XLOOKUP can't find the search value, but it causes nested XLOOKUPs to fail. Can you see any way around that?


    • Chandoo says:

      Hey Stuart... Can you share an example of what result you are expecting in nested case? One option is to use a single IFERROR outside all the nested functions.

  18. Peter Bartholomew says:


    Do not limit yourself to thinking of [if_not_found] as being a text string, e.g. "Oops"; it can be a formula in its own right, returning a default row from the original table or even a lookup from an alternative table.

    What it must return is an array in order to form a valid parameter for the outer XLOOKUP.

    • Stuart says:

      Hi Peter,

      You've got it! As you suggest, by setting the inner XLOOKUP to return an array full of zeroes (or whatever) solves the problem. The outer XLOOKUP can of course just have 0, or whatever, stated its if_not_found value.

      I am surprised that I haven't come across this issue or solution anywhere else. There are lots of blogs / videos which mention using nested XLOOKUPs as a replacement for INDEX / MATCH / MATCH. I can't say I've read or watched them all, but the ones I have don't mention this issue. I suspect there are / will be a lot of people getting #N/As or, worse, #VALUES depending on what they specify as the inner function's if_not_found.

      Thanks for your help!

  19. Kathryn says:

    I am trying to lookup a date and name and return the number of hours from another worksheet? If I'm mixing text and dates, will this still work?

  20. Michael says:

    Great article. But,...two questions:
    1) I do have Office 365. Yet, the XLookup is not recognized by Excel. Your sample file displays a #NAME? Why?
    2) In your samplefile you have a leading '_xlfn.' in front of the formula. Why is that?

    • Chandoo says:

      Hi Michael...

      Can you confirm what is your current version of Excel is? Also see if you can update to newer version. You can do both from File > Account.

Leave a Reply