Функция ВПР в Excel отлично справляется с поиском первого подходящего значения в списке. Но что делать, если вам нужны все совпадения, а не только первое? Например, найти все заказы конкретного клиента или все транзакции по определённой статье расходов.
К сожалению, стандартная ВПР на это не способна. Однако есть несколько рабочих способов собрать все результаты в одну таблицу или вывести их списком. Рассмотрим самые практичные из них, от простых комбинаций функций до использования динамических массивов.
Комбинация ВПР и ПОИСКПОЗ для вывода порядкового номера
Самый простой метод — модифицировать запрос так, чтобы ВПР искала не первое, а n-ное совпадение. Для этого нужен вспомогательный столбец, который будет присваивать каждому совпадению уникальный порядковый номер.
- Добавьте слева от вашей исходной таблицы новый столбец «№». В первую его ячейку для искомого значения введите формулу, например:
=ЕСЛИ(A2=$F$2; СЧЁТЕСЛИ($A$2:A2; $F$2); ""). ЗдесьA2— столбец с именами клиентов, а$F$2— ячейка с искомым именем. - Эта формула проставит цифры 1, 2, 3… только напротив строк с нужным клиентом.
- Теперь в таблице результатов используйте комбинацию:
=ЕСЛИОШИБКА(ВПР(СТРОКА()-n; $A$2:$D$100; {номер_столбца_с_данными}; ЛОЖЬ); ""). АргументСТРОКА()-nбудет последовательно подставлять 1, 2, 3... и вытягивать соответствующие строки.
Этот метод нагляден, но требует изменения структуры исходных данных, что не всегда возможно.
Функция ФИЛЬТР для современных версий Excel и Google Таблиц
Если вы используете Microsoft 365, Excel 2026 или Google Таблицы, задача решается одной функцией — ФИЛЬТР. Это самый эффективный и современный способ.
Синтаксис прост: =ФИЛЬТР(диапазон_данных; условие1; [если_нет_данных]).
Например, чтобы получить все строки, где клиент «Иванов», формула будет выглядеть так:=ФИЛЬТР($A$2:$D$100; $B$2:$B$100="Иванов"; "Нет данных")
Функция динамически возвращает весь массив подходящих строк. Если совпадений нет, вы увидите указанное сообщение или пустую ячейку. Это решение не требует вспомогательных столбцов и обновляется автоматически при изменении данных.
Как собрать уникальный список всех найденных значений
Бывает, что нужно не просто отфильтровать строки, а вывести в один столбец, например, все уникальные номенклатурные номера, связанные с заказом. Для этого в современных Excel подойдёт связка ФИЛЬТР и УНИК.
=УНИК(ФИЛЬТР(столбец_с_номенклатурой; столбец_с_номерами_заказов=номер_искомого_заказа))
Эта формула сначала отфильтрует все позиции по нужному заказу, а затем оставит только уникальные значения, убрав повторы.
Решение для старых версий Excel: способы без ФИЛЬТР
Если у вас Excel 2019 или старше, где нет ФИЛЬТР, можно использовать комбинацию ИНДЕКС и ПОИСКПОЗ в формуле массива. Это более сложный, но рабочий вариант.
- Создайте формулу для первого результата:
=ИНДЕКС($C$2:$C$100; НАИМЕНЬШИЙ(ЕСЛИ($B$2:$B$100=$F$2; СТРОКА($B$2:$B$100)-СТРОКА($B$2)+1); СТРОКА(A1))). - Введите её комбинацией Ctrl+Shift+Enter (формула массива — появятся фигурные скобки
{}). - Протяните вниз. Формула будет последовательно выводить 1-е, 2-е, 3-е совпадение. После того как совпадения закончатся, появится ошибка
#ЧИСЛО!, которую можно скрыть с помощью функции ЕСЛИОШИБКА.
Выбор метода зависит от вашей версии Excel и задачи. Для разовых отчётов подойдёт метод с вспомогательным столбцом. Для регулярной автоматизации отчётности в современных условиях берите ФИЛЬТР. А если нужно быстро решить проблему без глубокого погружения в формулы, сохраните себе наш каталог готовых решений, где собраны шаблоны для подобных задач.
