1

I am attempting to obtain an array of only values that meet certain criteria.

I have an array in which the meeting of these criteria is summarised in a column of TRUE/FALSE for each entry, as such

INDEX MEETS_CRITERIA
1     TRUE
2     TRUE
3     FALSE
4     FALSE
5     FALSE
6     TRUE
7     TRUE
8     TRUE
9     FALSE
10    FALSE

I wish to obtain, in a separate sheet, a list, without gaps, of all indexes that have true in the column "MEETS_CRITERIA", as below

INDEX
1
2
6
7
8

I am attempting this with the below array formula:

{=INDEX(ORIGINAL_ARRAY!$A$2:$A$10, MATCH("TRUE",ORIGINAL_ARRAY!$B$2:$B$10, 0))}

However this returns #N/A.

What is the error in my formula or my approach?

1
  • TRUE is a Boolean value and as such doesn't need the quotation marks.
    – Clif
    Jul 29, 2015 at 20:35

1 Answer 1

2

As well as the observation by Clif, your current formula would in any case only be suitable for providing a single return, not the list you give.

For that you would require (assuming you have Excel 2010 or later):

=INDEX(ORIGINAL_ARRAY!$A:$A,AGGREGATE(15,6,ROW(ORIGINAL_ARRAY!$B$2:$B$10)/ORIGINAL_ARRAY!$B$2:$B$10,ROWS($1:1)))

and copied down.

As to masking errors, then, assuming the example you give is representative of your actual set-up - in that there are only a dozen or so rows in the source data - you can afford an otherwise inefficient IFERROR set-up, viz:

=IFERROR(INDEX(ORIGINAL_ARRAY!$A:$A,AGGREGATE(15,6,ROW(ORIGINAL_ARRAY!$B$2:$B$10)/ORIGINAL_ARRAY!$B$2:$B$10,ROWS($1:1))),"")

If the data range in question is in fact quite large, then it will be much preferable to use an additional cell somewhere in the sheet, e.g. D1, in which a single formula is used to determine the expected number of returns, viz:

=COUNTIF(ORIGINAL_ARRAY!B2:B10,TRUE)

which can then be referenced in the main formula itself i.e.:

=IF(ROWS($1:1)>D$1,"",INDEX(ORIGINAL_ARRAY!$A:$A,AGGREGATE(15,6,ROW(ORIGINAL_ARRAY!$B$2:$B$10)/ORIGINAL_ARRAY!$B$2:$B$10,ROWS($1:1))))

and will be far more efficient than the IFERROR set-up, for reasons (if you're interested) explained here:

Look up a value in a list and return ALL multiple corresponding values

Regards

1
  • Works perfectly and went above and beyond with the error checking. An excellent answer. Many thanks for your assistance. Jul 30, 2015 at 7:54

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .