!!Hard Formula INDEX MATCH MIN to find earliest date return cell attribute.... HELP!!

Copper Contributor

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.