Поиск наименования по 2 критериям (ВПР)⁠⁠

Задача такая: По наименованию бренда и типу упаковки найти цену товара и затем рассчитать общую сумму от цены и объема:

Основную сложность здесь представляет поиск цены, так как надо искать и по бренду, и по упаковке. Есть 2 способа решения: 1) простой, развернутый, с доп.столбцами, и 2) компактный, но требующий определенных навыков работы.

Способ 1

Были бы исходные данные только в одном столбце (и бренд, и упаковка), искать можно было бы обычным ВПР. Но данные в 2-х столбцах. Поэтому для начала объединим бренд и упаковку в одно наименование:

1. Создадим новый столбец (С) с формулой: =А3&В3.

2. Аналогичный столбец создадим и в маленькой таблице:

3. Теперь, когда искомые значения готовы, с помощью ВПР можно выполнить поиск. Создаем еще один новый столбец (D) с формулой: =ВПР(C3;$N$3:$O$8;2;0), где

С3 — искомое значение, $N$3:$O$8 — диапазон с таблицей поиска,

2 — столбец для вывода информации, 0 — точный поиск:

4. Теперь, когда цена найдена, можно рассчитать конечные суммы:

В общем, все! Все найдено, все подсчитано. В несколько шагов, правда. Продуктивно. Но не изящно.

Поэтому предлагаю

Способ 2.

Сразу и поиск, и подсчет реализуем в окончательных столбцах. При этом принцип будет аналогичный: исходные данные требуют объединения (&).

Итак, действия:

1. Объединить 2 ячейки в момент поиска с указанием также 2-х объединенных столбцов для поиска может функция ПОИСКПОЗ. Для этого связку А3&B3 помещаем как искомое значение, а массивом поиска станет такая же связка J3:J8&K3:K8. Вот так:

=ПОИСКПОЗ(A3&B3; J3:J8&K3:K8; 0)

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

=ИНДЕКС(J3:L8;ПОИСКПОЗ(A3&B3;J3:J8&K3:K8;0);3)

где

J3:L8 — таблица с ценами,

3 — номер столбца с ценой:

3. Для завершения формулы осталось только дописать *С3 (умножить на объем в январе) и проставить закрепления ($) во все диапазоны (обратите внимание, закрепление не везде абсолютное):

4. ОЧЕНЬ ВАЖНЫЙ ПУНКТ!

Если у вас Excel не 365, а другой -  ничего, что сделано выше, работать не будет, если не выполнить теперь этот шаг!

Обычные формулы ПОИСКПОЗ и ИНДЕКС не могут поддерживать связки с такими объединениями (&), поэтому завершать ввод формулы будем не [Enter], а [Ctrl]+[Shift]+[Enter]. Это формула массива, ее можно вводить только так! Иначе будет ошибка. И при любых изменениях формулы заканчиваем редактирование тоже сочетанием [Ctrl]+[Shift]+[Enter].

Если у вас Excel 365, нажимайте просто [Enter], там массивы уже более современные.

А вот копировать формулу (растягивать по столбцам) можно уже как обычно. Полностью законченная таблица выглядит так:

Все расчеты при этом получилось сделать в нужных столбцах без добавления дополнительных столбцов.