Given a value, can you return a series of rows ignoring blanks but including duplicates?

user_10209842 0
Viewed 41

I have one sheet that looks similar to this:

enter image description here

I would like a formula that will allow me to provide a value from row 1, then remove all blank rows and give me column A and the corresponding column to my selection. Note that values in column A may sometimes be duplicates. Here are some examples of what results might look like:

enter image description here enter image description here

google-sheets-formula google-sheets
2 Answers

try:

=INDEX(QUERY({A1:A, INDIRECT(ADDRESS(1,         MATCH("dog", 1:1, 0))&":"&
                             ADDRESS(ROWS(A:A), MATCH("dog", 1:1, 0)))}, 
 "where Col2 is not null", 0))

enter image description here

user_56326290

Here is another option on the new tab in your shared sheet called MK.idea:

=FILTER(SPLIT(FLATTEN(A2:A&"|"&B2:F),"|",0,0),FLATTEN(B1:F1&(B2:F<>""))=(I1&TRUE))
user_110751290
Related