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

1. ## 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.  Register To Reply

2. ## 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  Register To Reply

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.  Register To Reply

4. ## 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!  Register To Reply

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