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