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