Select cell in range based on min in another

%3CLINGO-SUB%20id%3D%22lingo-sub-2842184%22%20slang%3D%22en-US%22%3ESelect%20cell%20in%20range%20based%20on%20min%20in%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2842184%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20range%20(r3%3At3).%20I%20want%20to%20select%20one%20of%20the%20cells%20in%20that%20range%20based%20on%20the%20minimum%20in%20another%20range%20(r11%3At11).%20r3%3At3%20is%20text%2C%20and%20r%3A11%3At11%20is%20numeric.%20Conditional%20formatting%20in%20r11%3At11%20gives%20me%20a%20red%20(255)%20on%20lowest%20number%20in%20the%20range%2C%20so%20I%20could%20select%20by%20MIN%20or%20interior.color.%20Which%20should%20I%20use%20and%20how%3F%20Thanks.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22cliffbrock_1-1634134848783.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F317076i7CDFC01C127AAC1C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22cliffbrock_1-1634134848783.png%22%20alt%3D%22cliffbrock_1-1634134848783.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2842184%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-2842724%22%20slang%3D%22en-US%22%3ERe%3A%20Select%20cell%20in%20range%20based%20on%20min%20in%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2842724%22%20slang%3D%22en-US%22%3Ethanks%2C%20hans.%20second%20time%20in%20a%20year%20i%20have%20not%20thought%20of%20index%2Fmatch.%20i%20am%20kicking%20myself.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2842312%22%20slang%3D%22en-US%22%3ERe%3A%20Select%20cell%20in%20range%20based%20on%20min%20in%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2842312%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F155636%22%20target%3D%22_blank%22%3E%40cliff%20brock%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DINDEX(R3%3AT3%2CMATCH(MIN(R11%3AT11)%2CR11%3AT11%2C0))%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

I have a range (r3:t3). I want to select one of the cells in that range based on the minimum in another range (r11:t11). r3:t3 is text, and r:11:t11 is numeric. Conditional formatting in r11:t11 gives me a red (255) on lowest number in the range, so I could select by MIN or interior.color. Which should I use and how? Thanks.

cliffbrock_1-1634134848783.png

 

2 Replies

@cliff brock 

=INDEX(R3:T3,MATCH(MIN(R11:T11),R11:T11,0))

thanks, hans. second time in a year i have not thought of index/match. i am kicking myself.