+ Reply to Thread
Results 1 to 4 of 4

INDEX, MATCH with multiple criteria and return MIN value from another column

  1. #1
    Registered User
    Join Date
    06-15-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    3

    INDEX, MATCH with multiple criteria and return MIN value from another column

    I am trying to create a formula that will look between 2 different sheets with the same data sets and return a non-shared column and provide the max value within the specified criteria.

    For example:

    SHEET 1

    A B C D
    Name Location Zip Phone Number

    SHEET 2

    A B C D E
    Name Location Zip Phone Number Date

    What I would like is as follows:

    I am trying to create a formula that will compare columns A, B, C, D on SHEET 1 against the columns for A, B, C, D on SHEET 2. If it find a match for all 3 criterias, it will return the lowest value in Column E on SHEET 2, as there may be many instances of the same "Phone Number", but occured on a different date. I want to know what the first date the "call" occured on.

    Can someone please assist with this? I've picked my brain for 2 days and I'm lost.

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,038

    Re: INDEX, MATCH with multiple criteria and return MIN value from another column

    Try this

    =SUMPRODUCT(MIN((Sheet2!A1:A3=Sheet1!A9)*(Sheet2!B1:B3=Sheet1!B9)*(Sheet2!C1:C3=Sheet1!C9)*(Sheet2!D1:D3=Sheet1!D9)*((Sheet2!E1:E3))))

    I put 3 rows of the same ABCD values with column E reading 6, 15 and 9 and the above pulled out 6 which is correct.
    You'll need to put this formula against each row in Sheet 1
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Registered User
    Join Date
    06-15-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: INDEX, MATCH with multiple criteria and return MIN value from another column

    Thanks so much for your response; however I am still having issues. I've tried on a small scale and it worked perfectly.

    I created 2 sheets, 4x3 and verified that the smallest number was returned in column E on Sheet 1 (where the sumproduct formula was placed):

    =SUMPRODUCT(MIN((Sheet2!$A$1:$A$3=Sheet1!A1)*(Sheet2!$B$1:$B$3=Sheet1!B1)*(Sheet2!$C$1:$C$3=Sheet1!C1)*(Sheet2!$D$1:$D$3=Sheet1!D1)*((Sheet2!$E$1:$E$3)))) = ROW 1
    =SUMPRODUCT(MIN((Sheet2!$A$1:$A$3=Sheet1!A2)*(Sheet2!$B$1:$B$3=Sheet1!B2)*(Sheet2!$C$1:$C$3=Sheet1!C2)*(Sheet2!$D$1:$D$3=Sheet1!D2)*((Sheet2!$E$1:$E$3)))) = ROW 2
    etc.

    I then tried in large scale from my own spreadsheet (23 some thousand rows) and it did not work, even though it's the same concept. I verified with a COUNTIFS formula from Sheet 1 to Sheet 2 that there are in fact matches; however the formula returns zero and not a date.

    Here's the live version of what I am doing:

    =COUNTIFS(SvrCollection[Plugin Name],[@[Plugin Name]],SvrCollection[Plugin],[@Plugin],SvrCollection[Severity],[@Severity],SvrCollection[IP Address],[@[IP Address]]) = this returns the correct value of how many verified occurances there were between the two sheets, or matches.

    =SUMPRODUCT(MIN(('-Collection - SVR-'!$A$2:$A$23469='-SVR RAW-'!A2)*('-Collection - SVR-'!$B$2:$B$23469='-SVR RAW-'!B2)*('-Collection - SVR-'!$C$2:$C$23469='-SVR RAW-'!C2)*('-Collection - SVR-'!$D$2:$D$23469='-SVR RAW-'!D2)*(('-Collection - SVR-'!$E$2:$E$23469)))) = this is the modified formula that I used to pull the min date range from sheet 2 (or in this case, it would be the date from Column E on "Collection - SVR").

    Can you see what I might be doing wrong?

    I tried the Formula evaluation tool and it didn't provide a lot of insight into what I'm missing.

  4. #4
    Registered User
    Join Date
    06-15-2012
    Location
    Washington, DC
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: INDEX, MATCH with multiple criteria and return MIN value from another column

    I think I realized the problem. It works if both sheets completely match, which is not the case.

    Here's an example:

    A B C D E
    1 2 4 6 1
    3 2 4 5 6
    5 3 4 5 2
    5 3 4 5 6

    A B C D E
    3 2 4 5 = 6
    0 1 2 5 = 0
    5 3 4 5 = 2
    1 2 3 4 = 0

    Essentially, this spreadsheet is comparing artifacts for systems against a database with a list of all of the artifacts ever documented and the dates they were documented. What I want to do is do a match against 4 different sets of critera (Column A, B, C, D) to be proof-positive that the artifacts match but take the earliest date where it was identified so we can determine when the artifact was first observed.

    Hope this helps clarify it some!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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