Thanks Bob. That does help. It is much cleaner than my approach.
However, in case of a tie (i.e. two identical dates for the matching
project, it pulls the position of the first record not the last. My
complex formula does pull the last record but given that it is highly
unlikely that the a project would have two different stages on a given
day, I think I'll go with the more simple approach.

Thanks.

- John Michl


On Oct 5, 6:09 pm, "Bob Phillips" wrote:
This looks simpler to me

=MATCH(1,(Projects=A1)*(MAX(IF(Projects=A1,Dates)) =Dates),0)

still an arrauy formula

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"John Michl" wrote in message

ps.com...



I have a large table of transactions that includes Project Number,
Transaction Date and Project Stage. The data ranges are named
Projects, Dates and Stages. The lookup value is a specific project
number that is stored in a single cell, say "A1". The data can be
sorted in random order which limits some approaches.

I've created a somewhat complex array formula that will return the
relative position number for the row that has the largest date value
for rows that match project. (If multiple rows have the same date, it
returns the last such row.) I can use this in an INDEX function to
pull the most recent stage.

Formula to return the relative position (row with in a range):

{=MAX(((Projects=A1)*Dates=MAX((Projects=A1)*Dates ))*ROW(INDIRECT("A1:A"&RO*WS(Projects))))}

My array formula seems overly complex but it works so I'm hoping
there's a better way. Any ideas?

- John Michl
www.johnmichl.com- Hide quoted text -

- Show quoted text -