ВПР в Excel и Google Таблицах: как работает функция, примеры и частые ошибки

ВПР (в английской версии — VLOOKUP) — функция Excel и Google Таблиц, которая находит значение в первом столбце таблицы и возвращает данные из нужного столбца той же строки. Проще говоря, ВПР подставляет цену к артикулу, телефон к фамилии или город к номеру заказа — без ручного поиска и копирования. Ниже — как устроена функция, пошаговый пример, частые ошибки и чем её заменить.

Содержание

• Как устроена функция ВПР
• Пример: подставляем цены из прайса в заказ
• ВПР в Google Таблицах
• Частые ошибки и как их исправить
• Чем заменить ВПР
• Где научиться работать с таблицами

---

Как устроена функция ВПР

У ВПР четыре аргумента. В русской версии Excel они разделяются точкой с запятой:

=ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)

• Искомое значение — что ищем: артикул, фамилию, номер заказа. Обычно это ссылка на ячейку, например A2.
• Таблица — диапазон, в котором ищем. Искомое значение должно стоять в его первом (левом) столбце — это главное правило ВПР.
• Номер столбца — из какого столбца диапазона вернуть результат. Считаем слева направо внутри диапазона, начиная с единицы.
• Интервальный просмотр — тип поиска. ЛОЖЬ (или 0) — точное совпадение, его ставят почти всегда. ИСТИНА (или 1) — приблизительное, оно нужно для шкал вроде «скидка от суммы заказа» и работает только при отсортированном первом столбце.

Пример: подставляем цены из прайса в заказ

Возьмём типичную рабочую задачу. На листе «Прайс» лежит справочник: в столбце A — артикулы, в B — названия товаров, в C — цены. На листе «Заказ» клиент прислал список артикулов, и к каждому нужно подставить цену.

1. На листе «Заказ» встаньте в ячейку рядом с первым артикулом, например в B2.
2. Введите формулу: =ВПР(A2;Прайс!$A$2:$C$200;3;ЛОЖЬ).
3. Нажмите Enter — в ячейке появится цена товара с артикулом из A2.
4. Протяните формулу вниз за маркер в правом нижнем углу ячейки — цены подставятся ко всем строкам заказа.

Что здесь важно. Знаки $ закрепляют диапазон прайса: без них при протягивании он «съедет» вниз, и часть артикулов перестанет находиться. Цифра 3 — это третий столбец диапазона, то есть цена. Если нужно название товара, поставьте 2.

Чтобы вместо ошибки для отсутствующих артикулов выводилась понятная подпись, оберните формулу в ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ВПР(A2;Прайс!$A$2:$C$200;3;ЛОЖЬ);"нет в прайсе")

ВПР в Google Таблицах

В Google Таблицах ВПР работает так же, как в Excel, с теми же четырьмя аргументами. Отличий три.

• Разделитель аргументов зависит от региональных настроек файла: в русских настройках — точка с запятой, в английских — запятая. Настройки меняются в меню «Файл» → «Настройки».
• Данные из другого файла в Excel подключаются ссылкой на открытую книгу, а в Google Таблицах — функцией IMPORTRANGE: сначала она забирает диапазон из другой таблицы, а ВПР ищет уже в нём.
• Совместная работа. Если прайс ведёт коллега в общей таблице, ВПР сразу подхватывает его правки — пересылать файлы не нужно.

Частые ошибки и как их исправить

• #Н/Д — значение не найдено. Чаще всего в ячейках есть лишние пробелы или артикул в одной таблице записан числом, а в другой — текстом. Лишние пробелы убирает функция СЖПРОБЕЛЫ, текст в число превращает «Данные» → «Текст по столбцам» или умножение на 1.
• Неправильные значения вместо ошибки. Почти всегда это пропущенный последний аргумент: по умолчанию ВПР ищет приблизительное совпадение и «промахивается» на неотсортированных данных. Ставьте ЛОЖЬ.
• #ССЫЛ! — номер столбца больше, чем столбцов в диапазоне. Например, диапазон из трёх столбцов и номер 4.
• Формула работает только в первой строке. Диапазон таблицы не закреплён знаками $ и сдвинулся при протягивании.
• Нужно искать по столбцу, который стоит правее результата. ВПР «смотрит» только вправо. Либо переставьте столбцы, либо используйте ИНДЕКС и ПОИСКПОЗ или ПРОСМОТРX — о них ниже.

Чем заменить ВПР

ПРОСМОТРX есть в Excel из Microsoft 365 и в Excel 2021, а в Google Таблицах та же функция называется XLOOKUP. Она ищет в любую сторону, по умолчанию ищет точное совпадение и не зависит от номера столбца:

=ПРОСМОТРX(A2;Прайс!$A$2:$A$200;Прайс!$C$2:$C$200;"нет в прайсе")

ИНДЕКС + ПОИСКПОЗ работает в любой версии Excel и в Google Таблицах. ПОИСКПОЗ находит номер строки, ИНДЕКС достаёт значение из нужного столбца:

=ИНДЕКС(Прайс!$C$2:$C$200;ПОИСКПОЗ(A2;Прайс!$A$2:$A$200;0))

Если вы только начинаете, освойте сначала ВПР: она встречается в рабочих файлах чаще всего, и на ней проще понять саму идею поиска по таблице. Когда пойдёт уверенно, переходите к ПРОСМОТРX.

Где научиться работать с таблицами

ВПР — одна из тем онлайн-курса OnStudy «Excel и Google Таблицы с нуля до Pro». В курсе 54 урока в 10 модулях: функцию разбираем на двух практических задачах — формирование заказа и обработка заявки, а дальше идут сводные таблицы, диаграммы, макросы, отдельный модуль по Google Таблицам и Power Query. Уроки остаются у вас и после программы.

Если пока нужно разобраться с одной темой, загляните в материалы о сводных таблицах и о выборе между Excel и Google Таблицами.