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 '15 at 20:35
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.
    – branches
    Jul 30 '15 at 7:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.