# Bringing to a cell the last date an event occurred

X

#### 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.

M

#### 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

X

#### XLFanatico

Mike H said:
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

F

#### 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.

X

#### XLFanatico

Fred Smith said:
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.

F

#### 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.

X

#### XLFanatico

Fred Smith said:
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)