Array Formula

mdorman

New Member
Joined
Jan 27, 2005
Messages
23
Problem.xls
ABCDEFGH
1StartFinishCriteria ListorCriteria List
2#1ape141
3bear360
4cat131
5dog850
622
7
8StartFinishCriteria ListorCriteria List
9#2ape14apeape
10bear36FALSE 
11cat13catcat
12dog85FALSE 
13
14StartFinishCriteria List
15#3ape14ape
16bear36cat
17cat13
18dog85
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...
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

fairwinds

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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
ABCDEF
1StartFinishCriteriaList
2#3ape14ape
3bear36cat
4cat13 
5dog85 
Sheet2
 

mdorman

New Member
Joined
Jan 27, 2005
Messages
23
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

MrExcel MVP
Joined
May 15, 2003
Messages
8,638
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

New Member
Joined
Jan 27, 2005
Messages
23
Ahhh now that is how you spell relief.... the winds are indeed fair.

Many thanks,
 

Forum statistics

Threads
1,148,049
Messages
5,744,509
Members
423,881
Latest member
Nguyen Vu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top