nth value in an array formula
 

I currently have an array formula that reads
{=IF(MilestoneDates=$D$31;Tasks;"")}

What this does is that it checks through my milestone dates and sees if they
are equal to the next Group Meeting date ($D$31) - if they are, the formula
returns the associated Task. The problem is I only know how to use this to
show the first such result. What I want to be able to do is return, for
example, all five tasks with this milestone date, on separate rows. The tasks
are all text values unfortunately, which means I can't use BIG or SMALL
unfortunately. Any ideas?

nth value in an array formula
 

Perhaps this can help:

http://office.microsoft.com/en-us/ex...260381033.aspx
--
Gary''s Student - gsnu200839


"Babymech" wrote:

I currently have an array formula that reads
{=IF(MilestoneDates=$D$31;Tasks;"")}

What this does is that it checks through my milestone dates and sees if they
are equal to the next Group Meeting date ($D$31) - if they are, the formula
returns the associated Task. The problem is I only know how to use this to
show the first such result. What I want to be able to do is return, for
example, all five tasks with this milestone date, on separate rows. The tasks
are all text values unfortunately, which means I can't use BIG or SMALL
unfortunately. Any ideas?

nth value in an array formula
 

Thanks - that did help indeed, but as soon as I got it to work another
question popped up: now I want to do the same with multiple criteria. I
assumed that it wouldn't be harder than using:

{=IFERROR(INDEX(Tasks;SMALL(IF(AND(MilestoneDates D31-6;MilestoneDates<=D31);ROW(MilestoneDates));ROW(1: 1))-3);"")}

But that returns nothing. How would I go about setting it up so that the
date it checks is between two values, or bring in another criteria to bear?

"Gary''s Student" wrote:

Perhaps this can help:

http://office.microsoft.com/en-us/ex...260381033.aspx
--
Gary''s Student - gsnu200839


"Babymech" wrote:

I currently have an array formula that reads
{=IF(MilestoneDates=$D$31;Tasks;"")}

What this does is that it checks through my milestone dates and sees if they
are equal to the next Group Meeting date ($D$31) - if they are, the formula
returns the associated Task. The problem is I only know how to use this to
show the first such result. What I want to be able to do is return, for
example, all five tasks with this milestone date, on separate rows. The tasks
are all text values unfortunately, which means I can't use BIG or SMALL
unfortunately. Any ideas?

nth value in an array formula
 

Never mind - I think I got it, by using multiplication instead of an AND
formula. Thanks!

"Babymech" wrote:


Thanks - that did help indeed, but as soon as I got it to work another
question popped up: now I want to do the same with multiple criteria. I
assumed that it wouldn't be harder than using:

{=IFERROR(INDEX(Tasks;SMALL(IF(AND(MilestoneDates D31-6;MilestoneDates<=D31);ROW(MilestoneDates));ROW(1: 1))-3);"")}

But that returns nothing. How would I go about setting it up so that the
date it checks is between two values, or bring in another criteria to bear?

"Gary''s Student" wrote:

Perhaps this can help:

http://office.microsoft.com/en-us/ex...260381033.aspx
--
Gary''s Student - gsnu200839


"Babymech" wrote:

I currently have an array formula that reads
{=IF(MilestoneDates=$D$31;Tasks;"")}

What this does is that it checks through my milestone dates and sees if they
are equal to the next Group Meeting date ($D$31) - if they are, the formula
returns the associated Task. The problem is I only know how to use this to
show the first such result. What I want to be able to do is return, for
example, all five tasks with this milestone date, on separate rows. The tasks
are all text values unfortunately, which means I can't use BIG or SMALL
unfortunately. Any ideas?

nth value in an array formula
 

Excel 2007 Pivot Table
Works on text.
One easy non-array formula:
=([Deadline]-[Milestone]<6)*([MyCriteria]=1)
No hidden rows. Dynamic resize.
http://www.mediafire.com/file/lgajntlwldm/03_16_09.xlsx