Общая формула
(=TEXTJOIN(", ",TRUE,IF(rng1=E5,rng2,"")))
Резюме
Для поиска и получения нескольких совпадений в списке, разделенном запятыми (в одной ячейке), вы можете использовать функцию IF с функцией TEXTJOIN. В показанном примере формула в F5:
(=TEXTJOIN(", ",TRUE,IF(group=E5,name,"")))
Это формула массива, которую необходимо вводить с помощью Ctrl + Shift + Enter.
В этой формуле используются именованные диапазоны «имя» (B5: B11) и «группа» (C5: C11).
Объяснение
Ядром этой формулы является функция ЕСЛИ, которая «фильтрует» имена в таблице по цвету следующим образом:
IF(group=E5,name,""))
Логический тест проверяет каждую ячейку в названном диапазоне «group» на значение цвета в E5 (в данном случае красный). Результатом является такой массив:
(FALSE;FALSE;TRUE;TRUE;FALSE;FALSE;TRUE)
Этот результат, в свою очередь, используется для фильтрации имен из именованного диапазона "name":
("Matt";"Sally";"Jude";"Aya";"Elle";"Linda";"George")
Для каждого ИСТИНА имя сохраняется, для каждого ЛОЖЬ, ЕСЛИ возвращает пустую строку ("").
Результатом IF выглядит этот массив:
("";"";"Jude";"Aya";"";"";"George")
который входит в функцию TEXTJOIN как text1.
TEXTJOIN настроен на использование запятой в качестве разделителя и игнорирование пустых значений. Конечный результат - это текстовая строка:
"Джуд, Айя, Джордж"
Несколько условий
Вы не можете использовать функции И или ИЛИ в такой формуле массива, потому что они возвращают только один результат. Вы можете использовать такую логическую логику для AND:
=TEXTJOIN(", ",TRUE,IF((condition1)*(condition2),name,""))
Подробнее об этом здесь.
