Forum Discussion
Find one or more scattered cells in a range
I'm not sure if I'm missing something, or just have a prolonged brain freeze...
Using the most recent version of 365, including beta functions.
Say I have this:
Simple (?) question: what function would return "Find me"? Not an array, not a column or row, just the content of the cell which is not "xxx".
Does the answer change if it looks like this:
and I want both cells that are not "xxx"?
Finally, does the answer change if instead of "xxx" the relevant cells were blank or FALSE, for example, if the array is the output of some function (see attached file)?
Dazed and confused...
Just an idea:
E1:
=TEXTJOIN("; ",TRUE,IF(A1:C3<>"xxx",A1:C3,""))E5:
=TEXTJOIN("; ",TRUE,IF(A5:C7<>"xxx",A5:C7,""))E9:
=TEXTJOIN("; ",TRUE,IF(A9:C11<>"",A9:C11,""))E13:
=TEXTJOIN("; ",TRUE,IF(A13:C15<>FALSE,A13:C15,""))Wrap above formulas in Beta TEXTSPLIT function if you want the values in columns/rows
3 Replies
Just an idea:
E1:
=TEXTJOIN("; ",TRUE,IF(A1:C3<>"xxx",A1:C3,""))E5:
=TEXTJOIN("; ",TRUE,IF(A5:C7<>"xxx",A5:C7,""))E9:
=TEXTJOIN("; ",TRUE,IF(A9:C11<>"",A9:C11,""))E13:
=TEXTJOIN("; ",TRUE,IF(A13:C15<>FALSE,A13:C15,""))Wrap above formulas in Beta TEXTSPLIT function if you want the values in columns/rows
- So it was a brain freeze after all - and TEXTJOIN() was the answer. It feels a little hacky - I thought there would be some magic function to pinpoint a particular cell in an array if that cell meets/doesn't meet certain conditions, but I guess Excel isn't there yet...
But it does work - and thanks!It mainly depends on which form you'd like to have an answer. As variant
=LET( exclude, "xxx", c, TOCOL(A1:C3), FILTER( c, c <> exclude ) )
