Get column names if column contains matching value (without VBA)

user_4709762 0
Viewed 201

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

  • XMATCH only evaluates on a 1-dimensional array
  • AGGREGATE is promising but I've so far been unable to evaluate CELL("address",{}) (or similar) such that it returns an array that I can use with AGGREGATE

The workbook in question is a shared workbook hosted in OneDrive so I'd like to avoid VBA if at all possible.

excel-formula excel
1 Answers

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. enter image description here

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)))) )

enter image description here

Another approach based on Excel for Web

This depends on SEQUENCE which is supported in Excel for Web. Hopefully, it works on Sharepoint. If not, then there is a (cumbersome) way to tease out sequences using ROW(), but it would be easier if you don't have to go there.

=TEXTJOIN(",",1,
     UNIQUE(IFERROR(INDEX(Table1[#Headers],
                    LARGE(IFERROR(MATCH(Table1,E2,0)*COLUMN(Table1[#Headers]),-1),
                          SEQUENCE(,COLUMNS(Table1),COLUMNS(Table1),-1))),
                    ""),
            1) )
user_46171210
Related