im on google sheets. so i want to output a set of words in a header, depending on if the number in a cell is found in the columns below the header. I also wanted it to output each header in a line break depending on if the number in a cell is in it. So if the number is found in columns A, G, and I it will output the header in those columns.
what i know how to do is this
=SUBSTITUTE(
TEXTJOIN(CHAR(10), TRUE,
IF(COUNTIF(A2:A, G5), A$1, ""),
IF(COUNTIF(B2:B, G5), B$1, "")
),
", ", CHAR(10)
)
basically looks for the number in cell G5 in columns A and B, then outputs the header so A1 and B1. But I want to make it more dynamic because I’ll be using it multiple times. I need help in making the formula work for multiple columns. like let’s say ill be doing it in a table with 20 columns. how do i NOT do it manually per column?
>Solution :
A generalized representation for lets say 6 columns A-F and Cell_G5 has the search_criteria:
=join(char(10),ifna(filter(A1:F1,bycol(A2:F,lambda(Σ,xmatch(G5,Σ))))))