200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Hi, I would like some help here. If you see the attached file, we have color and date, When I filter a color, I need to get the oldest date in the empty marked cell... I get the oldest date by using =small(K5:K21), But once I sort it, it gives me the same result which is the oldest of the entire list.
Thank you SO much in advace!
This needs an array formula. It must be entered using Ctl+Shift+Enter instead of the customary singular Enter.
=SMALL(IF(J5:J21="Pink",K5:K21),1)When entered correctly Excel will display it as {=SMALL(IF(J5:J21="Pink",K5:K21),1)} in the formula bar, with curcly braces added.
As you see, the formula repeats the filter. Therefore it deosn't need the filter to be applied in order to work. The filter criterium, "Pink" in the above example, and the rank, "1" in the example, can be supplied in the shape of cell references. The same syntax will also work with the LARGE() function.