Trying to find max date in 1 column for selected text in another column

I have 2 columns: Locations (Column A) & Dates (Column B).
I want to find the most recent date for a selected Location (stored in $G$1).

currently I have in C2: =If($A2=$G$1,$B2,"") copied down the column to the end of data (currently C153). Then in C1: =Max(C$2:C$153).

I also have in D2: =If($C2<$C$1,$C2,"") copied down to D153, and in D1: =Max(D$2:D$153) to find the 2nd most recent date.

Is there some way to condense this into just 2 cells?

See attached file for example (note the columns are currently sorted by date, but that is not always the case).

Thanks, I guess I'll have to start using Array formulas. I've been avoiding learning about them till now. And the Large function is new to me, guess I'll have to go through the entire list of functions to see what else I don't know about.

I am curious, Is Max() more effecient that Large(). Wouldn't Large work in the 1st formula also?

Appreciate this is Solved but thought I would take the opportunity to add another alternative which is non-array but much less elegant... and so I can ascertain Dominic's opinion on the efficiency of the approach...

Latest date
=INDEX($B$2:$B$153,MATCH(TRUE,INDEX($A$2:$A$153=$G$1,0),0))

2nd from last date
=INDEX($B$2:$B$153,MATCH(1,INDEX(($A$2:$A$153=$G$1)*($B$2:$B$153<$I1),0),0))

Note: this is flawed if there are ever two meetings on the same day whereas LARGE CSE will handle this.


Dominic, would you say the above are hideously inefficient ? As you know I like to know your opinion. I picked up this method using INDEX with TRUE to avoid CSE the other day from reading one of daddylonglegs' posts and I confess that like it... I know that using the LOOKUP(2,1/....) is regarded as slow and given this approach is doing a similar thing albeit in a different direction I presume the same is true here ... ?

[C]trl [S]hift [E]nter - ie Array.

Originally Posted by foxguy

I believe they depend on the ranges being sorted in descending date order

Correct. In the sample file everything was listed in Desc order but if as you say if this is not always the case then I agree it's a null and void approach.

Dominic, if/when you have the time I would still like your opinion re: efficiency of this approach...

Cheers,
DO

Yes, it should be $C1 (I was testing in J1 - ie not overriding your results)

You say it still doesn't work - so I can test - what is G1 set to / and is this using the same sample ? Also, can you confirm which XL version you're running ? (your profile would indicate 2002)

Concatenate the two

=MATCH(TRUE,INDEX($H$2:$H$153&$I$2:$I$153=$G$1,0),0)

Per Dominic's note there's no gain using this approach over the standard Array approach other than not having to set the array ... and in fact performance may not be as good.

Originally Posted by DonkeyOte

Concatenate the two

=MATCH(TRUE,INDEX($H$2:$H$153&$I$2:$I$153=$G$1,0),0)

Per Dominic's note there's no gain using this approach over the standard Array approach other than not having to set the array ... and in fact performance may not be as good.

Actually, as per my post, this approach -- without concatenation -- is somewhat faster. However, as you've already indicated, the data needs to be sorted by date, in descending order. Surprisingly, though, when concatenation is used with this approach, Excel is brought to a virtual standstill.