Такие замечательные функции, как ВПР, ПОИСКПОЗ+ИНДЕКС, ГПР позволяют по известному критерию найти только одно значение из списка: самое первое при просмотре сверху вниз для вертикальных таблиц или самое первое при просмотре слева направо при просмотре слева направо. А что делать, если нужно получить не одно, а все значения. Например, выбрать для каждой компании объекты, которые ей подчиняются и не с помощью фильтрации, а именно по формулам. Найдем ответ и на эту задачу!

Решение этой задачи будет с использованием формулы массива, поэтому необходимо заранее выделить предполагаемый диапазон ячеек (можно с запасом) и написать формулу, завершив её формулой массива, т.е. нажав клавиши Ctrl+Shift+Enter:

Сперва в ответ получаем значения из таблицы, а когда они заканчиваются, то сообщение об ошибке. Подобное, как и всегда, можно убрать с применением функции ЕСЛИОШИБКА.

Разберем формулу “по косточкам”:

• $C$2:$C$16 – диапазон ячеек-результата, т.е. объекты.

• НАИМЕНЬШИЙ(Массив;K) – возвращает k-е наименьшее значение в множестве данных:
  Массив определяется по формуле: ЕСЛИ(D2:D16=$F$2;СТРОКА(D2:D16)-СТРОКА($D$1);””), т.е. проверяется в ячейках D2:D16 соответствие выбранной компании (критерий) в ячейке F2, а затем определяются позиции (номера по порядку) в выделенной области, но так как диапазон выделен без заголовков (с ячейки D2), то необходимо скорректировать на строку-заголовок, т.к. СТРОКА($D$1), а в случае если совпадение критерия не найдено, то позиция не определяется – “”.
  K – позиция в диапазоне данных.

Окончательно формула будет выглядеть:

В этом случае, порядок расположения объектов следует сверху вниз. Если требуется обратный порядок, т.е. снизу вверх, то функцию НАИМЕНЬШИЙ надо заменить на НАИБОЛЬШИЙ:

В случае, если ответ необходимо получать данные то одного столбца, то другого, то необходимо расширить массив в функции ИНДЕКС и определить номер столбца с помощью функции ПОИСКПОЗ: