SOLVED

Simple formula for search the min date with criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-2630723%22%20slang%3D%22en-US%22%3ESimple%20formula%20for%20search%20the%20min%20date%20with%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2630723%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%20the%20screenshot%20are%20the%20sample%20of%20raw%20data%20and%20result.%26nbsp%3B%20I%20would%20like%20to%20return%20the%20earliest%20date%20from%20the%20category%20of%20%22pull%2Cpush%2Cpull-M%2Cpush-M%22%20with%20formula%20at%20K%20column.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22simple%20formula.PNG%22%20style%3D%22width%3A%20866px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F302098iAA744BF32492C412%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22simple%20formula.PNG%22%20alt%3D%22simple%20formula.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2630723%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-2630872%22%20slang%3D%22en-US%22%3ERe%3A%20Simple%20formula%20for%20search%20the%20min%20date%20with%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2630872%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1084064%22%20target%3D%22_blank%22%3E%40MeiChan1314%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20Excel%202019%20or%20Excel%20in%20Microsoft%20365%2C%20enter%20the%20following%20formula%20in%20K111%3A%3C%2FP%3E%0A%3CP%3E%3DMINIFS(%24G%24111%3A%24G%24500%2C%20%24F%24111%3A%24F%24500%2C%20J111%2C%20%24H%24111%3A%24H500%2C%20%22pu*%22)%3C%2FP%3E%0A%3CP%3EAdjust%20the%20ranges%20if%20your%20data%20extend%20below%20row%20500%2C%20then%20fill%20down.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20an%20older%20version%20of%20Excel%2C%20enter%20the%20following%20formula%20in%20K111%20and%20confirm%20it%20with%20Ctrl%2BShift%2BEnter%20to%20turn%20it%20into%20an%20array%20formula%3A%3C%2FP%3E%0A%3CP%3E%3DMIN(IF((%24F%24111%3A%24F%24500%3DJ111)*(LEFT(%24H%24111%3A%24H500%2C%202)%3D%22pu%22)%2C%20%24G%24111%3A%24G%24500))%3C%2FP%3E%0A%3CP%3Eand%20fill%20down.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2631762%22%20slang%3D%22en-US%22%3ERe%3A%20Simple%20formula%20for%20search%20the%20min%20date%20with%20criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2631762%22%20slang%3D%22en-US%22%3EThanks%20for%20the%20help%20on%20it.%20My%20excel%20is%20newer%20version%2C%20with%20Minifs%20will%20return%200-Jan-00%20if%20not%20found%20or%20match%2C%20I%20would%20like%20to%20have%20show%20blank%20or%20empty%20if%20not%20found.%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi All, the screenshot are the sample of raw data and result.  I would like to return the earliest date from the category of "pull,push,pull-M,push-M" with formula at K column. 

 

simple formula.PNG

3 Replies

@MeiChan1314 

If you have Excel 2019 or Excel in Microsoft 365, enter the following formula in K111:

=MINIFS($G$111:$G$500, $F$111:$F$500, J111, $H$111:$H500, "pu*")

Adjust the ranges if your data extend below row 500, then fill down.

 

If you have an older version of Excel, enter the following formula in K111 and confirm it with Ctrl+Shift+Enter to turn it into an array formula:

=MIN(IF(($F$111:$F$500=J111)*(LEFT($H$111:$H500, 2)="pu"), $G$111:$G$500))

and fill down.

Thanks for the help on it. My excel is newer version, with Minifs will return 0-Jan-00 if not found or match, I would like to have show blank or empty if not found.
best response confirmed by MeiChan1314 (Occasional Contributor)
Solution

@MeiChan1314 

Use

 

=IF(MINIFS($G$111:$G$500, $F$111:$F$500, J111, $H$111:$H500, "pu*")=0,"",MINIFS($G$111:$G$500, $F$111:$F$500, J111, $H$111:$H500, "pu*"))