fbpx

What is XLOOKUP? 13 formula examples to really understand it

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Over the last few days Microsoft released XLOOKUP formula to the early adopters of Excel. Think of XLOOKUP as VLOOKUP 2.0. In this post, 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)
  • 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.

XLOOKUP Syntax

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.

[NEW] 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

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.
CriteriaQuestionAnswerFormulaNotes
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 can get it immediately, if you have Office 365 and signed up for insider program. The insider program is easy to sign up. Just open Excel (or other office applications) and go to File > Account and click on Office Insider and select “Change level”. Sign up for weekly updates and you are good to go.

Join office insider program from your Office 365 account options in Excel > File > Account page.

When you sign up for insider program, Microsoft will release weekly new version to your computer. Be on the lookout for features that may disappear or change the look from week to week as MS will test things before launching them to wider audience.

Note: As per Joe McDaid‘s (Project Manager in Excel Team) latest tweet, if you are on insider program, then you should have access to XLOOKUP, so no need to wait.

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.

This file is updated on 4th of November to reflect new syntax of XLOOKUP with 4th parameter – if not found option.

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
Facebook
Share on twitter
Twitter
Share on linkedin
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

Chandoo is an awesome teacher
5/5

– Jason

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.

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.

22 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!!!

        • Peter Bartholomew says:

          I believe XLOOKUP has been written to deliver exact matches at the same speed as a binary (vlookup's approximate) search.

  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:

    Chandoo

    You can also use XLookup like

    =Sum(xlookup():Xlookup())
    Refer the example 4 at:
    https://support.office.com/en-us/article/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929?ui=en-US&rs=en-US&ad=US

    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?

Leave a Reply