INDEX/MATCH Формула только с одним значением

Question

У меня есть эта таблица ниже. В другой вкладке на том же рабочем листе я хочу извлечь все значения из столбца "имя", которые соответствуют "статусу" как NotFound. Моя формула выглядит так:

=INDEX('nameoftab'!A:A, MATCH("NotFound", 'nameoftab'!C:C, 0))

Тем не менее, формула выплевывает только первое найденное значение - Лору. Как я могу сделать так, чтобы я извлек оставшиеся значения в простой список? Я везде искал и не нашел простого решения, помогите пожалуйста!

Answer 1

=INDEX('nameoftab'!B:B, AGGREGATE(14,6,ROW('nameoftab'!$C$2:$C$10)/('nameoftab'!$C$2:$C$10="NotFound"),ROW(A1))

Эта формула предполагает, что таблица на вашем рисунке находится на листе с именем "nameoftab" и охватывает диапазон A1:C10.

Поместите его туда, где вы хотите, чтобы появилось имя, и скопируйте его, как требуется. Если вы хотите, чтобы он отображался без пробелов вместо сообщения об ошибке, когда вы копируете его за пределы списка имен, которые соответствуют вашим критериям, оберните все это в функцию IFERROR:

=IFERROR(INDEX('nameoftab'!B:B, AGGREGATE(14,6,ROW('nameoftab'!$C$2:$C$10)/('nameoftab'!$C$2:$C$10="NotFound"),ROW(A1)),"")

Обратите внимание: поскольку уравнение 14 в AGGREGATE выполняет вычисления, подобные массиву, избегайте использования полных ссылок на столбцы в массиве. Использование полной ссылки на столбец для INDEX хорошо, так как это не входит в функцию AGGREGATE.

Answer 2

Для решения этой проблемы вам понадобится Формула обратного просмотра:

Как это устроено:

• Я предполагаю, что у вас есть данные в диапазоне A3:C11 на Sheet1 .
• Напишите критерии, не найденные в Sheet2 , A1 .

• Введите эту формулу в Sheet2 , A3 и заполните ее.

  =IF(Sheet1!C3=$A$1,VLOOKUP($A$1,CHOOSE({1,2},Sheet1!C$3:C$11,Sheet1!B3:B$11),2,0),"")

NB

• В приведенной выше формуле E1 является значением поиска. C3:C11 - это столбец, формула ищет значение Lookup, а B3:B11 - это формула столбца, который ищет целевое значение.

• При CHOOSE{1,2} 1 & 2 указатель столбцов учитывает Col C и B И 2 устанавливает индекс столбца, чтобы получить имя из столбца B

Отредактировано:

Если вы хотите вывод, подобный этому, то вы можете использовать формулу массива (CSE), показанную ниже:

{=IFERROR(INDEX(Sheet1!$B$3:$B$11,SMALL(IF($A$1=Sheet1!$C$3:$C$11,ROW(Sheet1!$C$3:$C$11)- MIN(ROW(Sheet1!$C$3:$C$11))+1,""),ROW(A1))), "")}

• Завершите формулу с помощью Ctrl+Shift+Enter и Fill down.

При необходимости измените ссылки на ячейки в формуле.