+ Reply to Thread
Results 1 to 2 of 2

Second to last exact match using xlookup

  1. #1
    Registered User
    Join Date
    11-24-2020
    Location
    Lithuania
    MS-Off Ver
    365
    Posts
    1

    Second to last exact match using xlookup

    Hi,

    I am using xlookup to find the last occurrence when two criteria match (exact match) in the table but also need to find second to last occurrence as the results are dates and I need to see how much time passed between them. Formula takes info from another file that I cannot add helper column to. Does anyone know how to do that?
    It is a peace of cake to find the last match this if the formula:
    =XLOOKUP(1;(C11='X:\EXCEL\[2019.xls]Sales'!$O$1:$O$65536)*(D11='X:\EXCEL\[2019.xls]Sales'!$E$1:$E$65536);'X:\EXCEL\[2019.xls]Sales'!$P$1:$P$65536;;0;-1)

    But I cant figure out what I could add to retrieve second to last match.

    Your help is appreciated.

  2. #2
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Second to last exact match using xlookup

    Hi and welcome to the forum

    Please upload a workbook or a representative cut down copy, anonymised if necessary. It is always easier to advise if we can see your request in its context.
    Also upload the file being linked to - it may be needed.

    Show a before and after situation with manually calculated results, explaining which information is data and which is results, and if it's not blindingly obvious how you have arrived at your results some explanatory notes as well.

    To upload a file click the Go Advanced button at the foot of your post, look underneath the post area for the Manage Attachments section and take it from there.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using IF, Xlookup and Match/search, to find value in array.
    By Lee_of_Excel in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-16-2020, 12:09 AM
  2. Replies: 14
    Last Post: 09-28-2019, 10:32 PM
  3. Replies: 6
    Last Post: 05-22-2019, 11:29 PM
  4. [SOLVED] Index/Match with 2 match columns, non-exact match
    By c1t in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-25-2019, 08:23 AM
  5. To Sort exact and partial exact match for a single column.
    By Jagdev in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-22-2014, 05:08 AM
  6. Replies: 3
    Last Post: 06-17-2013, 12:37 PM
  7. [SOLVED] Index & Match returning incorrect value. Arrays fixed and exact match used.
    By SDes in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 09-07-2012, 08:29 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1