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

Question

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

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

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

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

Я попробовал использовать сводную таблицу, но она оказалась не очень элегантной (мои реальные данные содержат 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

Это позволит выполнить итерацию по ячейкам и вернуть все номера строк, соответствующие ИНДЕКСУ, который затем вернет значение.

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

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