What's wrong with this array formula?


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



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:


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:


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. :)


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.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
barry houdiniCommented:
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:


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


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.


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
Martin LissOlder than dirtCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.