Help with complex index array issue

[kkendall]

Hello,

I am working on converting a set of data from an old system to a ne
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 th
result, but to return the result based on the max date, which is in
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

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)*(Migration!$B$2:$B$3900=B2),0))

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

Thank you,

Kell

 

[Domenic]

Try...

=INDEX(Migration!$CH$2:$CH$3900,MATCH(1,(Migration!$A$2:$A$3900=A2)*(Migration!$B$2:$B$3900=B2)*(Migration!$CK$2:$CK$3900=MAX(IF((Migration!$A$2:$A$3900=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]

That worked perfect. Thank you very much for the help.

Kell

 

[kkendall]

Domenic, thanks again for the help. I am on to my next and last issu
with this formula.

I need to use it in a different location to basically concatenate ever
entry that it finds, less the max data value. I hope that makes sense
So what I am trying to see is if I can add something to it less max-1
max-2, etc to pull each record in in order. Is that at all possible?

Would it be something like:

=INDEX(Migration!$CW$2:$CW$3900,MATCH(1,(Migration!$A$2:$A$3900=A2)*(Migration!$B$2:$B$3900=F2)*(Migration!$CK$2:$CK$3900=MAX(-1)*(IF((Migration!$A$2:$A$3900=A2)*(Migration!$B$2:$B$3900=F2),Migration!$CK$2:$CK$3900))),0)

 

[Domenic]

To get the second largest, use the LARGE function with 2 as its position
argument...

=INDEX(Migration!$CW$2:$CW$3900,MATCH(1,(Migration!$A$2:$A$3900=A2)*(Migration!$B$2:$B$3900=B2)*(Migration!$CK$2:$CK$3900=LARGE(IF((Migration!$A$2:$A$3900=A2)*(Migration!$B$2:$B$3900=B2),Migration!$CK$2:$CK$3900),2)),0))

Actually, you can replace the last number 2 (position argument for
LARGE) with ROWS($CY$2:CY2), enter the formula in CY2, and copy down.
This will give you the first largest, second largest, third largest,
etc.

Hope this helps!