Link to home
Start Free TrialLog in
Avatar of WeThotUWasAToad
WeThotUWasAToad

asked on

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.
ASKER CERTIFIED SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.