Comments on: XLOOKUP function in Excel with formula examples

The tutorial introduces XLOOKUP - the new function for vertical and horizontal lookup in Excel. Left lookup, last match, Vlookup with multiple criteria and a lot more things that used to require a rocket science degree to accomplish have now become as easy as ABC. Continue reading →

Comments page 2. Total comments: 107

  1. I am trying to work out this formula =IF(A2>0, "Non Compliance","Compliance") but I want to exclude blank cells. How can I do that?

  2. Can a nested Xlookup return a result based on criteria in two different columns? I noticed that the most successful results are when there is one horizontal and one vertical.

    1. Hi Paul,

      Sure, it can. Please check the "XLOOKUP with multiple conditions" example in this tutorial.

  3. Hello,

    I am wondering if it's possible to have a multiple results but from the same column.

    For example I have value X that I am trying to find in column ''Y'' and than return the value from column ''Z'' next to it. This is pretty standard.

    But X appears a few times in ''Y'' with different value in ''Z'' - and I want the formula to return all those different values. Is it possible?

      1. Hello!

        The instruction that you linked is exactly what I need (this section: Vlookup to return multiple results in one cell (comma or otherwise separated)).

        My issue is that the values I want returned are on the left side of the cell with the looked up value - that's why I was wondering if it's possible to do something like that with xlookup since here we don't have to worry about that.

        1. Hi!
          I don't think you opened the article I recommended. Do that and see that the VLOOKUP function is not used and the order of the columns is irrelevant.

  4. Thanks for the great article. Wondering if you can suggest if it is useful for my situation below:
    I am trying to categorize purchases from my credit card bill. The entries look something like this:
    A B
    MARKS&SPENCER LONDON $100
    ITUNES 1234 $0.99
    TESCO PAY AT PUMP LIVERPOOL $97.00
    MARKS&SPENCER GATESHEAD $15.00
    ITUNES 0987 $0.99

    Column C should classify each item into a category (eg Supermarket, iTunes or Petrol) based on the content of Column A. I am currently using a IFS(ISNUMBER(SEARCH formula to check column A for partial text (i.e. "MARKS&SPENCER") and return a category but the formula has become very long. Can this be done using an array instead?

    1. Hello!
      Use the SEARCH function to determine partial matching of text strings.

      =INDEX($E$1:$E$5,MATCH(TRUE,ISNUMBER(SEARCH($E$1:$E$5,A2)),0))

      Use INDEX MATCH to get the desired value from the list of categories $E$1:$E$5. I hope it’ll be helpful. If something is still unclear, please feel free to ask.

      1. Thank you Alexander, that got the job done

        Cheers

  5. I am using a XLOOKUP in order to sort and match results between two tables based on a unique ID number.

    the formula I am using looks like this: =XLOOKUP(A2,LiveProjects!M:M,LiveProjects!P:P,"",0,1)

    This seems to be working only for SOME of the IDs i have in the table I am using, but not all. Does this not work if you are searching using a value in A2 which is the result of another formula?

    I.e. does the look up only look in the text in the cell and not the result given by that text?

    1. Hello!
      The XLOOKUP function works with values that are the result of other formulas. If these values are numbers, pay attention to the decimal values and use round numbers.

  6. Hi - this is the best tutorial I've come across. Thanks

    1- I have two Sheets (LISTING= with columns: Vendor Name, ID# and Hotel; & HOTEL= with 9 columns that contain the first 3 cols as: Priority Vendor1, ID# & Email ; Priority Vendor2, ID# & Email; and Priority Vendor3, ID# & Email

    2- I need to return the vendor name found on either one of the Priority Vendor1, 2 or 3 cols based on the ID# on the LISTING sheet, but I haven't been able to get results by doing a search of the ID# on (sheet1) on the 3 cols of sheet2

    Is this possible?

  7. I am comparing 2 columns. The first column has 16318 rows and the second 18526 rows. The formula =XLOOKUP(C2, B2:B18584, A2:A18584, "No match is found") works perfectly.

    My question: Is there a way to populate the formula in column "D" incrementing the C2 value without changing the range values? When I use ctl and pull the corner of the cell down to copy into subsequent cells the first cell value of the ranges also increments resulting the elimination of one cell for every copy.

    Example: =XLOOKUP(C61, B61:B18584, A61:A18584, "No match is found")
    Thanks for your insight.

      1. Alright I know this is very late but I must let you know how much I appreciate this response. It has corrected something that was plaguing me tremendously and saved so much data entry time. Thank you for the assist.

  8. Is XLookup able to return the first result that has a negative number?

  9. Is there a way to use xlookup (or maybe it would be something else) to return one value from two. To explain I have a table in document 1 that has included in it first names (column c) and last names (column B) and I want it to find and input the ID number (column A) from document 2. Document 2 has all of the above information and other information that I don't need, all in separate columns. So I need it to get a match to both first and last name for it to get the ID number. Unfortunately, the way it is set up, I cannot do the inverse and input the ID numbers and search for the first and last names. Is there a way to do this? Another option is I can have it in document 1 as LastName, First Name in column B and ID number in column A, but document 2 would still have the last name and first name separate.

    Thanks in advance!!

    1. Hello!
      You can search by two criteria with INDEX+MATCH or XLOOKUP. See formula examples here.
      To input the ID numbers and search for the first and last names, you can use the paragraph in this article above: XLOOKUP to return multiple columns or rows.
      I hope I answered your question. If something is still unclear, please feel free to ask.

  10. Hi,
    I need to retrieve data under certain conditions from one sheet to another, and here is the formula I am using: =XLOOKUP((IF(OR('by sample'!$H4="cheek, right",'by sample'!$H4="cheek, left"),'by sample'!$H4,"")),'by sample'!$H4:$H1000,'by sample'!B4:B1000,,0).
    It works well, but when the results is N/A I would like excel to find the next xlookup value.
    I am trying to write an IFNA of the sort: IFNA(XLOOKUP(...), find next xlookup value) to avoid removing the cells manually/with find and select tool, but without success...
    How could I proceed?

  11. HI,
    I am trying to copy down the XLOOKUP function to use it for an entire column but it is changing the search array.
    EXP.
    XLOOKUP(B1, C2:C6, D3:D7) then when I copy down I want XLOOKUP(B2, C2:C6, D3:D7) but I get XLOOKUP(B2, C3:C7, D4:D8). It shifts all by 1 cell instead of just the search value.

    How do I get it to automatically shift the search but not the other 2 array?

    Thanks

  12. Hello - has this replaced the use of Transpose and Filter? I used to use this formula to look up an array that had multiple instances and returns in one column: "=TRANSPOSE(FILTER(C2:C6, B9=B2:B6))". Where C2:C6 had the expected outputs (more than one output but only in one column) B9 is the criteria to lookup and B2;B6 is the array to look for it in.
    When I use it now I need a NAME error which seems to indicate its no longer a recognized formula name.
    I cant find the equivalent in the xlookup formula i.e. where the multiple responses are all in the same column.

  13. Very informative, thank you. I was particularly interested in XLOOKUP with multiple criteria. You use:

    XLOOKUP(1, (criteria_range1=criteria1) * (criteria_range2=criteria2) , return_array)

    I had not seen this before, and previously I had used concatenation, for example:

    XLOOKUP(criteria1 & criteria2, criteria_range1 & criteria_range2, return_array)

    But this can be very slow if you are referencing whole columns. Do you know if your approach is faster? I suspect it would be.

    1. Hello!
      I do not recommend using the entire column as criteria_range (for example, A:A). This will slow down the calculations significantly. Use the range where the data is actually located (for example, A2:A100).

      1. Thanks Alexander. Yes I appreciate that using specific ranges is faster than entire columns. In some models though I find column references are preferable, as the formulas are easier to read and you don't have to worry about users adding new data to rows outside the range.

        I guess my question is, *for a given range of data*, whether there is a significant difference in speed between the two XLOOKUP approaches above. I might have a play around to see.

  14. Hi -
    this may not be an xlookup question.

    I have one sheet with a list of protein names in column A, and on another sheet, a list of biological pathways (column A), with the corresponding proteins that are found in that pathway listed across columns B-Z in that row. Every pathway has column B filled (at least 1 protein per pathway), but after that has a variable number of entries depending on how many proteins are associated with that pathway (from 1 up to 25, with any remaining cells blank). All proteins on sheet 2 are contained in the list on sheet 1, and vice versa. There should be no "not founds".

    I want to enter a formula in sheet 1 by which sheet 2 will be searched for each protein name and for each, return which pathways it is associated with, returning those pathway names into sheet 1, column B (and C and D and so on until all relevant pathways are listed).

    a small made up example, separate column entries separated by pipes
    sheet 1 (single letter protein names)
    A
    B
    C
    D

    sheet 2 (pathway name|protein|protein|protein)
    Notch|A|B|C|D
    WNT|A|B|D
    EGFR|C|D|A
    RAS|D

    desired results, sheet 1:
    A|Notch|WNT|EGFR
    B|Notch|WNT
    C|Notch|EGFR
    D|Notch|WNT|EGFR|RAS

    Any help appreciated.
    thanks

    1. Hello!
      To get data by condition, you can use the FILTER function.
      I believe the following formula will help you solve your task:

      =TRANSPOSE(INDEX(FILTER(Sheet2!$A$1:$E$4,(Sheet2!$B$1:$B$4=Sheet1!A1)+(Sheet2!$C$1:$C$4=Sheet1!A1)+(Sheet2!$D$1:$D$4=Sheet1!A1)+(Sheet2!$E$1:$E$4=Sheet1!A1)),,1))

  15. Do you know what is the correct syntax if I would like to implement the following query inside of a VBA code? =XLOOKUP(1, (B2:B10=G1) * (A2:A10=G2) * (C2:C10=G3), D2:D10)
    I would like to implement the following, which works well in pasted in a cell:
    =XLOOKUP(1;(Elisa!$J$2:$J$2015=BH$2)*(Elisa!$B$2:$B$2015=$AY7);Elisa!$E$2:$E$2015;" ")
    What I have tried:
    Worksheets("Adatbázis").Cells(H, I).Value = WorksheetFunction.XLookup(1, (Worksheets("Elisa").Range("J2:J2015") = Cells(2, I)) * (Worksheets("Elisa").Range("B2:B2015") = Cells(H, 51)), Worksheets("Elisa").Range("E2:E2015"), " ")
    This always ends with a Tyme mismatch message around this part: Worksheets("Elisa").Range("J2:J2015") = Cells(2, I). I have tried it several ways but no luck so far...

  16. I really like the xlookup function but I am struggling with a situation I am unable to resolve.

    I want to use the xlookup to find a transaction in a check register and put that transaction in a budget report. I am able to get xlookup to find a single line item for a budget category but when a category has multiple entries xlookup is only returning the first line item of that category. What I am after is not only to find the first transaction of a particular category but to find all of them and add(sum) them all together into one cell. ex: find all amounts paid for groceries then sum them all together.

    I am using the date & category as a reference to find the amount paid in each category. Here is the formula I am using it only finds the first transaction. How do I need to change it to find all transactions and sum them all together in one cell?

    =IFERROR(XLOOKUP($J$4&F15,'Arvest Savings'!$C:$C&'Arvest Savings'!$E:$E,'Arvest Savings'!$H:$H,,0),"")

    The above formula is going into the budget form under the actual column then once the entry is made in the check register it automatically populates into the budget form for the appropriate month,

    It works great for a single transaction where I need assistance is with finding multiple transactions and getting them summed all together.

  17. how does xlookup work for multiple tables - two similar sets of data, one for onshore and one for offshore. I have Line of work (Java, testing etc), Designation (Manager, Team lead, etc), rate for line of work vs designation. This calculation is straight forward if its only for offshore or onshore. Now If i have to get both in one formula should I use IFERROR ?

  18. I'm struggling with XLOOKUP and dates with multiple criteria. Please help!
    I'm trying to return a Lab value for a particular ID collected on a date that is closest to, but before or equal to, the Clinic Date.
    For example for ID: A251527 on Clinic Date 17/09/2008 (in cells E2 and F2 respectively).
    The answer should be 130 on 12/08/2008 but XLOOKUP using multiple criteria seems to return the Lab value on the first date for this ID (124).

    =XLOOKUP(1, ($A$2:$A$17="ID")*(LARGE($B$2:$B$17,COUNTIF($B$2:$B$17,">"&$F$2)+1)<=F2), $C$2:$C$17)

    The data is unsorted and is large ~110,000 rows.

    Clean ID Date Collected Lab value ID Clinic date
    A251300 17/09/2008 118 A251527 17/09/2008
    A251300 16/09/2008 129
    A251527 13/06/2008 124
    A251527 16/06/2008 115
    A251527 18/06/2008 114
    A251527 23/06/2008 113
    A251527 24/06/2008 108
    A251527 24/06/2008 113
    A251527 12/08/2008 130
    A251527 03/11/2008 133
    A251527 19/02/2009 120
    A251527 20/02/2009 112
    A251527 21/02/2009 122
    A251527 22/02/2009 138
    A252601 19/08/2008 136
    A252601 10/02/2009 117

    1. Hello!
      You cannot apply XLOOKUP approximate search on unsorted data.
      If I got you right, the formula below will help you with your task:

      =INDEX(C2:C17,MATCH(MAX(IFERROR((B2:B17-E2) / ((A2:A17=D2)*((B2:B17-E2)<0)),-100000)), (A2:A17=D2)*(B2:B17-E2),0))

      In Excel 365:

      =VLOOKUP(E2,SORT(FILTER(B2:C17,(B2:B17 < E2)*(A2:A17=D2)),2,1),2,1)

      I hope my advice will help you solve your task.

      1. Thanks for replying.
        Your VLOOKUP formula for Excel 365 seems to be missing some arguments at the end? Can you complete please?

  19. hi
    thank you for the post, very useful
    how can I set the Xlookup function to get the first, second, and third result on arranged set when working with multiple data in a table

      1. thank you for your prompt answer
        greetings!

  20. Hi.
    I have a strange issue with XLookup and structured referencing, and I cannot for the life of me work out what I’m doing wrong.
    =XLookup(A2,B:B,G:G) works, but =Xlookup(A2,Table1[ColumnB],Table1[ColumnG]) doesn’t - I get a #VALUE ERROR. Column G contains numbers. There are no spaces or other things that usually cause value errors.
    I don’t think the problem is with my syntax. The table is named Table1, and the two columns are named as above. I have used xlookup in the same format referencing other tables in the worksheet with no issues.
    Any help appreciated.

    1. Hello!
      The formula works for me. I was unable to get this error. Check the column names in your table. Column sizes must be the same.

      1. Thanks Alexander. It seems my problem was using a mixture of structured table references and named ranges in the Xlookup formula. This seems not to be allowed.

  21. Hi everyone,
    I need to perform XLOOKUP such that the criteria is Difference/Subtraction between two cells of column X and column Y. Suppose Quarter 1,2 as columns and i need to check rows where element/value of Quarter2 - element/value of Quarter1 = 25.

    In other words rows in which the difference/subtraction of Column X and Column Y is exactly equal to 25

    1. Hello!
      Please try the following formula:

      =XLOOKUP(1,--(A2:A10-B2:B10=25),A2:B10)

      This formula only returns the first value found. To get all values that meet the criteria, use the FILTER function:

      =FILTER(A2:B10,A2:A10-B2:B10=25)

      Hope this is what you need.

  22. I need to find and list all possible matches for a 'PART NAME" within a column containing FULL NAMES. Example:
    1. JOHN TERRY
    2. ADAM JOHN
    3. ADAM TERRY
    4. CRUISE JOHN
    5. JONATHAN JOHN

    I need to do an XLOOKUP to list all matching Rows containing "JOHN"

    1. Hello!
      XLOOKUP can only return one match, not a list of values. If you need to return a list of values that contain "JOHN", use this tutorial: How to VLOOKUP multiple values in Excel.

      =IFERROR(INDEX($A$1:$A$10, SMALL(IF(ISNUMBER(SEARCH($B$1,$A$1:$A$10,1)), ROW($A$1:$A$10)-1,""), ROW()-1)),"")

      This array formula is on line 2. Copy it down the column.
      I hope I answered your question. If you have any other questions, please don’t hesitate to ask.

  23. Is not working if lookup array & return array from other sheet. Can you help please

  24. Thanks this is great! For some reason the search in reverse order is not working.
    Using your example my sheet has (=XLOOKUP(G1,B2:B9,D2:D9, ,-1)) still gives me $25 as the result, not $45.
    All the other examples seem to work great

    1. Hi Dave,

      Thank you for pointing out this issue!

      It looks like Microsoft has changed the order of arguments in the XLOOKUP function! At the moment of writing, this function was a beta available for Office Insiders. That time, search_mode that controls the direction of search was the 5th argument. Now, it's the last (6th) argument. I will review and update all the affected examples ASAP.

      In the meantime, please use this formula to get the last occurrence:
      =XLOOKUP(G1, B2:B9, D2:D9, , ,-1)

  25. I couldn't find xlookup in excel 2019

    1. Update: the XLOOKUP function is now available in the Monthly channel of Office 365 subscriptions. Starting in July 2020, it is planned to be included in the Semi-Annual channel.

    2. Hi Hamada,

      Currently XLOOKUP is only available to users of the Office 365 Insiders channel.

  26. Hi
    I am trying to work out this formula =IF(A2>0,"Non Compliance","Compliance")but i want to exclude blank cells. How do i do that?

    thanks
    Stephane

    1. Hi Stephane,
      You can nest another IF function that checks for blanks, like this:
      =IF(A2="","", IF(A2>0,"Non Compliance","Compliance"))

Post a comment



Thanks for your comment! Please note that all comments are pre-moderated, and off-topic ones may be deleted.
For faster help, please keep your question clear and concise. While we can't guarantee a reply to every question, we'll do our best to respond :)