SOLVED

Return a value based on MIN

%3CLINGO-SUB%20id%3D%22lingo-sub-2339210%22%20slang%3D%22en-US%22%3EReturn%20a%20value%20based%20on%20MIN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2339210%22%20slang%3D%22en-US%22%3E%3CP%3EI%20want%20to%20return%20a%20value%20located%20on%20column%20%22A%22%20that%20corresponds%20to%20a%20minimum%20value%20located%20in%20column%20%22F%22%20if%20it%20meets%20a%20criteria%20based%20on%20information%20contained%20in%20column%20%22G%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2339210%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-2339274%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20a%20value%20based%20on%20MIN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2339274%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1049205%22%20target%3D%22_blank%22%3E%40djungst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETry%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(A1%3AA5%2CMATCH(1%2CINDEX((F1%3AF5%3DMINIFS(F1%3AF5%2CG1%3AG5%2C%22RS%22))*(G1%3AG5%3D%22RS%22)%2C%2C)%2C0))%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2339308%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20a%20value%20based%20on%20MIN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2339308%22%20slang%3D%22en-US%22%3EHans%2C%20this%20worked%20perfectly.%20Thanks%20for%20your%20help.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2340028%22%20slang%3D%22en-US%22%3ERe%3A%20Return%20a%20value%20based%20on%20MIN%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2340028%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1049205%22%20target%3D%22_blank%22%3E%40djungst%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXLOOKUP(%22RS%22%2CSORTBY(G1%3AG5%2CF1%3AF5)%2CSORTBY(A1%3AA5%2CF1%3AF5))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

I want to return a value located on column "A" that corresponds to a minimum value located in column "F" if it meets a criteria based on information contained in column "G".

3 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@djungst 

Try

 

=INDEX(A1:A5,MATCH(1,INDEX((F1:F5=MINIFS(F1:F5,G1:G5,"RS"))*(G1:G5="RS"),,),0))

Hans, this worked perfectly. Thanks for your help.

@djungst 

As variant

=XLOOKUP("RS",SORTBY(G1:G5,F1:F5),SORTBY(A1:A5,F1:F5))