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

Точное совпадение

Сначала вкратце напомним работу ВПР() - VLOOKUP() с аргументом интервальный_просмотр = ЛОЖЬ (когда ищется значение в ключевом столбце точно соответствующее критерию).

Задача: Для заданного пользователем Товара найти в таблице соответствующую цену и количество (см. файл примера ).

Решение очевидно ( =ВПР(B10;B13:C15;2;0) ) и подробно рассмотрено в статье про ВПР() .

Предположим, что исходная таблица усложнилась - добавился столбец с номерами заказов.

Теперь необходимо искать заданный Товар не по всему столбцу Товар, а лишь в строках, относящегося к заданному пользователем Заказу.

Найдем количество и цену для товара Мандарины в Заказе 2.

В ячейках А21 и В21 введем номер Заказа и наименование товара.

В ячейке С21 введем формулу массива :

=ЕСЛИ(МАКС(ЕСЛИ((Таб1[Заказ]=A21)*(Таб1[Товар]=B21);Таб1[Количество];""));МАКС(ЕСЛИ((Таб1[Заказ]=A21)*(Таб1[Товар]=B21);Таб1[Количество];""));"В заказе нет искомого Товара")

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

Ближайшее ЧИСЛО (ДАТА)

В предыдущей задаче предполагалось, что наименование Товара присутствует в Заказе в единственном экземпляре и точно соответствует критерию поиска. Однако, существует класс задач, когда это не так. Особенно часто встречаются задачи поиска ближайшего ЧИСЛА (если точное значение в ключевом столбце не найдено, то выводится ближайшее).

Примечание . Формулы, созданные для поиска ближайших ЧИСЕЛ, работают и для ДАТ, т.к. даты в MS EXCEL хранятся в числовом формате .

Сначала вкратце напомним работу ВПР() - VLOOKUP() с аргументом интервальный_просмотр = ИСТИНА (когда ищется значение в ключевом столбце точно или приблизительно совпадающее с критерием).

Задача: Для указанной пользователем Даты найти соответствующую ей цену (см. файл примера ).

Решение - формула =ВПР(B10;Табл2.1;2) , где Табл2.1 - таблица в формате MS EXCEL 2007 .

Напомним, что для ВПР() с аргументом интервальный_просмотр = ИСТИНА требуется сортированный по возрастанию ключевой столбец (Дата). При отсутствии в столбце точного совпадения выводится наибольшее значение, которое меньше искомого. При наличии нескольких одинаковых подходящих значений в ключевом столбце, учитывается, то что ниже. Поэтому Цена выведена 220р., а не 240 и не 230.

Теперь необходимо для заданной пользователем Даты найти соответствующую ей цену, но эта Цена и дата должны относится к заданному пользователем Продавцу.

Другими словами, найдем цену на дату, ближайшей к заданной, но только для определенно продавца.

В ячейках А25 и В25 введем Продавца и дату.

Прежде чем писать формулу, определимся, что значит "ближайший". На самом деле можно дать несколько определений, нужный вариант выбирается исходя из условий решаемой задачи.

Вот несколько возможных определений:

1. В терминах функции ВПР() "ближайший" - это наибольшее значение, которое меньше искомого. При наличии нескольких одинаковых подходящих значений в ключевом столбце, учитывается, то что расположено ниже в таблице. Понятно, что такое значение может быть не обязательно ближайшим.

2. Другой вариант: наименьшее значение, которое больше искомого. При наличии нескольких одинаковых значений в ключевом столбце, учитывается, то что выше.

3. Ближайшее, т.е. та дата, которая ближе, чем все остальные (м.б. раньше или позже заданной). Если таких дат несколько, то учитывается нижняя и выводится соответствующая ей цена.

Еще несколько вариантов предложено в файле примера .

Понятно, что для каждого определения ближайшего требуется своя формулы массива (все их можно найти в файле примера ).

Формулы для определения цены, как правило, используют результат формулы определяющей ближайшую дату.

Ближайшее время

Поскольку как и дата, так и время в EXCEL хранятся в числовом формате (см. здесь https://excel2.ru/articles/kak-excel-hranit-datu-i-vremya ), то формулы будут работать и для дат с указанием времени, например 03.12.2018 15:06.

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

На том же листе решена и другая задача: в столбце, содержащих время в часах и минутах, выделяются строки, которые попадают в заданный интервал времени (сортировка не обязательна).