match record field

[Gary]

I have a simple excel table where 5 users enter records on the same
spreadsheet throughout the week. In one of the fields (columns) the user
will enter his name to identify who the record belongs to, and in another
field is the date that record was entered. I end up with a table like the
following:

name entry_date
Joe 04/16/04
Bill 04/17/04
Joe 04/18/04
Bill 04/19/04
Joe 04/20/04
Ed 04/21/04

My problem: In another cell on another worksheet I would like to return the
latest entry date for a particular user as such:

Joe 04/20/04

I can't seem to use index(), match(), or max() functions to do the job. I
have used the formula array {=MAX((E2:E19=A4)*(F2:F19))} but I can't get
that to work for an entire column - only for a defined array.

Any suggestions?

Thanks,
Gary

 

[Gary]

Never mind. I think I figured it out using Dmax().

Thanks

 

[Irina]

Hi Gary,

I had the same problem and I was suggested (thanks people) to use nex
functions:

1. =ADDRESS(MATCH(MAX(B:B),B:B,FALSE),COLUMN(A:A))
2. =ADDRESS(MATCH(MAX(B:B),B:B,FALSE),COLUMN(b:b))

if you just add INDIRECT function it will work in your case:

1. =indirect(ADDRESS(MATCH(MAX(B:B),B:B,FALSE),COLUMN(A:A)))
2. =indirect(ADDRESS(MATCH(MAX(B:B),B:B,FALSE),COLUMN(b:b)))

If you set this formula in the same column from where you get data
just change the range.


Best regards, Irina.