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

Прежде уясним: ВПР – очень полезная функция, но ею нужно правильно пользоваться. Не читайте эту статью бегло, осознайте весь изложенный материал, а при необходимости — уточните в комментариях.

пример ВПР

Синтаксис функции: =ВПР(искомое значение ; таблица для поиска ; № столбца для вывода ; тип поиска)

Вот описание аргументов:

Аргумент Описание
Искомое значение Та величина, которую мы ищем в таблице с данными
Таблица для поиска Диапазон, в котором содержится искомое значение. Важно: формула ищет значение в крайнем слева столбце массива. И только там
№ столбца для вывода Порядковый номер столбца массива, который нужно вернуть. Внимание: порядковый номер именно в массиве! Смотрите, как я пронумеровал столбцы на рисунке выше
Тип поиска

0 (ЛОЖЬ) – искать точное совпадение

1 (ИСТИНА) – искать приблизительное совпадение

Значение по умолчанию – ИСТИНА!

На рисунке выше я показал, как искал в таблице продажи г-на Соколова с помощью ВПР, формула такая:

=ВПР(I2;B3:D17;3;0)

  1. Искомое значение – указал ссылку на фамилию «Соколов»
  2. Таблица для поиска – выбрал массив B3:D17, в котором и содержатся данные о продажах. Заметили, поиск будет по фамилии, поэтому важно, чтобы столбец с фамилиями стоял в этом массиве первым
  3. № столбца для вывода. Нам нужно вывести продажи Соколова, т.е. значение из столбца D. В массиве B3:D17 эта колонка – третья. Поэтому укажем значение аргумента – 3;
  4. Тип поиска – указываю ноль, чтобы велся точный поиск. Нас интересует именно Соколов, а не кто-то, возможно, с похожей фамилией.

Функция вернула правильный результат из 6-й строки – 258. В 11-й строке так же есть Соколов, но точный поиск ВПР ищет именно первое сверху «попадание», его и считает правильным результатом.

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

Это важно понимать и помнить

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

  • Функция выполняет точный или неточный поиск значения. Какой из этих методов используется – выбираете вы в последнем аргументе. По умолчанию используется неточный поиск
  • При точном поиске результатом будет первое найденное совпадение (самое верхнее)
  • При неточном поиске будет возвращено ближайшее меньшее значение
  • Чтобы неточный поиск работал правильно, таблицу данных следует отсортировать по возрастанию по первому столбцу массива
  • ВПР ищет указанную величину в первом столбце заданного диапазона
  • Формула выводит значение любого столбца найденной строки
  • Если результат не найден – возвращается ошибка #Н/Д
  • ВПР не различает регистры текста. Для него «ВАСЯ», «Вася» и «вася» — одно и то же
  • Если формула будет копироваться в разные ячейки листа, нужно «заморозить» массив во втором аргументе

В этой статье я показал вам, как работает в Эксель функция ВПР, изложил важные особенности ее использования. Следующий практический пост посвятим лайфхакам в использовании ВПР. Не пропустите, там будет использование приблизительного поиска, работа с несколькими условиям, подстановочные символы и другие интересные возможности работы с ВПР. До встречи!