Forum Discussion
!!Hard Formula INDEX MATCH MIN to find earliest date return cell attribute.... HELP!!
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.
Thanks, SergeiBaklan I'll give this a shot!!
