• Status: Solved
• Priority: Medium
• Security: Public
• Views: 123

# What's wrong with this array formula?

Hello,

Can someone help me understand what's wrong with the following formula; why it's wrong; and what changes will get it to work (if that's possible)? (The 2nd is the same as the 1st but with additional parentheses.)

{=IF(\$M\$11:\$M\$600="BIG",ROW(\$M\$11:\$M\$600))}
{=IF((\$M\$11:\$M\$600)="BIG",ROW(\$M\$11:\$M\$600))}

Explanation

As a result of some other unrelated formulas, one of several results which can display in column M of a worksheet is the word "BIG".

The formula:

=COUNTIF(\$M\$11:\$M\$600,"BIG")

returns 8, so the next step is to determine which 8 rows contain the specified result ("BIG").

I realize that there are simpler methods for doing this (eg filter, MATCH(INDEX()) [I think]) but as an "Excel-er" who has recently been venturing into the world of array formulas, I wondered if it could be done using that approach.

My reasoning for attempting an array formula was the realization that if I were to look at each of the cells one-at-a-time, I would use the following 590 formulas:

=IF(M11="BIG",ROW(M11))
=IF(M12="BIG",ROW(M12))
=IF(M13="BIG",ROW(M13))
…
=IF(M600="BIG",ROW(M600))

and of the results, 582 would return FALSE and the remaining 8 would return their respective row numbers.

Then, the next step seemed to be combining or restating the 590 formulas (each of which references a single cell) into one array formula which references all of the cells (ie the entire range). That's been my sense of what an array formula does (ie combines operations on multiple single cells into a single operation on a range of cells) with the result being a string of TRUE's & FALSE's.

Obviously my reasoning — or maybe my syntax (or both) — is incorrect so hopefully someone can correct what needs correcting. :)

Thanks

PS Just before posting the question, I started thinking that maybe the problem (or one of the problems) is that I'm trying to return Row numbers in a string of results which can only consist of TRUE's & FALSE's. Is that a correct observation? It seems like I've seen other results (besides T & F) when using the Evaluate Formula box in the past but maybe this is not the same.
0
2 Solutions

Commented:
It give you a false as an answer because for your IF you don't have  second condition specified...

In additional the way you have applied an array formula is incorrect because normally when you apply an array formula you combine with an aggregate function at the end to do what you are looking for since once you find the matching results an aggregate function will act as like group by statement on it.

Here are the few links which you can refer for understanding of array formulas...

Array Formula-1

Array Formula-2

Saurabh...
0

Commented:
Hello Steve,

The first formula works fine, you don't need the added parentheses....but it returns an array of 589 values - that array will consist of row numbers when the row contains "BIG", or FALSE otherwise

To see what the array looks like you can paste that formula in a cell, select the cell, press F2 to select the formula and then press F9 to display the array - you'll see something like this:

={FALSE;FALSE;10;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;21;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE........

Note you see the numbers 10 and 21, that means that "BIG" is in those rows

As Saurabh says you need some other function to extract the relevant numbers. One way to do that is to use SMALL function in successive rows with the k value of SMALL starting at 1 and incrementing every row, for example put this formula in P2, confirm with CTRL+SHIFT+ENTER and copy down

=SMALL(IF(\$M\$11:\$M\$600="BIG",ROW(\$M\$11:\$M\$600)),ROWS(P\$2:P2))

That will give you the first row number (with "BIG") in P2, the second in P3, the third in P4 etc (in my example you'd get 10 in P2, 21 in P3 etc.)......When you run out of matches you'll get #NUM! errors, so to prevent that you could add an IFERROR function to get blanks instead, e.g.

=IFERROR(SMALL(IF(\$M\$11:\$M\$600="BIG",ROW(\$M\$11:\$M\$600)),ROWS(P\$2:P2)),"")

In that formula, as you copy down, ROWS function increments each time so it returns 1 in P2, 2 in P3 etc. so you get each successive row number where "BIG" exists.

regards, barry
0

Older than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.