3

Column A is date

Column B is criteria

I want to find the MIN date for each criteria. I tried using Ctrl+Shift+Enter with

=MIN(MATCH(B2,B:B,0))

but thats not quite right because I need to refer to Column A somehow to get the date. I'm pretty confident this can be done with arrays, so any help would be great.

Ram
  • 3,092
  • 10
  • 40
  • 56
jlarkins
  • 79
  • 1
  • 1
  • 7
  • Possible duplicate of [Excel: Find min/max values in a column among those matched from another column](http://stackoverflow.com/questions/20904092/excel-find-min-max-values-in-a-column-among-those-matched-from-another-column) – sancho.s ReinstateMonicaCellio Feb 04 '17 at 11:56

3 Answers3

11

Try this (array formula):

=MIN(IF(B2=B:B,A:A))
andy holaday
  • 2,282
  • 1
  • 17
  • 25
  • 1
    I ended up using vlookup, but I will try this next time I have to run this data report, probably this week. – jlarkins May 29 '12 at 14:40
  • 3
    For anyone reading this, note that it can take 30 to 60 minutes to calculate this on a spreadsheet with 50K rows. Obviously a database would be a more efficient, long term solution. – jlarkins Jun 12 '12 at 22:31
  • Hmm, you are right. The formula approach is too slow with that volume of data. This would be very easy to do with a pivot table. Well worth a few extra minutes to learn the process. – andy holaday Jun 13 '12 at 20:51
  • I want to do it in small range... but I am getting an error #value! – Shravya Boggarapu Apr 12 '16 at 10:43
  • @ShravyaBoggarapu make sure you are committing the formula as an array by pressing ctrl+shift+enter (not just enter). – andy holaday Apr 16 '16 at 00:28
  • Yea, thank you.. I had pressed Ctrl+shift+enter but I may have not done it right cause when I re-tried a few times, it worked. – Shravya Boggarapu Apr 19 '16 at 06:35
1

An even more compact array formula is:

=MINIF(B2=B:B,A:A)

NOTE 1: Complete using Ctrl+Shift+Enter to enter the formula as an array formula.

NOTE 2: The two-formula method (i.e., using =MIN(IF(B2=B:B,A:A))) is more flexible and works in more cases than the single-formula method shown here but I've included it as an answer as a possible option.

Andreas Toth
  • 13
  • 4
1
=SMALL(INDEX(($F$2:$F$14=F3)*$D$2:$D$14,),SUM(COUNTA(F:F)-COUNTIF(F:F,F3)))

If your criteria is repeated and want to find the min date for that you can use this without shift+ctrl + enter function.

  • date is D column
  • criteria is F column
Samuel Hulla
  • 6,617
  • 7
  • 36
  • 70
Pradeep Kumar K
  • 11
  • 1