Can vlookup return highest cell value of in the relation to Lookup Value?

I have a spreadsheet of data that includes the following:


S# Record # SO # Line # SIZE DATE
11A035 1 5703 1 12M 03/02/13
11A035 1 6177 1 12M 04/20/13
11A036 2 5804 1 12M 03/18/13
11A036 2 6295 1 NB 05/20/13
11A040 6 5804 35 12M 03/18/13
11A040 6 6295 6 NB 05/20/13
09A288 522 7037 7 18M 04/29/13
58A471 1143 5929 93 5T 03/29/13
58A471 1143 7167 20 2T 04/26/13

What I am trying to accomplish is to return the SO# with the earliest date based on the S#. I am trying to write a VLookup function that has the S# as the Lookup value and returns the SO# of the earliest date record.

For instance, my formula should return 5703 for S#11A035 and 5929 for S# 58A471.

I tried using a VLookup + MIN function but that didn't work out for me. Should I use a Pivot table? Any help would be greatly appreciated.

Originally Posted by Melvinrobb

I added a helper column which adds a ranking based on the date.
The vlookup can then work.

Thank you for your help but this spreadsheet has 26740 rows, I don't know if I could efficiently rank all the various dates. This was just a very small subset. I will try more though.

Any one else? Thanks

Originally Posted by martindwilson

with that info in a1:f10 this array formula should do it
=INDEX($C$2:$C$10,MATCH(H2&MIN(IF($A$2:$A$10=H2,$F$2:$F$10)),INDEX($A$2:$A$10&$F$2:$F$10,0),0))
or you could split that over 2 cells see attached

The split into 2 cells looks to be working. Let me work with it more. Thanks guys.

Are the dates always in ascending order (relative to each S#) ?
So the earliest date is always the first date for each S# ?
It appears so in your sample dataset

In this case, a plain old exact match VLOOKUP will work

=VLOOKUP(S#,A:C,3,0)

Originally Posted by Jonmo1

Are the dates always in ascending order (relative to each S#) ?
So the earliest date is always the first date for each S# ?
It appears so in your sample dataset

In this case, a plain old exact match VLOOKUP will work

=VLOOKUP(S#,A:C,3,0)

No, the earliest date is not always the first.

Originally Posted by martindwilson

with that info in a1:f10 this array formula should do it
=INDEX($C$2:$C$10,MATCH(H2&MIN(IF($A$2:$A$10=H2,$F$2:$F$10)),INDEX($A$2:$A$10&$F$2:$F$10,0),0))
or you could split that over 2 cells see attached

YEP! the split works! Thanks guys.