Допустим, ваша входная ячейка - A1, а выходные ячейки - A3 для имени и B3:B .. для выходного диапазона (конец диапазона B опускается до максимального числа результатов, которое вы ожидаете увидеть),
Для A3 положите формулу =A1 .
Для B3 поставить формулу
=IFERROR(INDEX($N$1:$N$99,SMALL(IF($J$1:$J$99=$A$3,ROW($N$1:$N$99)-ROW($N$1)+1),ROWS($N$1:$N1))),"")
Это формула массива, и ее необходимо вводить с помощью комбинации клавиш Control - Shift - Enter . Затем вы можете скопировать его в конец выходного диапазона.
Обратите внимание, что формула жестко запрограммирована для списка ввода, содержащего не более 99 строк. Вы можете изменить это на любую длину, которая вам нужна. Хотя можно было бы ссылаться на весь столбец (J:J и N:N), это может привести к снижению производительности, которого вы, вероятно, хотите избежать.
Как работает формула
Работая изнутри, формула сначала сравнивает имя, для которого должен быть выполнен поиск (ячейка A3), с полным списком имен (до 99 имен в диапазоне J1:J99). Это сравнение показано в строке 6 функции, показанной ниже.
Результатом этого сравнения является массив со значениями True для совпадения и значениями False для несоответствия, например, {False, False, False, False, True, True, False и т.д.}.
Затем проводится сравнение между этим массивом и массивом, который можно рассматривать как "номера строк" в списке имен: {1, 2, 3, 4, 5, 6, ... 99}. Это сравнение выполняется оператором IF в строках 6-8 таблицы формул.
Сравнение поэлементно. Если элемент массива сравнения имен равен True, тогда IF возвращает соответствующий ему номер строки; если элемент равен False, IF возвращает FALSE. Используя два приведенных выше примера массивов, результатом оператора IF будет {False, False, False, False, 4, 5, False, ...}.
Продолжая, функция SMALL (начиная со строки 8 схемы функции) используется для получения k-го наименьшего элемента этого нового массива из IF. "K" в этом случае предоставляется выражением ROWS ($ N $ 1:$ N1), которое будет просто считать от 1 до 99, когда вся формула копируется из строки 1 в строку 99 (ROWS ($ N $ 1) :$ N1) = 1, ROWS ($ N $ 1:$ N2) = 2 и т.д.).
Итак, SMALL сначала найдет наименьший элемент массива, созданного IF, игнорируя элементы False. Другими словами, он вернет номер первой строки, где сравниваемое имя совпадает с именем в списке поиска имен. В нашем примере это число 4, как показано в 6-м столбце таблицы ниже.
Шаг завершения использует INDEX для поиска значений, чтобы выбрать элемент, соответствующий номеру строки, который был вычислен. В этом случае 4-й элемент в примере списка цветов для вопроса - "желтый". (IFERROR гарантирует, что пустое значение отображается, когда формуле не удается найти совпадение.)
Таков был результат первого экземпляра полной формулы. Когда он копируется в следующую ячейку, единственное, что изменяется в расчете, это значение "k" для функции SMALL, которое увеличивается до 2. И второй наименьший номер строки, который был найден, равен 5, что дает значение "белый".
