Given a named range Table1, how do I return all names from the array Table1[#Headers] where a matching input value exist in that column?
| a | b | c |
|---|---|---|
| 1 | 2 | 3 |
| 4 | 5 | 2 |
| 6 | 1 | 2 |
For the above sample data, I would like to return
| search_value | headers |
|---|---|
| 1 | a,b |
| 2 | b,c (or b,c,c) |
| 3 | c |
With the above sample data, I can count the occurrences of a named search_value with the formula =SUM(--(Table1=search_value)). Given the existing True/False array from that formula, I've been trying to get relative cell mapping information. Sadly
concatenated
Sorry - I just realized that you want a concatenated output:
=TEXTJOIN( ",", 1, FILTER(Table1[#Headers],BYCOL(--ISNUMBER(MATCH(Table1,E2,0)),LAMBDA(x,MAX(x)))) )