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"&ROWS(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