Try...

=INDEX(Migration!$CH$2:$CH$3900,MATCH(1,(Migration !$A$2:$A$3900=A2)*(Migration!$B$2:$B$3900=B2)*(Mig ration!$CK$2:$CK$3900=MAX(IF((Migration!$A$2:$A$39 00=A2)*(Migration!$B$2:$B$3900=B2),Migration!$CK$2 :$CK$3900))),0))

...confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Hope this helps!

kkendall Wrote:
Hello,

I am working on converting a set of data from an old system to a new
system, and in the process, I need to be able to do the index below,
but to also add in one more critieria. I need it to not just return the
result, but to return the result based on the max date, which is in a
different column.

=INDEX(Migration!$CH$2:$CH$3900,MATCH(1,(Migration !$A$2:$A$3900=A2)*(Migration!$B$2:$B$3900=B2),0))

Migration!$CH = Status column (open, closed, scheduled)
Migration!$A = PO #
Migration!$B = Job type (rough, trim)

The last column needed to compare against is Migration!$CK which is a
date column.

I experimented with many options, including:

=INDEX(MAX(Migration!$CK$2:$CK$3900)*Migration!$CH $2:$CH$3900,MATCH(1,(Migration!$A$2:$A$3900=A2)*(M igration!$B$2:$B$3900=B2),0))

I have also set the code, except for the last part I cannot figue out
as:

=INDEX(Migration!$A$3:$CK$3901,MATCH(1,(Migration! $A$3:$A$3901=A2)*(Migration!$B$3:$B$3901=F2),0),86 )

But no luck. Any ideas? Would this be easier using VBA?

Thank you,

Kelly


--
Domenic
------------------------------------------------------------------------
Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785
View this thread: http://www.excelforum.com/showthread...hreadid=393096