Часть 1. Немного теории

ВПР (VLOOKUP)

На первый взгляд функция выглядит сложной, но это не так – нужно запомнить всего три простых правила:

1. ВПР ищет значение сверху вниз;

2. ВПР ищет в первом (крайнем левом) столбце диапазона;

3. Если у Вас данные не отсортированы по алфавиту (возрастанию) и если вы не уверены в результатах поиска – не используйте приблизительное совпадение.

Схематически работу функции можно изобразить так:

Давайте рассмотрим аргументы функции:

Искомое_значение – значение, которое нам нужно найти в первом столбце диапазона.

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

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

Интервальный_просмотр – аргумент, который определяет, нужно ли нам искать точное совпадение (ЛОЖЬ) или же приблизительное (ИСТИНА). Как говорилось выше – при приблизительном совпадении данные должны идти по алфавиту (возрастанию).

Часть 2. Советы из практики

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

Автоматическая смена номера столбца

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

Формула может иметь приблизительно такую конструкцию:

=ВПР(G3;$B$3:$D$38;ПОИСКПОЗ($H$2;$B$2:$D$2;0);ЛОЖЬ)

Таким образом при смене названия столбца – будут подтягиваться данные по новому условию.

Обратный режим

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

В этом случае формула будет иметь примерно такой вид:

=ВПР(E4;ВЫБОР({1;2};$C$4:$C$39;$B$4:$B$39);2;ЛОЖЬ)

Таким образом мы «обманули» функцию ВПР, указав, что первым столбцом в нашем массиве является столбец «C», вторым – «B».

Только экспериментируя с формулами Excel Вы можете открыть всё новые и новые грани этого мощного инструмента! Не бойтесь делать новые шаги – и у Вас всё получится!

Еще больше о ВПР — на нашем курсе

Автор: Михаил Беленчук, тренер DATA bi

Узнайте больше про полную программу "EXCEL: бизнес-анализ и прогнозирование"