Bringing to a cell the last date an event occurred

[XLFanatico]

This is a long worksheet and need a formula, but don't know if possible.
A COLUMN B COLUMN C COLUMN
Doe, John Jan 05 a
Doe, Jane Jan 16 a
Presley, Elvis Jan 22
Doe, Jane Jan 30 a

All the previous data will be in SHEET 1
Now, in SHEET 2 :
COLUMN A = all possible names in COLUMN A SHEET 1,
written by me.
The formula will be used in COLUMN B, SHEET 2 and will tell me
which was the last date for example DOE,JANE had a letter "a"
(Jan 30 should be the result of the formula)
I'm thinking in VLOOKUP formula but don't know how to
organize it.
Thanks.

 

[Mike H]

Hi,

This is the 'ARRAY' formula if on the same sheet

=MAX(IF($A$1:$A$8="John Doe",IF($C$1:$C$8="a",$B$1:$B$8)))

and on another sheet with your list of names in Col A the 'ARRAY' formula

=MAX(IF(Sheet1!$A$1:$A$8=A1,IF(Sheet1!$C$1:$C$8="a",Sheet1!$B$1:$B$8)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

 

[XLFanatico]

Hi,

This is the 'ARRAY' formula if on the same sheet

=MAX(IF($A$1:$A$8="John Doe",IF($C$1:$C$8="a",$B$1:$B$8)))

and on another sheet with your list of names in Col A the 'ARRAY' formula

=MAX(IF(Sheet1!$A$1:$A$8=A1,IF(Sheet1!$C$1:$C$8="a",Sheet1!$B$1:$B$8)))

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike

Thanks for your interest and response MIKE.
The provided formula gives me a number "0".
And I really need as an answer is a DATE.
A DATE where the last "a" was found on DOE, JANE.
Thanks

 

[Fred Smith]

Did you enter it as an array formula? You can verify this when the formula
has braces {} around it in the formula bar.

Regards,
Fred.

 

[XLFanatico]

Did you enter it as an array formula? You can verify this when the formula
has braces {} around it in the formula bar.

Regards,
Fred.



.
Yes, I did enter as array.

The result still a number instead of a date.
Does it make a difference if I'm using XL 2007 ?.
Sorry for the lack of information in my part.
Thanks.

 

[Fred Smith]

First, you said the result was zero. Now you say it's "a number instead of a
date". Presumably this "number" is non-zero?

If so, and you prefer a date rather than the number, then format the cell as
a date, with your preference of format.

It doesn't make any difference if you're using Excel 2007. For future posts,
however, specify that up front, because it can make a difference to the
appropriate recommendations.

Regards,
Fred.

 

[XLFanatico]

First, you said the result was zero. Now you say it's "a number instead of a
date". Presumably this "number" is non-zero?

If so, and you prefer a date rather than the number, then format the cell as
a date, with your preference of format.

It doesn't make any difference if you're using Excel 2007. For future posts,
however, specify that up front, because it can make a difference to the
appropriate recommendations.

Regards,
Fred.



.
DO NOT PRESUME about zero, I said a number and the last time I

check zero was a number.
I however need a date not a number or zero.
(cells are format as date already)