Резюме
Чтобы извлечь несколько совпадений в отдельные ячейки в отдельные строки, вы можете использовать формулу массива на основе ИНДЕКС и МАЛЕНЬКИЙ. В показанном примере формула E5 выглядит так:
(=IFERROR(INDEX(names,SMALL(IF(groups=E$4,ROW(names)-MIN(ROW(names))+1),ROWS($E$5:E5))),""))
Это формула массива, и ее нужно вводить с помощью Control + Shift + Enter.
После ввода формулы в первую ячейку перетащите ее вниз и поперек, чтобы заполнить другие ячейки.
Объяснение
Примечание: в этой формуле используются два именованных диапазона: «имена» относятся к C4: C11, а «группы» относятся к B4: B11. Эти имена также определены на снимке экрана выше.
Суть этой формулы такова: мы используем функцию МАЛЕНЬКИЙ, чтобы получить номер строки, соответствующий «n-му совпадению». Получив номер строки, мы просто передаем его в функцию-функцию ИНДЕКС, которая возвращает значение в этой строке.
Хитрость в том, что SMALL работает с массивом, который динамически создается IF в этом бите:
IF(groups=E$4,ROW(names)-MIN(ROW(names))+1)
Этот фрагмент кода проверяет "группы" именованного диапазона на предмет значения в E4. Если он найден, он возвращает «нормализованный» номер строки из массива номеров строк, созданного с помощью этой части формулы:
ROW(names)-MIN(ROW(names))+1
Результатом является массив, содержащий номера строк, где есть совпадения, и FALSE, где нет. Массив выглядит примерно так:
(1; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; ЛОЖЬ; 6; ЛОЖЬ)
Этот массив переходит в МАЛЕНЬКИЙ. Значение k для МАЛЫЙ (nth) происходит из расширяющегося диапазона:
ROWS($E$5:E5)
При копировании в таблицу результатов диапазон расширяется, вызывая приращение k (nth). Функция SMALL возвращает каждый соответствующий номер строки, который передается функции INDEX как row_num, с именованным диапазоном «names» в качестве массива.
Обработка ошибок
Когда ROWS возвращает несуществующее значение k, SMALL выдает ошибку #NUM. Это происходит после того, как произошли все совпадения. Чтобы подавить ошибку, мы используем ЕСЛИОШИБКА, чтобы перехватить ошибку и вернуть пустую строку ("").
