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 3. Total comments: 172

  1. HI, I have a dataset with staff completing their inspections on specific dates for certain street addresses.
    Ex.
    Header rows:
    1)Area
    2) Segment
    3)Boundaries
    4)Approx. Blocks
    5)# Inspectors
    6) Bureau
    7)Team Lead
    8)Completed 12/7
    9)Completed 12/8
    10)Completed 12/9
    11)Completed 12/10
    12)Completed 12/11

    If I want to get the latest value from #8-#12 columns, what syntax can I use?

  2. 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)

  3. Hi Svetlana,
    Very complete but I am stuck with a more complex issue.
    I need to get a value based on 3 criteria, including one that is an approximate (i.e. lookup value would be within a range)
    My formula looks like this:
    =xlookup(Value01&Value02&Value03,Range01&Range02&Range03,ReturnRange,,-1) where value 03 is the approximate crlteria.

    The ranges are same lenghts and side by side. It should work however it gives me random return values, never consistent.

    Please help.

  4. I have multiple Xlookup's running in my workbook, but I'm having issues with a particular one. I have a column with pay type data (regular time, overtime) and another column where the job name is listed. I am attempting to use xlookup to determine which pay type AND if the job name is one of 3 possibilities (referencing a range of cells for that data) have it return a job type number listed in another range of cells. My current formula is =XLOOKUP($E2,AC2:AC5&($T1,AE2:AE5),AD2:AD5,E2)which returns "regular time" as the answer (which I only want it to do if it won't be replacing the data with information from the proper range). I have to be missing part of the query, but I'm not sure where.

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail?
      What do you want to determine using this formula? Pay type? Job type? What is written in cell E2? What is written in cell T1? You are using 3 ranges of AC2: AC5 AE2: AE5 AD2: AD5. What is written in them? Without this, I cannot understand the logic of your formula

  5. Is it possible to utilize an XLOOKUP in conjunction with a range? I'd like to return a value based on a range that varies depending on the lookup criteria.

    Example. Criteria one is a customer name, Criteria two is their sales, Result/Return is a order frequency. There is 5 possible return values of what the order frequency should be based on the range of sales and the customer name. There are different ranges based on customer names.

    1. Hello Robert!
      Read carefully the section above "XLOOKUP with multiple criteria" You did not explain what you mean by "order frequency".
      I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Give an example of the source data and the expected result.
      It’ll help me understand it better and find a solution for you. Thank you.

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

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



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)