Hans,

Here's a solution for you (not the most elegant, but it works):

=SUM(IF(Sheet2!$H$3:$H$5000=MAX(IF(C3=Sheet2!$D$3: $D$5000,Sheet2!$H$3:$H$5000)),IF(C3=Sheet2!$D$3:$D $5000,Sheet2!$JI$3:$J$5000)))

You have to include references to rows - in my example it's 3 and 5000. This
is an array formula, so you must press CTRL+SHIFT+ENTER to add braces {}
around it.
If you have multiple entries for the same store for the same Max date, they
will be added. To select Min or Max from the multiple entries, change SUM
accordingly; don't forget CTRL+SHIFT+ENTER.



" wrote:

Dom,
This is frustrating to say the least.
I just read somewhere in a post the same thing you stated, so let me
give it another go.

In Sheet1 I have the Store#'s in column C. I need a formula to look in
Sheet2 and find the same store number in column D (Store Numbers can
exist multiple times, hence the MAX), then look in Sheet2 column H and
IF the date is the MAX date for that store then look in column J for
the data I need returned to Sheet1 column E.


Does this make any sense?


Thanks

Hans

wrote:
I can't quite make out what you want, but one suggestion is this: Match
and Max do not return true / false, which is what AND wants for
arguments. In both the case of Match and Max, however, the return will
be evaluated as either true or false, and that may explain why it looks
random.

Hope this gets you started.

Dom




wrote:
I have looked and tried various posts ideas, but can't quite get this
working.

The formula is:
=IF(AND(MATCH(C3,'[Endcap Comments.xls]Rep
Comments'!$D:$D,0),MAX('[Endcap Comments.xls]Rep
Comments'!$H:$H)),OFFSET('[Endcap Comments.xls]Rep
Comments'!$A:$A,0,9),"")

What I am expecting it to do is:
IF the match from c3 and D:D is true AND the MAX is true for the
previous match then offset to give me the resulting data. It is
returning data but not what is offset by 0 rows and 9 columns. It seems
to be just random...

Thanks


Hans