Array Formula

[mdorman]

Problem.xls
	A	B	C	D	E	F	G	H
1			Start	Finish		Criteria List	or	Criteria List
2	#1	ape	1	4		1
3		bear	3	6		0
4		cat	1	3		1
5		dog	8	5		0
6						2		2
7
8			Start	Finish		Criteria List	or	Criteria List
9	#2	ape	1	4		ape		ape
10		bear	3	6		FALSE
11		cat	1	3		cat		cat
12		dog	8	5		FALSE
13
14			Start	Finish		Criteria List
15	#3	ape	1	4		ape
16		bear	3	6		cat
17		cat	1	3
18		dog	8	5
Sheet2

#1 & #2 above are a given (even for me), but i'd like to get to #3 which generates a list which excludes returned values that don't meet the given criteria. I hope the example helps.

Surely there is a way to accomplish this in excel using an array formula (or other method)????

Any help is greatly appreciated...

 

[fairwinds]

Hi,

=IF(ROW()-ROW($F$1)>COUNTIF($D$2:$D$5,"<5"),"",INDEX($B$1:$B$5,SMALL(IF($D$2:$D$5<5,ROW($D$2:$D$5)),ROW()-ROW($F$1))))


is entered with Ctrl + shift + enter in F2, then dragged down.

Note that when you adjust to your ranges, the range after "INDEX(", must always start at row 1 wherever your data is.

Book1
	A	B	C	D	E	F
1			Start	Finish		CriteriaList
2	#3	ape	1	4		ape
3		bear	3	6		cat
4		cat	1	3
5		dog	8	5
Sheet2

 

[mdorman]

This works - and works well....

Anyway you can explain WHY it works?

Thanks in advance for your help.... i'd like to understand this better...

 

[fairwinds]

IF($D$2:$D$5<5,ROW($D$2:$D$5)
Gives an array of the row numbers for the cells that meet criteria
In this case 2 , FALSE , 4 , FALSE

ROW()-ROW($F$1)
Gives 1 for the first formula, 2 for the second etc.

Thus
SMALL(IF($D$2:$D$5<5,ROW($D$2:$D$5)),ROW()-ROW($F$1))
Gives the smallest row number for the first formula,the second smallest for the second etc.

The above is used within the INDEX formula to trurn the name from that row.

The first part:
IF(ROW()-ROW($F$1)>COUNTIF($D$2:$D$5,"<5"),"",
is to give a blank cell instead of an error value when there are no more cells meeting your criteria.

 

[fairwinds]

Re your PM:

1. To understand the individual functions, please look in help file, it will be too much typing to explain them here one by one.

2. To get the criteria from a cell, you need to change the formula in 2 places. Here I linked H1 as the cell holding the number 5.

=IF(ROW()-ROW($F$1)>COUNTIF($D$2:$D$5,"<"&$H$1),"",INDEX($B$1:$B$5,SMALL(IF($D$2:$D$5<$H$1,ROW($D$2:$D$5)),ROW()-ROW($F$1))))

 

[mdorman]

Ahhh now that is how you spell relief.... the winds are indeed fair.

Many thanks,