#N/A issue when using an equation to give the latest date in a list

[EAGLEBUCKS]

Example1.xlsx

Hi everyone, I am having an issue with this equation you can see in the cell showing N/A in attached file. Basically what I am trying to do is we have a long list of material that shows everything we are waiting on currently. This list also shows the dates that the material will be coming in. For scheduling purposes I am trying to come up with a way where I can see the latest date for each cost unit (e.g. C.A5305.110, C.A5305.120, etc.). For example if I had these three material missing....

Cost Unit Material Due Date
C.A5305.110 Hoist 8/15/12
C.A5305.110 Crank 8/20/12
C.A5305.110 Rod lock 8/28/12


I would want the cell to show 8/28/12 for that cost unit. I want to have one cell for each cost unit (e.g. C.A5305.110, C.A5305.120, etc.). Does anyone have any idea on how to do this?

Thanks,
John

[jason.b75]

Array confirmed with Shift Ctrl Enter

=IFERROR(1/(1/MAX(IF($A$2:$A$6527=M2,$K$2:$K$6527))),"")

[Søren Larsen]

Something like this:

Please Login or Register  to view this content.

I think the methodology would work, anyway.

[NBVC]

So are you looking for the last match to column M in A, and return corresponding date from K?

Try:

=LOOKUP(2,1/($A$2:$A$6527=M2),$K$2:$K$6527)

copied down. I assume this is a shortened list and that is why we get N/A for most....

[EAGLEBUCKS]

WOW you guys are good and quick! It worked out perfectly, I really appreciate the help and this issue is now closed!

[NBVC]

You got 3 differing formulas giving different results... you should indicate which was the one that worked for you so that future users can use this....

and then mark the thread as SOLVED