CODEWITHSUNDEEP
arraysgoogle-sheetsgoogle-sheets-formulatransposegoogle-query-language
Falcon4ch
Falcon4ch

Reputation: 120

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

I have a sheet that looks like this:

enter image description here

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:

enter image description here

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

Upvotes: 2

Views: 150

Answers (2)

player0
player0

Reputation: 1

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)), "♀"))))

enter image description here

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

enter image description here

Upvotes: 1

CMB
CMB

Reputation: 5173

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:

enter image description here

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

Upvotes: 1

Related Questions