• 469K Members
• 4,755 Online
• 568K Conversations

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

Highlighted
Occasional Contributor

# !!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:

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

# Re: !!Hard Formula Index Match Multiple AND and OR criteria and find MIN date.... 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

# Re: !!Hard Formula Index Match Multiple AND and OR criteria and find MIN date.... HELP!!

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

# Re: !!Hard Formula Index Match Multiple AND and OR criteria and find MIN date.... HELP!!

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.

# Re: !!Hard Formula Index Match Multiple AND and OR criteria and find MIN date.... HELP!!

Thanks, @Sergei Baklan I'll give this a shot!!

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
30 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies