SOLVED

Find one or more scattered cells in a range

Occasional Contributor

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:

Screenshot 2022-08-05 111232.png

 

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:

 

Screenshot 2022-08-05 112308.png

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

3 Replies
best response confirmed by TheDub (Occasional Contributor)
Solution

@TheDub 

 

Just an idea:

_Screenshot.png

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!

@TheDub 

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