Сопоставьте заголовок столбца (неопределенного местоположения) с содержимым столбца и получите все заголовки строк

Question

Я использую Excel 2016 на Windows 8.1.

Я хочу иметь возможность соответствовать двум критериям, где один критерий является заголовком столбца, а другой - содержимым ячейки, которое может встречаться в этом столбце любое количество раз, и возвращать в качестве результатов несколько заголовков строк.

В моем примере я хочу иметь возможность вводить название фрукта (заголовки столбцов) и качество («хорошее» или «плохое») в ячейках B6 и B7 и получать любые характеристики (заголовки строк), соответствующие этому фрукту и качество. Если я дам ему «яблоки» и «хорошо», я хочу, чтобы он возвращал «внешний вид» и «вкус» (а не «цену»), например.

Ранее я использовал Index Match и слышал об использовании формул массива для работы с диапазонами ячеек. Но я не могу понять, как объединить все это - тем более, что все учебники, которые я нашел, были о том, как сопоставить заголовок строки и заголовок столбца и найти результирующие ячейки. Кроме того, я не хочу «жестко кодировать» ссылку на заданный заголовок столбца, потому что я не всегда хочу совпадать с «яблоками».

Я пробовал сводную таблицу, и она была не очень элегантной (мои реальные данные содержат 5 «качественных» элементов, а не только 2).

Снимок экрана примера таблицы

   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

Это вариант того, что предложил @Scott Craner, но он может сработать для вас.

Я добавил заполнители для дополнительных качеств. Поместите эту формулу в B11, а затем перетащите вниз на количество строк, равное количеству ваших качеств.

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

Если вам не нравится внешний вид Results1 и вы хотите, чтобы значения находились в одной ячейке, поместите эту формулу в B18 и скройте строки с 11 по 15.

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

Замените все, что вы хотите в качестве разделителя для «//».

Answer 2

Это будет перебирать ячейки и возвращать все номера строк, которые соответствуют INDEX, а затем будет возвращать значение.

Поместите это в 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))),"")

И скопировать достаточно, чтобы учесть все характеристики.