ПОИСК

ВПР по двум (и более) критериям

Предположим что Вы директор по продажам.
И у Вас есть вот такой ежедневный отчёт по продажам ваших менеджеров:

Из него Вам необходимо узнать сколько упаковок сыра продал Иванов. Понятно что ВПР тут не поможет, она просто вернёт значение из указанного столбца и первой сверху строки совпадения по фамилии (или по продукту).

ПРОБЛЕМА: Как искать значения по двум (и более) критериям?

РЕШЕНИЕ: Формула массива (вводится нажатием Ctrl+Shift+Enter):

=ИНДЕКС(C2:C6;ПОИСКПОЗ(E2&G2;A2:A6&B2:B6;0))

В английской версии:

=INDEX(C2:C6,MATCH(E2&G2,A2:A6&B2:B6,0))

КАК ЭТО РАБОТАЕТ: Амперсанд & сцепляет (конкатенирует) искомые значения "Иванов" и "Сыр" в одно "ИвановСыр" и просматриваемый массив A2:A6 и B2:B6 в "ИвановМолоко":"ПетровРыба":"СидоровКефир" и т.д.

Функция ПОИСКПОЗ находит номер строки вхождения точного соответствия "ИвановСыр" в получившемся массиве, функция ИНДЕКС возвращает "Кол-во" из соответствующей строки таблицы.

МИНУСЫ: Конкатенация массивов сильно "утяжеляет" формулу. На больших массивах будет длительный пересчёт, на очень больших - возможно зависание файла (зависит от параметров компьютера, в первую очередь от объёма оперативной памяти, потому как массив, получившийся в результате конкатенации хранится не на листе, а в памяти).

ОБЛАСТЬ ПРИМЕНЕНИЯ: Любая версия Excel

ПРИМЕЧАНИЯ: Так же можно искать по трём, четырём и более (неограниченно) критериям.