По умолчанию функция ВПР (VLOOKUP) работает только с одним условием, однако в работе не раз может возникать ситуация, когда необходимо реализовать функционал ВПР по нескольким критериям. В данной статье будут рассмотрены два способа реализации такого вопроса. В конце статьи вы найдете ссылку на файл с данными из этой статьи.
Для наглядности возьмем такой пример. Имеются данные по неким индексам, которые в зависимости от частоты принадлежат к определенному оператору и определенному региону. Необходимо определить регион на основании индекса и частоты.
Чтобы решить эту задачу можно воспользоваться сочетанием функций ВПР + ЕСЛИ или же сочетанием функций ИНДЕКС + ПОИСКПОЗ. Про совместное использование функций ИНДЕКС и ПОИСКПОЗ более подробно читайте тут.
Первый способ: ВПР + ЕСЛИ
В данном случае, формула будет выглядеть следующим образом:
=ВПР(H4;ЕСЛИ(C6:C1385>=I4;A6:F1385;"");6;0)
=ВПР(*индекс*;ЕСЛИ(*диапазон_частота_до*>=*частота*;*диапазон_таблицы*;"");*номер_столбца_с_данными_о_регионе*;0)
Принцип действия формулы
В качестве первого аргумента функции ВПР передается первое искомое значение, которое находится в крайнем левом положении в таблице. В функцию ЕСЛИ передается виртуальная таблица, где проверяется условие о том, что “Частота До” больше указанной нами частоты. Каждая строка, в которой результат условия был ИСТИНА, возвращает саму строку, в случае, если условие было со значением ЛОЖЬ – возвращается “” (пусто). Далее, обрезанная виртуальная таблица (в ней теперь находятся все значения, которые подпадают под условие) подставляется во второй аргумент.
Стоит отметить, что в данном случае под условие попадает большое количество строк, но, так как значения частоты в одном индексе идут в порядке возрастания, ВПР вернет первое выполненное условие, что будет корректно.
Второй способ: ИНДЕКС + ПОИСКПОЗ
При использовании этих двух функций, формула будет выглядеть более массивной, однако ее функционал будет выше, так как в таком случае будет задаваться и верхняя и нижняя граница диапазона. Формула следующая:
=ИНДЕКС(F5:F52;ПОИСКПОЗ(1;(A5:A52=H4)*(B5:B52<=I4)*(C5:C52>=I4);0))
=ИНДЕКС(*диапазон_столбца_регион*;ПОИСКПОЗ(1;(*диапазон_индекс*=*индекс*)*(*диапазон_частота_от*<=*частота*)*(*диапазон_частота_до*>=*частота*);0))
Принцип действия формулы
Такой способ также работает через виртуальные таблицы. Суть в том, что в качестве первого аргумента функции ИНДЕКС передается столбец, значение в котором нам нужно получить. Вторым аргументом является функция ПОИСКПОЗ, в которой происходит поиск цифры “1” в виртуальной таблице, в которой существуют три столбца: индекс, частота от, частота до. Если значение совпадает – возвращается 1, иначе – 0.
Для наглядности прилагаю скриншот с примером работы виртуальной таблицы. В приложенном файле вы сможете найти эту таблицу со всеми формулами.
Файл для скачивания доступен по ссылке здесь: https://aboutdatum.ru/excel/functions/vpr-po-neskolkim-uslovijam/