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