FILTER or XLOOKUP? One criteria in multiple locations; possible multiple results listed

Hi,

I think I'm to the point of brainfarting for the time being, so I'm sorry if this has been asked before. I feel like I'm not grasping the answers I'm researching, so I'll just ask directly:

Screenshot 2021-08-12 112505.jpg

This is a mock up of what I want. While you look at it, How do I search for one criteria that could be in multiple columns (in this case multiple Action columns), and return the all results? In real life this may be one result or five results. Basically it's a who shares this criteria? question, which is easy enough when the criteria is in one column: FILTER. But multiple columns? Many of the answers I see are how to display results using select columns by using a FILTER-wrap, like

Please Login or Register  to view this content.

I'm not finding how to search multiple, possibly non-adjacent columns for criteria. And maybe I need to use XLOOKUP instead, but I think that returns only one result, not many.

Like I said I'm brainfarting, so maybe I'm staring the answer in the face without seeing it.

The mock up spreadsheet is attached.

Thank you.

PS: I could go the macro route, but I'm exploring formulas first. Thanks.

=LET(rng,B2:G5,rws,ROWS(rng),cols,2,seq,SEQUENCE(rws*COLUMNS(rng)/cols,,0),a,INDEX(A2:A5,MOD(seq,rws)+1),b,INDEX(rng,MOD(seq,rws)+1,INT(seq/rws)*cols+SEQUENCE(,cols)),FILTER(CHOOSE({1,2,3},a,INDEX(b,,1),INDEX(b,,2)),INDEX(b,,2)=A8))

Wow!

This works...by magic, I imagine.
I'll see if I can decipher it.

UPDATE: Ok - so LET formula is new to me. But now that I'm reading about it, it reminds me of those one-line programs that Beagle Brothers used to issue for the Apple IIe computers way back when - all the variables, formulas and functions on one line of code. The rotating index card-file with sound was my favorite.

So, it seems LET is basically a one-line macro with variable assignment and substitution, and resultant formulas wrapped inside a function.

• I'm seeing that everything between the first "(" and "FILTER" is you setting up variables
• I'm seeing that the first variables are used to generate secondary variables
• FILTER is after the last comma of the LET formula, telling LET that all the variables are to be used in the FILTER formula.
• The FILTER + CHOOSE combo does the heavy lifting - I'm still not sure how

Thank you again.