Since the formula will be copied down, I think you need to make some of the
references absolute...

=MAX(IF(ISNUMBER(SEARCH(A1,Sheet2!D$1:D$6)),Sheet2 !A$1:A$6+Sheet2!C$1:C$6))

To the OP... this is still array-entered**

**Commit using Ctrl+Shift+Enter, not just Enter by itself.

--
Rick (MVP - Excel)


"T. Valko" wrote in message
...
Try this...

Array entered** :

=MAX(IF(ISNUMBER(SEARCH(A1,Sheet2!D1:D6)),Sheet2!A 1:A6+Sheet2!C1:C6))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Format as m/d/yyyy h:mm AM/PM

--
Biff
Microsoft Excel MVP


"tech1NJ" wrote in message
...
Here is a better example of the table...

ColumnA ColumnB Column C ColumnD
1/8/2006 - 2:29 PM rdupree Authorized Testwireless or Upgrade
1/10/2006 - 5:19 AM rdupree Authorized Centron 48 input Node
1/11/2006 - 10:55 AM pcaruso Authorized Centron 128 input Node
1/11/2006 - 11:39 AM jose Authorized Testwireless or Upgrade
1/11/2006 - 11:44 AM debby Authorized Software Opt:
1/11/2006 - 11:47 AM debby Authorized Software Opt:
--
tech1NJ


"Rick Rothstein" wrote:

Is the date and time on Sheet2 both in Column A or have you merged
Columns A
and B somehow? If it is in Column A, is it a real Excel date which has
been
formatted to look like you showed (the dash is non-standard)? It is a
little
hard to tell... I'm assuming the names on Sheet2 are in Column C, right?
Does your data start on Row 1 on both sheets or do you have a header on
Row
1 and the data starts on Row 2?

--
Rick (MVP - Excel)


"tech1NJ" wrote in message
...
I have 2 sheets setup. Sheet 1 has name listed in column A and Sheet 2
has
data as listed in the example below. I need help with extracting the
latest
date and time from sheet2 based on the name listed on sheet1. The
problem
that I have is that Sheet2 Column D has more text than just the name.
In
this
example I like to retrive the latest date and time for rdupree and
place
it
into Sheet1 Column C in the same row as rdupree is. I currently am
using
the
following formula but it only works if Sheet2 Column D has only just
the
name. It does not work if the extra text is in the cell. One more
thing. I
am
not allowed to remove the extra text from Column D in Sheet2.

Example1 - names listed in Sheet1 Column A
rdupree
jose
debby
pcaruso

Example2 - type of data on Sheet 2
Column A ColumnC ColumnD
1/8/2006 - 2:29 PM rdupree Authorized Testwireless or Upgrade
1/10/2006 - 5:19 AM rdupree Authorized Centron 48 input Node
1/11/2006 - 10:55 AM pcaruso Authorized Centron 128 input Node
1/11/2006 - 11:39 AM jose Authorized Testwireless or Upgrade
1/11/2006 - 11:44 AM debby Authorized Software Opt:
1/11/2006 - 11:47 AM debby Authorized Software Opt:
--
tech1NJ