Array to find relative position - is there a better way?

[John Michl]

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

 

[Bob Phillips]

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]

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

 

[John Michl]

I figured out an alternative that will return the position of the last
match instead of the first.

{=MATCH(1,1/((Projects=$A$1)*(MAX(IF(Projects=$A$1,Dates))=Dates)),
1)} (entered as an array formula)

Dividing the array inside this MATCH formula into 1 (i.e. 1/(rest of
formula)) creates an array of #DIV/0! values where the original array
value was 0. The remaining values will be 1 since (1/1 = 1). Since
MATCH ignores error values, the only values evaluated in the formula
are the ones that match the criteria. The Match type of 1 forces the
formula to return the position of last matching value.

Thanks for the help Bob.

- John Michl

http://excelguru.johnmichl.com coming soon.