Is there an easy way to find multiple header columns given a set of data?

Question

I have a sheet that looks like this:

I'd like to have a formula that queries the data in A2:E7 and pulls the corresponding column headers from A1:E1. The trick is that values may appear more than once.

Sample results would look like this:

Example sheet: https://docs.google.com/spreadsheets/d/1Zr_q8nwYPixUjxWYdT-WlQLiGIUzqoK_cSQXSp19nJg

Answer 1

Suggestion:

If you are amenable to scripting (Tools -> Script Editor), you can create a custom formula for this:

function CUSTOMLOOKUP(key, headers, table) {
  var result = [[]];
  for (i = 0; i < table.length; i++) {
    for (j = 0; j < table[i].length; j++) {
      if (key == table[i][j]) {
        result[0].push(headers[0][j]);
      }
    }
  }
  return result;
}

After saving, in your spreadsheet, CUSTOMLOOKUP can now be used as:

=CUSTOMLOOKUP($G1,$A$1:$E$1,$A$2:$E$7)

And then drag down to the rest of the values.

Output:

If you want to get started with scripting, there are a multitude of JavaScript references and tutorials online.

Answer 2

use:

=ARRAYFORMULA(IFERROR(TRIM(SPLIT(IFNA(VLOOKUP(G1:G, 
 SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(UNIQUE(SPLIT(FLATTEN(
 IF(A2:E="",,A2:E&"♂×"&A1:E1&"♀×"&A1:E1)), "×")), 
 "select Col1,max(Col2) 
  where Col2 is not null 
  group by Col1
  pivot Col3"), 
 "offset 1", 0)),,9^9)), "♂"), 2, 0)), "♀"))))

---

update:

=ARRAYFORMULA(ARRAY_CONSTRAIN(IFERROR(TRIM(SPLIT(IFNA(VLOOKUP(G1:G, 
 SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(UNIQUE(SPLIT(FLATTEN(
 IF(A2:E="",,A2:E&"♂×"&A1:E1&"♀×"&A1:E1)), "×")), 
 "select Col1,max(Col2) 
  where Col2 is not null 
  group by Col1
  pivot Col3"), 
 "offset 1", 0)),,9^9)), "♂"), 2, 0)), "♀"))), 9^9, 2))