Excel find data based upon min date in range

MrDavidThorn
MrDavidThorn used Ask the Experts™
on
Hi Experts

I have a range of data, I want to return the Id from a Name based upon the minimum date, in my example I would like to return an Id of 1 for the Name terry, as 01/01/2010 is the miinmum date in the range.

Thanks
Example.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2011
Awarded 2010

Commented:
Hello,

try

=INDEX(A:A,MATCH(MIN(B:B),B:B,0))

cheers, teylyn

Author

Commented:
On the right lines, but I want to supply the name - i.e Peter and then the formula to return 9 - the function supplied just returns the name - the formula returns 9 becasuse is the minimum date not the beacuase its the first match in the list
Most Valuable Expert 2011
Awarded 2010

Commented:
To return the ID just change the Index to the ID column, as in

=INDEX(C:C,MATCH(MIN(B:B),B:B,0))

In the supplied example, the smallest date is 1/01/2010, so the ID will be 1, not 9

If that's not what you're after, please provide more details.


cheers, teylyn
Most Valuable Expert 2011
Awarded 2010
Commented:
That's a bit different from your original question.

With the name "Peter" in cell E3:

Try this array formula, which needs to be confirmed with CTRL-Shift-Enter

=MIN(IF(A1:A26=E3,C1:C26))

Again, it is an array formula. Don't just hit Enter. Copy or type the formula into the formula bar and then hold down the Ctrl and Shift key while pressing the Enter key,

see attached


cheers, teylyn
Example--2-.xlsx

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial