Combine um cabeçalho de coluna (de localização não específica) com o conteúdo da coluna e obtenha todos os cabeçalhos de linha

Question

Estou usando o Excel 2016 no Windows 8.1.

Quero ser capaz de corresponder a dois critérios, onde um critério é um cabeçalho de coluna e o outro é o conteúdo da célula que pode ocorrer inúmeras vezes nessa coluna, e retornar vários cabeçalhos de linha como resultados.

No meu exemplo, quero poder inserir o nome de uma fruta (os cabeçalhos das colunas) e uma qualidade ('boa' ou 'ruim') nas células B6 e B7 e receber quaisquer características (cabeçalhos de linha) que correspondam a essa fruta e qualidade. Se eu der “maçãs” e “bom”, quero que retorne “aparência” e “sabor” (e não “preço”), por exemplo.

Já usei Index Match antes e ouvi falar do uso de fórmulas de matriz para trabalhar com intervalos de células. Mas não consigo descobrir como combinar tudo isso - especialmente porque todos os tutoriais que encontrei tratam de como combinar um cabeçalho de linha e um cabeçalho de coluna e encontrar as células resultantes. Além disso, não quero 'codificar' uma referência a um determinado cabeçalho de coluna porque talvez nem sempre queira corresponder 'Maçãs'.

Eu tentei uma tabela dinâmica e ela não era muito elegante (meus dados reais têm 5 itens de 'qualidade', não apenas 2).

Captura de tela da tabela de exemplo

   A           B         C        D         E        F
1            | Apples | Bananas | Lemons | Mangos | Oranges
-----------------------------------------------------------
2 Appearance | good   | good    | bad    | bad    | good
 ----------------------------------------------------------
3 Flavour    | good   | good    | bad    | good   | good
 ----------------------------------------------------------
4 Price      | bad    | good    | good   | bad    | good
----------------------------------------------------------
5            |        |         |        |        | 
----------------------------------------------------------  
6 Fruit:     | apples |         |        |        |
----------------------------------------------------------
7 Quality:   | good   |         |        |        |             
----------------------------------------------------------
8            |        |         |        |        |     
----------------------------------------------------------  
9 Results:   |        |         |        |        |         
----------------------------------------------------------

Answer 1

Esta é uma variação do que @Scott Craner propôs, mas pode funcionar para você.

Adicionei espaços reservados para qualidades adicionais. Coloque esta fórmula em B11 e arraste para baixo um número de linhas igual ao número de qualidades que você possui.

=IF(INDEX($B2:$F2,,MATCH($B$8,$B$1:$F$1))=$B$9,$A2,"")

Se você não gosta da aparência de Resultados1 e realmente deseja que os valores fiquem em uma célula, coloque esta fórmula em B18 e oculte as linhas 11 a 15.

=IF(B11="","",B11&"//") & IF(B12="","",B12&"//") & IF(B13="","",B13&"//") & IF(B14="","",B14&"//") & IF(B15="","",B15&"//")

Substitua o que quiser como separador por "//".

Answer 2

Isso irá percorrer as células e retornar todos os números de linha que correspondem ao INDEX, que retornará o valor.

Coloque isso em B9:

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW($A$2:$A$4)/(($B$2:$F$4=$B$7)*($B$1:$F$1=$B$6)),COLUMN(A:A))),"")

E copie o suficiente para levar em conta todas as características.