ВПР, как и ГПР являются одними из самых полезных функций в Excel. На моём канале вы можете найти статьи про каждую из них( кликай - ВПР, ГПР).
Однако бывают ситуации, когда поиск требуется произвести по двум и более условиям. Стандартный принцип работы уже не подойдет, и в этой статье я покажу три варианта решения такой нестандартной задачи.
Заранее скажу, что если вы используйте Microsoft Office старее 2019, то можете столкнуться с крайней медленной обработкой ваших формул. Все будет зависеть от:
- Мощности вашего ПК.
- Количества ячеек, которое просматривают нижеприведенные формулы.
- Количества ячеек, в которых прописана данная формула.
Лишь в 2019 офисе(и естественно в офисе 365) исправили алгоритмы работы, благодаря чему можно использовать все формулы массивов без каких-либо ограничений.
Способ Первый - через ЕСЛИ внутри ВПР
=ВПР(D1;ЕСЛИ(B6:B20=D2;A6:C20;"");3;0)
Суть способа - заставить формулу ЕСЛИ(кликни для перехода на видео по формуле) проверять определенные столбцы на соответствие заданному признаку и в случае совпадения становится вторым аргументом в формуле ВПР.
Если у вас старая версия офиса(старее 2019), то для корректной работы формулы потребуется нажать на горячие клавиши CTRL+SHIFT+ENTER одновременно для заключения формулы в массив. В этом случае формула будет дополнительно заключена в скобки {}.
У такого способа есть только один недостаток - он удобен только в случае, когда критериев поиска два. Если критериев больше то потребуется городить большое количество формул ЕСЛИ внутри ЕСЛИ. Переходим ко второму способу.
Способ Второй - ВПР по нескольким критериям с помощью связи ИНДЕКС+ПОИСКПОЗ
На моём канале я уже рассказывал(кликни для перехода на статью) о такой связке, как ИНДЕКС+ПОИСКПОЗ. По моему субъективному мнению она лучше всего подходит для нахождения ячейки по нескольким критериями, если итог поиска не является числом.
=ИНДЕКС(D8:D22;ПОИСКПОЗ(1;(A8:A22=E1)*(B8:B22=E2)*(C8:C22=E3);0))
Если потребуется добавить критериев - просто добавляем еще одну скобку, в которую вписываем столбец в котором будем искать и ячейку с критерием поиска).
И ещё раз напоминаю - если старая версия Office - нужно нажать на горячие клавиши CTRL+SHIFT+ENTER одновременно для заключения формулы в массив {}.
Способ Третий - СУММПРОИЗВ
Я не просто так упомянул в предыдущем способе о том, что он лучший если итог поиска не число а например текст. Если же мы ищем число то, самой рациональной формулой будет СУММПРОИЗ(Сумма произведения).
=СУММПРОИЗВ((A8:A22=$E$1)*(B8:B22=$E$2)*($C$8:$C$22=E3)*(D8:D22))
При применении данной формулы не потребуется нажимать CTRL+SHIFT+ENTER.
Надеюсь моя статья была вам полезна. Спасибо за прочтение.
