Please help with array function

  • Thread starter Thread starter David
  • Start date Start date
D

David

Greetings,
I am working with a large set of data but the following
scenario describes the problem:
Each of the following is a field in the database:
TAG(text), JobCode (Text - 2 chr), PlanDate(date), Status
(is either "complete" or "incomplete"). Each tag appears
many times in the table with various codes, dates and
status.
I have a seperate table with all tags in column A (no
duplicates) for lookup purposes. To find the earliest
PlanDate for each tag where ststus is "incomplete" i use
the following:
=LARGE((TAGS=$A4)*(STATUS="incomplete")*PlanDate,SUM
((TAGS=$A4)*(STATUS="incomplete"))))
This works OK. I think I could use a similar them to
return each later PlanDate. What I would like to do (and
am struggling with) is to return the 'JobCode' as well as
the PlanDates.
Any help will be much appreciated
David
 
Harlan,
Thanks again for your solution. Please can I ask you to
just clear one thing up for me:
"For the earliest,

=INDEX(JobCode,MATCH(MIN(IF((TAGS=$A4)* (STATUS="incomplete"),PlanDate)),
IF((TAGS=$A4)*(STATUS="incomplete"),PlanDate),0))"
Click to expand...

That zero, before the 2nd last bracket. I know that it is
the number of columns argument. I've found that the
formula works perfectly with this set to zero (and
imperfectly when it is ommited or set to 1). Just for my
education, can you please explain why zero gives the
required result?
TIA
David
 
Thanks again for your solution. Please can I ask you to
just clear one thing up for me:
Click to expand...

[reformatted for clarity - delete underscores and newlines]
=INDEX(
___JobCode,
___MATCH(
_____MIN(
_______IF(
_________(TAGS=$A4)*(STATUS="incomplete"),
_________PlanDate
_______)
_____),
_____IF(
_______(TAGS=$A4)*(STATUS="incomplete"),
_______PlanDate
_____),
_____0
___)
_)
That zero, before the 2nd last bracket. I know that it is
the number of columns argument. . . .
Click to expand...

The reformatting above should make it clearer that the 0 before the 2nd to last
right parenthesis is the 3rd argument to the MATCH function rather than an
argument to the INDEX function. As the 3rd argument to MATCH, it tells MATCH to
search for an *exact* match rather than the largest value less than or equal to
the lookup value (in this case, the MIN function's result). You need exact
matching in this instance.
 
Harlan,
Once again, thankyou for clearing that up. The clarity of
your reply is sincerely appreciated.

Kind regards,
David
 
Back
Top