+ Reply to Thread
Results 1 to 7 of 7

Trouble trying to find last match with multiple criteria.

  1. #1
    Registered User
    Join Date
    05-19-2019
    Location
    America
    MS-Off Ver
    2019
    Posts
    13

    Trouble trying to find last match with multiple criteria.

    Hello,

    I am trying to find the last match value with multiple criteria. I have tried several formulas that I found online, but either had trouble getting them to work, or could not get them to work the way I wanted to.

    The value of the last match I need to find is the Contact Date (ContactT[Contact Date]).
    Criteria #1 is the Company Names must match.
    Criteria #2 is the Sample Status must match the "Delivered" status.
    The cell and column I am extracting the value to is on a separate table and separate page (LeadsT[Delivered]).

    Basically, I am trying to find the last "Delivered" Date for each Company.


    The current formula I have in the LeadsT[Delivered] column, is an Index/Match formula with multiple criteria. It works getting the first match, which I was satisfied with, prior to realizing that I needed the last match.
    Another is a Lookup formula. It works getting the first match with multiple criteria, but not the last match like I need.
    And another is two characters off from the second formula, which I did by mistake I think (it has one less set of parenthesis). It works getting the last match, but ignores the second criteria (Sample Status="Delivered").
    All other formulas I tried, I wound up with errors.

    The formulas are included in the attached workbook. For some reason, when I try to add them, it says I can't post any links without posting more.

    Thank you so much for any help, please let me know if I need to add anything else!
    Attached Files Attached Files

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Sadly... in lockdown, but based in Ireland.
    MS-Off Ver
    µSoft Office 365. Learning Excel all over again!!
    Posts
    29,873

    Re: Trouble trying to find last match with multiple criteria.

    Rather than telling us about a formula that doesn't work... please tell us what your expected answer is!!! What's the expected answer for Woodinville??
    Glenn



  3. #3
    Registered User
    Join Date
    05-19-2019
    Location
    America
    MS-Off Ver
    2019
    Posts
    13

    Re: Trouble trying to find last match with multiple criteria.

    Hi Glenn, thank you for responding. I'm sorry I didn't explain that well enough or if I made that too confusing.

    I am expecting to find the last match for the Company and "Delivered" date, therefore, for Store #3 (Woodinville), I want it to retrieve the date of "7/25/2019" (the latest Delivery date), and NOT "4/25/2019" (the first Delivery date).

    Thank you for taking the time to look at everything. Please let me know if there is anything else I should add or clear up. Thanks!

  4. #4
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    921

    Re: Trouble trying to find last match with multiple criteria.

    D16=IF(MAXIFS(ContactT[Contact Date],ContactT[Sample Status],LeadT[[#Headers],[Delivered]],ContactT[Company Name],[@[Company Name]])=0,"-",MAXIFS(ContactT[Contact Date],ContactT[Sample Status],LeadT[[#Headers],[Delivered]],ContactT[Company Name],[@[Company Name]]))

    Another way

    D16=IFERROR(LOOKUP(2,1/(ContactT[Company Name]=[@[Company Name]])/(ContactT[Sample Status]=LeadT[[#Headers],[Delivered]]),ContactT[Contact Date]),"-")

    copy down
    Last edited by CARACALLA; 09-17-2019 at 05:14 PM.

  5. #5
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    11,282

    Re: Trouble trying to find last match with multiple criteria.

    Please Login or Register  to view this content.
    Ben Van Johnson

  6. #6
    Registered User
    Join Date
    05-19-2019
    Location
    America
    MS-Off Ver
    2019
    Posts
    13

    Re: Trouble trying to find last match with multiple criteria.

    Caracalla, wow thank you so much! Those both definitely work. I really appreciate your help and taking the time out of your day to do so.

    ProtonLeah, thank you for also helping and doing the same!

    This forum is great Have a great day!

  7. #7
    Valued Forum Contributor
    Join Date
    09-25-2015
    Location
    Milan Italy
    MS-Off Ver
    office 365
    Posts
    921

    Re: Trouble trying to find last match with multiple criteria.

    You are welcome

    works also with aggregate

+ 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] Find the nth match, using multiple criteria and wildcards
    By timmtamm in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 05-01-2017, 05:21 PM
  2. [SOLVED] Find multiple results with INDEX & MATCH on 2 criteria
    By SubwAy in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 02-22-2017, 08:56 AM
  3. [SOLVED] Find/match multiple criteria and return value
    By Aaron092 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-01-2014, 07:22 AM
  4. Trouble with Index Match multiple criteria
    By Groovicles in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 06-30-2014, 01:39 AM
  5. Match Multiple Criteria & Find Oldest Date
    By DoodlesMama in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 12-04-2013, 11:10 AM
  6. Find records that match criteria of multiple fields
    By jvbeats in forum Access Tables & Databases
    Replies: 3
    Last Post: 03-12-2013, 05:40 PM
  7. Replies: 3
    Last Post: 07-11-2009, 02:58 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