How do I make a list of cells that contain text from a table and avoid blanks? Index/Match/Lookup options (no VBA)

Question

I have a table in excel set up like so:

Procedure	Visit 1	Visit 2	Visit 3	Visit 4
Arm		X	Z	X
Brain	X	Y	X
Chest			Z	Z
Eye	X	Y	X	Z

I want to get be able to select information like so that I can filter to see what procedures marked X (or Y or Z) occur on what Visits. Basically I'd love for the output to look like this, skipping blanks.

Arm (X)
Visit 2
Visit 4

Eye (Z)
Visit 4

I have tried this in countless ways in a pivot table and can't quite get it to look like I want. I'm on a work computer, so can't run VBA.

I can get this to work with LOOKUP, but I do a ton of these that are different size tables. Match/Index?

I'm sorry, my brain is fried.

Answer 1

It will be better to return the output like shown in the screenshot below, so you can see all the procedures where it has returned X or Y or Z

---

• Formula used in cell H2

=HSTACK(TOCOL(IFS(G2=Table1[[Visit 1]:[Visit 4]],Table1[Procedure]),3),
 TOCOL(IFS(Table1[[Visit 1]:[Visit 4]]=G2,Table1[[#Headers],[Visit 1]:[Visit 4]]),3))

---

Notes: Please change the table names as per your suit.

---

Or,

=LET(
     α, TOCOL(IFS(G2=Table1[[Visit 1]:[Visit 4]],Table1[Procedure]&"|"&Table1[[#Headers],[Visit 1]:[Visit 4]]),3), 
     HSTACK(TEXTBEFORE(α,"|"),TEXTAFTER(α,"|")))

---

Returning the output in a tabular layout is recommended, so it can be used in future to create a pivot tables or for other data manipulations, however here is another way, you could try as well:

---

• Formula used in cell H2

=LET(
     α, TOCOL(IFS(G2=Table15[[Visit 1]:[Visit 4]],Table15[Procedure]&"|"&Table15[[#Headers],[Visit 1]:[Visit 4]]),3),
     φ, TEXTBEFORE(α, "|"),
     δ, TEXTAFTER(α,"|"),
     uφ, UNIQUE(φ),
     HSTACK(uφ, BYROW(uφ, LAMBDA(x, TEXTJOIN(", ",,FILTER(δ, x=φ))))))

---