"Fred Smith" wrote:

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" wrote in message
...


"Fred Smith" wrote:

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" wrote in message
...


"Mike H" wrote:

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" wrote:

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

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

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