Your question, your audience. Choose who sees your identity—and your question—with question security.

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

{=IF($M$11:$M$600="BIG",RO

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

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

2 Solutions

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

={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(IF($M$11:$M$600="BI

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(SMALL(IF($M$11:$M

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

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.

Tackle projects and never again get stuck behind a technical roadblock.

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