Array Formula

mdorman

New Member
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

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
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

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...

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.

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))))

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

Many thanks,

Replies
14
Views
416
Replies
3
Views
729
Replies
1
Views
154
Replies
5
Views
184
Replies
8
Views
201

1,221,521
Messages
6,160,302
Members
451,637
Latest member
hvp2262

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.

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

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