Forum Discussion
!!Hard Formula INDEX MATCH MIN to find earliest date return cell attribute.... HELP!!
I received some help earlier, but the formula I need is getting slightly more complicated and I've spent 2 days and.... still no luck.
I need to query a sheet for multiple AND/OR criteria. What I need to do is find all rows with the following criteria:
Column A - Diamond, or Heart, or Spade
AND
Column G - Next, or Now, or Soon
AND
Column K - Team A
AND
Column B - Earliest possible date one criteria above have been matched
THEN
Return the code attribute from Column I to cell N4
Here's the formula I used and after manipulating this formula many ways, the best I was able to do was return the first match on the sheet:
=INDEX(I2:I30,MATCH(1,INDEX(((A2:A30="Diamond")+(A2:A30="Heart")+(A2:A30="Spade"))*((G2:G30="Next")+(G2:G30="Now")+(G2:G30="Soon"))*(MIN(B2:B30)=B2:B30),0),0),0)
In the attached spreadsheet example, the result I'm looking for would be:
Row 11 - return I20 to cell N4
Row 11 has a result of Spade, Now, TeamA and the earliest date of 01/13/2019
This is racking my brain hard... :-)
4 Replies
Hi,
It doesn't work since there is no match - minimum date is in row 2 (Jan 12, 2019). If change the date here on later one, you'll have i20 in N4
Trying to figure out how so? I want to sift through all the rows to get a match on the results and then of the results that provide True (1) - to rows, then take the earliest date of that sub-sample.
So, the results of the AND/OR matches per sub-set of rows would be:
| A | B | C | D | E | F | G | H | I | J | K |
6 | Diamond | 02/2/19 | c105 | -- | -- | -- | Next | -- | i15 | j15 | TeamA |
11| Spade | 01/13/19 | c110 | -- | -- | -- | Now | -- | i20 | j20 | TeamA |
15| Diamond | 01/16/19 | c114 | -- | -- | -- | Next | -- | i24 | j24 | TeamA |
20| Heart | 01/18/19 | c119 | -- | -- | -- | Soon | -- | i29 | j29 | TeamA |
25| Diamond | 02/04/19 | c124 | -- | -- | -- | Next | -- | i34 | j34 | TeamA |
29| Spade | 02/08/19 | c128 | -- | -- | -- | Now | -- | i38 | j38 | TeamA |
from the sub-set above, I'd want to get the MIN (or earliest date), which should be 01/13/2019
The Return result from (i11) to (N4) I'm looking for from this subset
Okay, I see. When it could be
=INDEX($I$2:$I$30,MATCH(AGGREGATE(15,6,1/(($A$2:$A$30="Diamond")+($A$2:$A$30="Heart")+($A$2:$A$30="Spade")) /(($G$2:$G$30="Next")+($G$2:$G$30="Now")+($G$2:$G$30="Soon"))*$B$2:$B$30,1),$B$2:$B$30,0))
With AGGREGATE you find smallest date on filtered subset, find it's position in the column with MATCH and return the value from that position in column I by INDEX.
Please see attached.
