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
XMATCHonly evaluates on a 1-dimensional arrayAGGREGATEis promising but I’ve so far been unable to evaluateCELL("address",{})(or similar) such that it returns an array that I can use withAGGREGATE
The workbook in question is a shared workbook hosted in OneDrive so I’d like to avoid VBA if at all possible.
>Solution :
One way (with Office 365) would be:
=FILTER(Table1[#Headers],BYCOL(--ISNUMBER(MATCH(Table1,E2,0)),LAMBDA(x,MAX(x))))
where E2 is search value.
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)))) )
