fbpx
Search
Close this search box.

What is XLOOKUP and how to use it in Excel?

Share

Facebook
Twitter
LinkedIn

Think of XLOOKUP as an improved version of VLOOKUP. In this article, learn all about the XLOOKUP function, it’s syntax, parameters and how to use it with real-world xlookup examples.

Xlookup example with syntax

=XLOOKUP(lookup_vaule, lookup_array, return_array)

What is XLOOKUP?

Using XLOOKUP, we can search for an item in a list using the lookup value and return a matching item. For example, you can lookup for salesperson “Jackie” and return their sales amount from the data below using the XLOOKUP function, as depicted above.

It is the newest member of Excel’s lookup function family. You may already know the other members of this group – VLOOKUP, LOOKUP, HLOOKUP, INDEX+MATCH.

How to use XLOOKUP – step by step instructions

Let’s say you have data for salespeople like above and you want to find the Net Sales for “Jackie”. Follow below steps to create the XLOOKUP function in Excel.

  1. Write =XLOOKUP( in a cell
  2. For the lookup_value, Type the name of the salesperson in double quotes (ex: “Jackie”)
  3. Tip: If you have the name of the person in a cell (like G4), you can point to the cell instead of typing the name
  4. Now for the lookup_array, select the names column of your data.
  5. and for the return_array, select the net sales column of your data.
  6. Close the brackets and hit enter.
  7. Congratulations, you’ve just created your first XLOOKUP formula in Excel.

Here is how the formula and result look in my data:

Demo - How to create XLOOKUP formula in Excel - step by step instructions.

What if the lookup value is not in the lookup array?

Using the if_not_found option with XLOOKUP to fix errors

One of the most common scenarios of lookups in Excel is not finding the value you are looking for. Imagine, you are looking for the salesperson “Chandoo”, but he doesn’t exist in the dataset. In this case, XLOOKUP will return #N/A error.

But we can use the 4th parameter of XLOOKUP – if_not_found to set an optional value to display when there is an error.

Here is an example formula with that:

How to lookup in the middle with XLOOKUP?

We can use any column or range as lookup array with XLOOKUP. This means, we no longer need to use INDEX +MATCH formulas to do lookups!

Let’s say you want to lookup the sales amount of $726 and return the name of the person (in this case, Jessy). Previously, you needed to use the INDEX+MATCH combination for this. But XLOOKUP let’s us specify any range or column for lookup_array portion. so, no more INDEX+MATCH… Yay!

No more need for index match formulas - thanks to XLOOKUP

To perform the “net sales” lookup and return the name of the person, we can use the below formula:

The above XLOOKUP example assumes that G4 contains the net sales value you want to lookup.

How to lookup higher than / lower than using XLOOKUP? (approximate match)

xlookup with approximate match example

Say, you want to find out the closest person with the net sales of $1300. In this case, we don’t have anyone with that value in the data. You can still use XLOOKUP to get the approximate matching value, either next higher or lower in the data.

For this we can use the 5th parameter of the XLOOKUP function – match mode.

There are 4 match modes in XLOOKUP.

  • 0 or Exact match. This is the default value for XLOOKUP.
  • 1 or next larger match. This looks up for the exact or next highest value in the lookup array.
  • -1 or next smaller match. This looks up for the exact or next lowest value in the lookup array.
  • 2 or wildcard match. This looks up based on a pattern you have mentioned in the lookup value. More on this further down in the article.

To get the next highest matching value:

The above formula returns “Jonathan” in the sample data, as he has the next highest amount – $1316.

To get the next lowest matching value:

This formula returns “John” as he has the next lower value – $1088.

Getting Partial Matches with XLOOKUP (Wildcard / Pattern Matching)

XLOOKUP also allows for a powerful and elegant pattern matching in your data. Let’s say you want to find the net sales for the person whose name begins with the letters Jam. In this case, we can use the match mode 2 (wild card) along with the wildcard operators * (asterisk) and ? (question mark) to create our XLOOKUP.

To find the net sales of the person whose name begins with Jam,

  1. Use the formula =XLOOKUP(H4&”*”,B4:B21,D4:D21,”No such person”,2)
  2. Here H4 contains the first few letters of the name, i.e. Jam
  3. The lookup value is H4 & “*”. This tells XLOOKUP that we want the name to begin with the value of H4 (Jam) and then there can be any number of characters.
  4. Rest of the XLOOKUP parameters are as per usual.
  5. Don’t forget the match mode operator as 2. We need this for Wild card – pattern matching.

Refer to above illustration for more on the pattern matching xlookup.

Additional Pattern Matching Tricks with XLOOKUP

Refer to below handy table for some extra tips on using the partial matching feature of XLOOKUP.

SituationFormulaExplanationSample Result
Name ends with ved=XLOOKUP(“*ved”, B4:B21,D4:D21,”No such person”,2)To find a word ending with, we use * (asterisk) at the beginning of the patternJaved, $2277
Name contains ack=XLOOKUP(“*ack*”, B4:B21,D4:D21,”No such person”,2)We can use two * symbols – one at the beginning of the pattern and one at the end. Jackie, $1610
Name has 5 letters and begins with Je=XLOOKUP(“Je???”,B4:B21,D4:D21,,2)In this case, we can use the ? (question mark) symbol to indicate that we can have any one character. So the pattern is Je???Jessy, $726
Name begins with J and ends with d=XLOOKUP(“J*d”, B4:B21, D4:D21,”No such person”, 2)J*d means the lookup value begins with J, contains any number of letters and ends with dJaved, $2277

What if there are two matching values?

xlookup finds the first matching item if there are more than one in your data.

If you have more than one matching item for the lookup value in your data, XLOOKUP, just like all other lookup functions in Excel, will always return the first matching item’s corresponding value.

As you can see in the above illustration, we have two sales persons with the name Johnson in our data.

When using =XLOOKUP(“Johnson”, B4:B21, D4:D21) we are going to get the net sales of the first Johnson ie $1540.

But what if I need to get the second or third or all the matching values?

In this case, you can use the new FILTER() function in Excel to get the second or all matching items.

Here is the formula.

For more information on FILTER function, refer to this article.

Horizontal Lookup with XLOOKUP:

XLOOKUP can be used with horizontal data too, thus replacing hlookup formula.

XLOOKUP works just as good with horizontal data too, thus replacing any need for HLOOKUP function.

In the above example, I have monthly budget table and I want to lookup the budget value for April 2024.

We can use this XLOOKUP formula to do just that.

Returning entire row of information with XLOOKUP:

Another powerful feature of XLOOKUP is that it can return multiple values all corresponding to the same lookup value. For example, I want to see the budget, actual and balance information for the month of April 2024, from my budget spreadsheet below. We can use XLOOKUP for that easily.

In the above formula, by using multiple rows (C4:N6) as the return array, we can return all corresponding values for the lookup value in C10 – ie April 2024. Excel will automatically spill these values into separate cells on the worksheet.

xlookup formula can return all the values for the month of April 2024 - and spill them on the screen thru dynamic array behavior of Excel

XLOOKUP – things to keep in mind:

When using XLOOKUP, you must keep these points in mind:

  • XLOOKUP needs Excel 365 or Excel on the web or Excel 2021: This function is not available in all the versions of Excel. So check your version of Excel before you start using xlookup. If you notice #NAME errors when working with XLOOKUP, that means your version of Excel does not support this function. Instead, use INDEX+MATCH formula.
  • Use the If not found option to fix errors: To avoid any lookup errors (#N/As), use the fourth argument of the XLOOKUP function – if_not_found.
  • Lock cell references when creating lookups: If you are writing multiple XLOOKUPs in a range, don’t forget to lock your cell references to absolute mode (change B4:B21 to $B$4:$B$21) so that when you drag or fill the formula down, your lookup and return array ranges don’t change. This is a common mistake and you will end up with wrong results. (absolute vs. relative references)
  • Or better yet, convert your lookup data to a table: A simple fix to the relative reference issue is to use tables on your data. This way, you can write simple XLOOKUP formulas like this: =XLOOKUP(“Jackie”,sales[Sales Person],sales[Net Sales])
  • In case of multiple matches, XLOOKUP always returns the first (or last matching item, if you used search mode option) value. This is why it is important to also learn how to use the FILTER function in Excel.

How is XLOOKUP better?

  • XLOOKUP makes the most used formula 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 wildcard 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. For example: XLOOKUP to create dependant drop down in Excel.
  • 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.

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.

5th parameter - match mode

6th parameter for XLOOKUP: Search mode

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

search mode - 6th parameter

XLOOKUP – Video Tutorial:

Here is a simple but effective video tutorial on how to use XLOOKUP function and how it can replace VLOOKUP and INDEX+MATCH functions. You can also watch the xlookup tutorial on my channel.

Download XLOOKUP examples – workbook

Click here to download the 13 XLOOKUP examples file.

And one more example file, this with INDEX+MATCH replacements.

Final thoughts on XLOOKUP

In my opinion, XLOOKUP is a terrific function and a must have for any data professional. It is part of my essential Excel formulas list for data analysts.

When we nest XLOOKUP formulas, we can also perform more complex lookups like 2-WAY lookups or search across different worksheets. I discuss some of these advanced scenarios in a recent video on my YouTube channel. Please watch it here.

The only downside of XLOOKUP is the compatibility. It doesn’t work in all versions of Excel. For this reason, I still think there is value in learning how to use VLOOKUP and INDEX MATCH functions.

Do let me know what you think about XLOOKUP and if you have any questions about it using the comments section.

Learn more about Data Analysis with Excel:

This post is part of my data analysis with Excel series. Please learn other topics too and improve your data skills with Excel.

Facebook
Twitter
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

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
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.

66 Responses to “What is XLOOKUP and how to use it in Excel?”

  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.

        • Brian Crawford Wilson says:

          I agree with Ron I have MS Office 2019 which I used for Charity work but a pensioner I find the cost of the MS365 unaffordable. Perhaps there is some way for a Ms Guru to perhaps create 3rd party update for the stand alone versions.
          I will however continues with Ms 365 this year as I have just renewed the subscription
          thanks very much for keeping us abreast of latest developments and also the excel community for their useful feed back
          regards Brian 18/03/2024

          • Chandoo says:

            Good point. I suggest using the free MS Office online (you just need onedrive account) to maintain old files and work on them. The only limitation is that it is browser based, so you won't be able to do many advanced things. But it is better than the alternative of shelling out $100+ every year.

  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?

  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?

    • 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:
    =XLOOKUP(A12,B15:B17,TRANSPOSE(C14:E14))

    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:

    Hi,

    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
    Thanks

    • Ron MVP (2012-2018) says:

      Never.

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

    • Abdul Kader Salaymeh says:

      I have it now in office 2021

  14. wschloss says:

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

    • Chandoo says:

      Which version of Excel are you running? XLOOKUP doesn't work in any version except Office 365.

      • Tom says:

        Hi, Chandoo.
        Great tips, thanks!

        In example #11, "What is the 'net sales' for Johnson? = 1540" the formula only takes into account the first match for Johnson (D10)?
        In row 21 Johnson appears again so the correct answer should be 4192 (D10 + D21).
        Imagine a DB with hundreds of records!
        How can we deal with duplicates using XLOOKUP?
        Thanks.

  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:

    =IF(VLOOKUP($B2,data,6,FALSE)="","",VLOOKUP($B2,data,6,FALSE))

    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?

    Thanks

    • 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:

    @Stuart

    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.

  21. Jonathon Tom says:

    My values that I want to join are not exact, i.e.

    000025868 and 0000258 68 Total

    Is there a way to join the data?

  22. Jonathon Tom says:

    Getting a #N/A as the results.

    Is there a way to convert "0000258 68 Total" to 000025868 (or visa versa) before I run the =XLOOKUP?

    • Chandoo says:

      If you just want to remove the word "total" at the end, use SUBSTITUTE for that. If there can be other words, you are better off first running the data thru Power Query so you can clean it.

    • Peter Bartholomew says:

      One thing that is possible is to take a numeric lookup value and convert it to text before searching a text lookup array. For example
      = XLOOKUP(TEXT( value, "0000000\?00\*" ), array, return, , 2 )
      will perform a search with wildcards that allow "Total" to be appended or any character to be inserted two digits before the end of the number.

      That would pick up
      "0000258 68 Total"
      but you would need an alternative test to match the number 25868, itself.

  23. Amit says:

    Check the reference, while selecting data the xlookup function automatically starts from new line. Try changing it to the first row and it would work.

  24. Veronica says:

    YOU ARE THE EXCEL KING!
    Thank you

  25. DDong says:

    Hi Chandoo,
    I have 2 sheets with 5 columns. data in columns A:C is similar except that changes are made in columns A and C. I want to lookup in column C in Sheet2 and update Sheet1 columns A:C.
    for example
    Sheet1
    ColA ColB ColC
    123 AB12 One
    234 BC23
    323 CB22 Six

    Sheet2
    ColA ColB ColC
    123 AB12 One
    234 BB22 Two
    323 CB22 Six

  26. David N says:

    I don't think we can claim that XLOOKUP "replaces" INDEX+MATCH. Yes, it provides a suitably powerful alternative, and is absolutely a full replacement for VLOOKUP and HLOOKUP, but it can't easily play some of the "math" games that are possible with INDEX+MATCH and sometimes even necessary when the data isn't in a convenient layout.

    What if you needed the row above or below the match or if the data was laid out in repeating sections where you first needed to know the location of the section header and then the location of a given item within each section? Both of those problems can be solved with plus/minus shifting of the number returned from the MATCH.

    So I would argue that INDEX+XMATCH are the true replacement for INDEX+MATCH, thus taking full advantage of the X -- defaulting to exact matches, virtual sorting, and so on -- while preserving the ability to "shift" the match as needed.

  27. Stephan Chayer says:

    I'm looking for a price in a multiple column price list. With Vlookup, I specified the entire table and for the column, looked at the user selected model/column. In Xlookup, how to specify the column number and the range up and down or can I just specify the column number only?

    • Wiz says:

      One advantage that VLOOKUP retains over XLOOKUP is the ability to supply a lookup column number dynamically, as a purely numerical result of a calculation. To replicate this functionality using XLOOKUP, you would need seperate logic to calculate the column reference (i.e. the column's number, range name or range address) and pass it to the XLOOKUP formula. You could do this inside the XLOOKUP function by setting up the 3rd param of XLOOKUP to be based on your "user selected model/column".

  28. KS Jolly says:

    Using Xlookup with "match mode" = -1 and "if not found" = "ABC"

    Now if the lookup value is not found in the lookup_array excel gives the the highest value from the return_array.
    This is not what I expect from xlookup.
    It should return "ABC"

    Can you explain why?

  29. Jennifer Jeffords says:

    Chandoo,
    I am having trouble with XLookUp. How do I get it to return multiple values such as employees with salary greater than $45,000 or to sum all the sales in the East region? Are these more pivot table inquires?

    Is XLOOKUP more useful for finding one record than multiple records?

    Thank you,
    Jennifer Jeffords

  30. CK says:

    Hi Chandoo,

    Is it possible to use XLOOKUP to return a status such as "Checked" and "NoCheck"(something similar to IF stmt)

    Thank you.

  31. Paula Paden says:

    I used the index and match to look up the hourly rate for a job classification as a part of a drop down. Now, I want to calculate the hourly rate multiplied by hours worked and the cell will not calculate. What might be the problem? The results cell of the look-up is formatted to be currency?

  32. Dorothy Adams says:

    You show return array can be more than 1 column but what about Look up array? What if I want to find a value than can be in 1 of 3 columns and then return one value from another column.

    • Chandoo says:

      You can use XLOOKUP for such things too.

      For example, if you have three columns: home phone, cell phone and email address
      and a column with customer name
      and you want to lookup the name of the customer when you specify any value from one of those 3 columns,
      you can use the below XLOOKUP.

      =XLOOKUP(TRUE,BYROW(C3:E22=I2,LAMBDA(a, OR(a))), B3:B22, "No record found!")

      Here I2 contains the search criteria (either home phone, cell phone or email)
      B3:B22 have names
      C3:E22 have the home / cell / email values

  33. Musawir Rasool says:

    Hi my name is Musawir Rasool i am from India in a state of jammu and Kashmir I love watching your videos and lot from your videos
    Thanks
    And one more can u teach me full power bi?

Leave a Reply