Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-331861%22%20slang%3D%22en-US%22%3E!!Hard%20Formula%20INDEX%20MATCH%20MIN%20to%20find%20earliest%20date%20return%20cell%20attribute....%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-331861%22%20slang%3D%22en-US%22%3E%3CP%3EI%20received%20some%20help%20earlier%2C%20but%20the%20formula%20I%20need%20is%20getting%20slightly%20more%20complicated%20and%20I've%20spent%202%20days%20and....%20still%20no%20luck.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20query%20a%20sheet%20for%20multiple%20AND%2FOR%20criteria.%26nbsp%3B%20What%20I%20need%20to%20do%20is%20find%20all%20rows%20with%20the%20following%20criteria%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A%20-%20Diamond%2C%20%3CEM%3E%3CSTRONG%3Eor%3C%2FSTRONG%3E%3C%2FEM%3EHeart%2C%20%3CEM%3E%3CSTRONG%3Eor%3C%2FSTRONG%3E%3C%2FEM%3ESpade%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3CSTRONG%3EAND%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EColumn%20G%20-%20Next%2C%20%3CEM%3E%3CSTRONG%3Eor%3C%2FSTRONG%3E%3C%2FEM%3ENow%2C%20%3CEM%3E%3CSTRONG%3Eor%3C%2FSTRONG%3E%3C%2FEM%3ESoon%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3CSTRONG%3EAND%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EColumn%20K%20-%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Team%20A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3CSTRONG%3EAND%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EColumn%20B%20-%20Earliest%20possible%20date%20one%20criteria%20above%20have%20been%20matched%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%3CSTRONG%3ETHEN%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3EReturn%20the%20code%20attribute%20from%20Column%20I%20to%20cell%20N4%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20formula%20I%20used%20and%20after%20manipulating%20this%20formula%20many%20ways%2C%20the%20best%20I%20was%20able%20to%20do%20was%20return%20the%20first%20match%20on%20the%20sheet%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3E%3DINDEX(I2%3AI30%2CMATCH(1%2CINDEX(((A2%3AA30%3D%22Diamond%22)%2B(A2%3AA30%3D%22Heart%22)%2B(A2%3AA30%3D%22Spade%22))*((G2%3AG30%3D%22Next%22)%2B(G2%3AG30%3D%22Now%22)%2B(G2%3AG30%3D%22Soon%22))*(MIN(B2%3AB30)%3DB2%3AB30)%2C0)%2C0)%2C0)%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20spreadsheet%20example%2C%20the%20result%20I'm%20looking%20for%20would%20be%3A%3C%2FP%3E%3CP%3E%3CSTRONG%3ERow%2011%20-%20return%20I20%20to%20cell%20N4%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3ERow%2011%20has%20a%20result%20of%20Spade%2C%20Now%2C%20TeamA%20and%20the%20earliest%20date%20of%2001%2F13%2F2019%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20racking%20my%20brain%20hard...%20%3A-)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-331861%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332335%22%20slang%3D%22en-US%22%3ERe%3A%20!!Hard%20Formula%20Index%20Match%20Multiple%20AND%20and%20OR%20criteria%20and%20find%20MIN%20date....%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332335%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%2C%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BI'll%20give%20this%20a%20shot!!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332227%22%20slang%3D%22en-US%22%3ERe%3A%20!!Hard%20Formula%20Index%20Match%20Multiple%20AND%20and%20OR%20criteria%20and%20find%20MIN%20date....%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332227%22%20slang%3D%22en-US%22%3E%3CP%3EOkay%2C%20I%26nbsp%3B%20see.%20When%20it%20could%20be%3C%2FP%3E%0A%3CPRE%3E%3DINDEX(%24I%242%3A%24I%2430%2CMATCH(AGGREGATE(15%2C6%2C1%2F((%24A%242%3A%24A%2430%3D%22Diamond%22)%2B(%24A%242%3A%24A%2430%3D%22Heart%22)%2B(%24A%242%3A%24A%2430%3D%22Spade%22))%0A%2F((%24G%242%3A%24G%2430%3D%22Next%22)%2B(%24G%242%3A%24G%2430%3D%22Now%22)%2B(%24G%242%3A%24G%2430%3D%22Soon%22))*%24B%242%3A%24B%2430%2C1)%2C%24B%242%3A%24B%2430%2C0))%3C%2FPRE%3E%0A%3CP%3EWith%20AGGREGATE%20you%20find%20smallest%20date%20on%20filtered%20subset%2C%20find%20it's%20position%20in%20the%20column%20with%20MATCH%20and%20return%20the%20value%20from%20that%20position%20in%20column%20I%20by%20INDEX.%3C%2FP%3E%0A%3CP%3EPlease%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-332083%22%20slang%3D%22en-US%22%3ERe%3A%20!!Hard%20Formula%20Index%20Match%20Multiple%20AND%20and%20OR%20criteria%20and%20find%20MIN%20date....%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-332083%22%20slang%3D%22en-US%22%3E%3CP%3ETrying%20to%20figure%20out%20how%20so%3F%20I%20want%20to%20sift%20through%20all%20the%20rows%20to%20get%20a%20match%20on%20the%20results%20and%20then%20of%20the%20results%20that%20provide%20True%20(1)%20-%20to%20rows%2C%20then%20take%20the%20earliest%20date%20of%20that%20sub-sample.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%2C%20the%20results%20of%20the%20AND%2FOR%20matches%20per%20sub-set%20of%20rows%20would%20be%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BA%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BB%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20D%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3BE%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3BF%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20%26nbsp%3B%20G%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20H%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3B%20I%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20%26nbsp%3B%20J%26nbsp%3B%20%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20K%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%7C%3C%2FP%3E%3CP%3E6%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3BDiamond%26nbsp%3B%20%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3B%3CEM%3E%3CSTRONG%3E02%2F2%2F19%3C%2FSTRONG%3E%3C%2FEM%3E%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3Bc105%26nbsp%3B%20%7C%26nbsp%3B%20--%20%7C%26nbsp%3B%20--%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3B--%26nbsp%3B%20%7C%26nbsp%3B%20Next%26nbsp%3B%20%7C%26nbsp%3B%20--%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20i15%20%7C%26nbsp%3B%20%26nbsp%3Bj15%26nbsp%3B%20%7C%20TeamA%26nbsp%3B%20%7C%3C%2FP%3E%3CP%3E11%7C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSpade%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%7C%26nbsp%3B%20%3CEM%3E%3CSTRONG%3E01%2F13%2F19%3C%2FSTRONG%3E%3C%2FEM%3E%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3Bc110%26nbsp%3B%20%7C%26nbsp%3B%20--%20%7C%26nbsp%3B%20--%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3B--%26nbsp%3B%20%7C%26nbsp%3B%20Now%26nbsp%3B%20%7C%26nbsp%3B%20--%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20i20%20%7C%26nbsp%3B%20%26nbsp%3Bj20%26nbsp%3B%20%7C%20TeamA%26nbsp%3B%20%7C%3C%2FP%3E%3CP%3E15%7C%26nbsp%3B%20%26nbsp%3BDiamond%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20%3CEM%3E%3CSTRONG%3E01%2F16%2F19%3C%2FSTRONG%3E%3C%2FEM%3E%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3Bc114%26nbsp%3B%20%7C%26nbsp%3B%20--%20%7C%26nbsp%3B%20--%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3B--%26nbsp%3B%20%7C%26nbsp%3B%20Next%26nbsp%3B%20%7C%26nbsp%3B%20--%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20i24%20%7C%26nbsp%3B%20%26nbsp%3Bj24%26nbsp%3B%20%7C%20TeamA%26nbsp%3B%20%7C%3C%2FP%3E%3CP%3E20%7C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Heart%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%7C%26nbsp%3B%20%3CEM%3E%3CSTRONG%3E01%2F18%2F19%3C%2FSTRONG%3E%3C%2FEM%3E%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3Bc119%26nbsp%3B%20%7C%26nbsp%3B%20--%20%7C%26nbsp%3B%20--%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3B--%26nbsp%3B%20%7C%26nbsp%3B%20Soon%20%7C%26nbsp%3B%20--%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20i29%20%7C%26nbsp%3B%20%26nbsp%3Bj29%26nbsp%3B%20%7C%20TeamA%26nbsp%3B%20%7C%3C%2FP%3E%3CP%3E25%7C%26nbsp%3B%20%26nbsp%3B%20Diamond%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20%3CEM%3E%3CSTRONG%3E02%2F04%2F19%3C%2FSTRONG%3E%3C%2FEM%3E%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3Bc124%20%7C%26nbsp%3B%20--%20%7C%26nbsp%3B%20--%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3B--%26nbsp%3B%20%7C%26nbsp%3B%20Next%26nbsp%3B%20%7C%26nbsp%3B%20--%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20i34%20%7C%26nbsp%3B%20%26nbsp%3Bj34%26nbsp%3B%20%7C%20TeamA%26nbsp%3B%20%7C%3C%2FP%3E%3CP%3E29%7C%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BSpade%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%7C%26nbsp%3B%20%3CEM%3E%3CSTRONG%3E02%2F08%2F19%3C%2FSTRONG%3E%3C%2FEM%3E%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3Bc128%26nbsp%3B%20%7C%26nbsp%3B%20--%20%7C%26nbsp%3B%20--%26nbsp%3B%20%7C%26nbsp%3B%20%26nbsp%3B--%26nbsp%3B%20%7C%26nbsp%3B%20Now%26nbsp%3B%20%7C%26nbsp%3B%20--%26nbsp%3B%20%26nbsp%3B%7C%26nbsp%3B%20i38%20%7C%26nbsp%3B%20%26nbsp%3Bj38%26nbsp%3B%20%7C%20TeamA%26nbsp%3B%20%7C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Efrom%20the%20sub-set%20above%2C%20I'd%20want%20to%20get%20the%20MIN%20(or%20earliest%20date)%2C%20which%20should%20be%2001%2F13%2F2019%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20Return%20result%20from%20(i11)%20to%20(N4)%20I'm%20looking%20for%20from%20this%20subset%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-331926%22%20slang%3D%22en-US%22%3ERe%3A%20!!Hard%20Formula%20Index%20Match%20Multiple%20AND%20and%20OR%20criteria%20and%20find%20MIN%20date....%20HELP!!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-331926%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIt%20doesn't%20work%20since%20there%20is%20no%20match%20-%20minimum%20date%20is%20in%20row%202%20(Jan%2012%2C%202019).%20If%20change%20the%20date%20here%20on%20later%20one%2C%20you'll%20have%20i20%20in%20N4%3C%2FP%3E%3C%2FLINGO-BODY%3E
jkoors
Occasional 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.

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies