Разбирайся. Настраивай. Контролируй.Проверено лично
Свой компьютер. Своя сеть. Свой сервер.
Get Own Tech. Explore. Repair. Build. Improve. Experiment.
Автоматизация

Функция ВПР в Excel: пошаговая инструкция с примерами

Функция ВПР в Excel: пошаговая инструкция на примере прайса и заказа, ЛОЖЬ и ИСТИНА, ВПР из другого листа и книги, ошибки #Н/Д и замена на ПРОСМОТРX.

17 минут чтения Обсудить
Таблица Excel с формулой ВПР, которая подставляет цену из прайса в заказ
Проверено: октябрь 2026, Excel 2021 (16.0.17002.20000), Windows 10. Все цифры - собственные замеры, скриншоты с реальной установки.
Содержание статьи
  1. Как работает ВПР в Excel
  2. Функция ВПР в Excel: пошаговая инструкция на примере
  3. ЛОЖЬ или ИСТИНА: где легко ошибиться
  4. Зачем знаки $ при протягивании
  5. ВПР в Excel из другой таблицы
  6. Почему ВПР выдаёт #Н/Д и другие ошибки
  7. Функция ЕСЛИ в Excel с ВПР
  8. Excel: ВПР и сводные таблицы
  9. Ограничения ВПР
  10. Когда лучше ИНДЕКС+ПОИСКПОЗ или ПРОСМОТРX
  11. ВПР в Google Таблицах и LibreOffice Calc
  12. Частые вопросы

Функция ВПР в Excel подставляет данные из одной таблицы в другую: по артикулу находит в прайсе цену, по табельному номеру находит фамилию, по коду клиента находит телефон. Ниже пошаговая инструкция с примерами: разберём формулу по аргументам, соберём заказ по прайсу с нуля и посмотрим, почему ВПР выдаёт #Н/Д и другие ошибки.

Все формулы и результаты в статье я проверил в Microsoft Excel 2021 (сборка 16.0.17002.20000) с русским интерфейсом, картинки сняты прямо из него. Учебный файл со всеми примерами можно скачать и повторять шаги вместе со статьёй.

Коротко

  • Формула: =ВПР(что_ищем; где_ищем; номер_столбца; ЛОЖЬ). Искать ВПР умеет только в первом столбце таблицы, а вернуть может любой столбец правее.
  • Четвёртый аргумент почти всегда должен быть ЛОЖЬ (или 0). Если его не указать, Excel ищет приблизительно и вместо ошибки может молча подставить чужое значение.
  • Знаки $ в адресе таблицы обязательны, если формулу протягивать вниз. Без них диапазон съезжает, и часть строк получает #Н/Д.
  • Чаще всего #Н/Д дают лишние пробелы и числа, сохранённые как текст. Помогают СЖПРОБЕЛЫ, приведение к числу и ЕСЛИОШИБКА.
  • ПРОСМОТРX (Excel 2021, 2024 и Microsoft 365, Google Таблицы, LibreOffice 24.8 и новее) ищет в любую сторону и по умолчанию точно. Если он есть, им удобнее.

Как работает ВПР в Excel

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

Формула ВПР в Excel по аргументам

Вывод
=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
Аргумент Что указать Пример
искомое_значение Что ищем: ячейку или значение в кавычках A2 или "A-104"
таблица Диапазон, в первом столбце которого лежит искомое Прайс!$A$2:$D$9
номер_столбца Какой по счёту столбец таблицы вернуть. Первый столбец таблицы имеет номер 1 3 (цена)
интервальный_просмотр ЛОЖЬ (или 0) для точного совпадения, ИСТИНА (или 1) для приблизительного. Можно не указывать, тогда будет ИСТИНА ЛОЖЬ

Аргументы в русском Excel разделяются точкой с запятой, а в английском запятой. Одна и та же формула из моего примера в двух видах:

Вывод
=ВПР(A2;Прайс!$A$2:$D$9;2;ЛОЖЬ)
=VLOOKUP(A2,Прайс!$A$2:$D$9,2,FALSE)

Если скопировали формулу из англоязычной инструкции и Excel не принимает её, замените запятые на точки с запятой и названия функций на русские. Разделитель берётся из региональных настроек Windows: на моём компьютере десятичный разделитель запятая, разделитель списков точка с запятой.

Функция ВПР в Excel: пошаговая инструкция на примере

Задача: есть прайс-лист с артикулами, названиями, ценами и остатками. Нужно сделать заказ, в котором достаточно вписать артикул и количество, а название и цена подставятся сами.

Шаг 1. Подготовьте прайс

Прайс лежит на листе «Прайс», в диапазоне A1:D9:

Артикул Товар Цена, ₽ Остаток
A-101 Кабель HDMI 2 м 450 12
A-102 Мышь беспроводная 890 0
A-103 Клавиатура 1 490 5
A-104 Флешка 64 ГБ 690 20
A-105 Удлинитель 5 розеток 1 150 3
A-106 Коврик для мыши 250 0
A-107 Веб-камера 2 390 7
A-108 Наушники 1 790 4

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

Шаг 2. Напишите первую формулу

На листе «Заказ» в столбце A стоят артикулы, в столбце D количество. Встаньте в B2 и введите:

Вывод
=ВПР(A2;Прайс!$A$2:$D$9;2;ЛОЖЬ)

Читается она так: возьми артикул из A2, найди его в первом столбце диапазона A2:D9 на листе «Прайс», верни значение из 2-го столбца этого диапазона (название товара), ищи только точное совпадение. Для цены в C2 формула та же, меняется только номер столбца:

Вывод
=ВПР(A2;Прайс!$A$2:$D$9;3;ЛОЖЬ)

Номер столбца считается от начала выделенной таблицы, а не от столбца A листа. Если бы таблица начиналась со столбца C, номер 2 означал бы столбец D.

Набирать адрес таблицы руками не обязательно. Когда дойдёте до второго аргумента, щёлкните ярлычок листа «Прайс» и выделите мышью диапазон A2:D9: Excel сам подставит Прайс!A2:D9. Затем нажмите F4, и адрес превратится в Прайс!$A$2:$D$9.

Шаг 3. Протяните формулу вниз

Выделите B2:C2 и потяните за маркер в правом нижнем углу до последней строки заказа. Вот как это выглядит в режиме показа формул (Ctrl+Ё, клавиша слева от единицы, или вкладка «Формулы» → «Показать формулы»):

Формулы ВПР на листе «Заказ» в режиме показа формул
Ссылка на артикул меняется по строкам, адрес прайса со знаками $ остаётся на месте

Ссылка на артикул сдвигается (A2, A3, A4…), а адрес прайса остаётся одним и тем же. Сумму считаем обычным умножением =C2*D2, итог через =СУММ(E2:E5). В Excel получилось так:

Готовый заказ: название и цена подставлены через ВПР
Заказ на 7 140 ₽, столбец «Наличие» считается через ЕСЛИ и ВПР

Теперь достаточно поменять артикул в столбце A, и строка пересчитается сама. Если в прайсе изменится цена, заказ тоже обновится.

ЛОЖЬ или ИСТИНА: где легко ошибиться

Четвёртый аргумент ВПР необязательный, и в этом главная ловушка. По справке Microsoft, если его не указать, Excel считает его равным ИСТИНА и ищет приблизительное совпадение. При приблизительном поиске ВПР не говорит «не нашёл», а берёт ближайшее меньшее значение.

Я проверил на артикуле A-110, которого в прайсе нет:

Формула Результат в Excel
=ВПР(B2;Прайс!$A$2:$D$9;2;ЛОЖЬ) #Н/Д
=ВПР(B3;Прайс!$A$2:$D$9;2) Наушники

Вторая формула без четвёртого аргумента уверенно вернула «Наушники», последний товар в прайсе: A-110 по алфавиту больше A-108, и Excel решил, что это подходящая строка. В заказе такая ошибка незаметна, просто в счёт попадёт чужой товар по чужой цене. Поэтому правило простое: для поиска по артикулу, коду, фамилии или номеру всегда пишите ЛОЖЬ или 0. Это одно и то же: =ВПР(A2;Прайс!$A$2:$D$9;2;0) работает так же.

Когда ИСТИНА нужна

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

ВПР с ИСТИНА подбирает скидку по шкале
Шкала скидок: 0, 5 000, 10 000 и 20 000 ₽

Сумма 7 500 ₽ попала в ступень «от 5 000», а ровно 10 000 ₽ уже в ступень «от 10 000». Обязательное условие: первый столбец шкалы отсортирован по возрастанию. Я перемешал строки той же шкалы (10 000, 0, 20 000, 5 000) и получил для 7 500 ₽ скидку 0 % вместо 3 %, а для 25 000 ₽ скидку 3 % вместо 7 %. Ошибки Excel при этом не показал.

Зачем знаки $ при протягивании

Адрес без знаков доллара относительный: при копировании вниз на одну строку Excel сдвигает его тоже на одну строку. Для ссылки на артикул это нужно, для ссылки на прайс нет. Я протянул формулу =ВПР(A2;Прайс!A2:D9;2;ЛОЖЬ) без $ на четыре строки заказа:

Строка Формула после протягивания Результат
2 =ВПР(A2;Прайс!A2:D9;2;ЛОЖЬ) Флешка 64 ГБ
3 =ВПР(A3;Прайс!A3:D10;2;ЛОЖЬ) #Н/Д
4 =ВПР(A4;Прайс!A4:D11;2;ЛОЖЬ) Веб-камера
5 =ВПР(A5;Прайс!A5:D12;2;ЛОЖЬ) #Н/Д

Таблица поиска уползла вниз, и артикулы A-101 и A-102 из верхних строк прайса в неё больше не попадают. Хуже всего, что строка 4 «случайно» работает: веб-камера стоит ниже, и ошибку легко не заметить на маленьком примере. Знак $ перед буквой закрепляет столбец, перед цифрой строку, $A$2:$D$9 закрепляет всё. Клавиша F4 при курсоре на адресе перебирает варианты.

Другой способ не думать о $: превратить прайс в «умную» таблицу (Ctrl+T) или выделить столбцы целиком: Прайс!$A:$D. Ссылка на целые столбцы не съедет, а новые строки прайса сразу попадут в поиск.

ВПР в Excel из другой таблицы

Из другого листа

В примере выше прайс уже лежит на другом листе. Адрес листа пишется перед диапазоном через восклицательный знак: Прайс!$A$2:$D$9. Если в названии листа есть пробел, Excel возьмёт его в одинарные кавычки: 'Прайс 2026'!$A$2:$D$9. Проще всего не набирать это руками, а щёлкнуть по листу и выделить диапазон мышью во время ввода формулы.

Из другой книги (файла)

Откройте оба файла, начните формулу в заказе и выделите диапазон в файле с прайсом. Я сделал это на двух файлах, prajs.xlsx и zakaz-iz-drugoy-knigi.xlsx. Пока прайс открыт, формула выглядит так:

Вывод
=ВПР(A2;[prajs.xlsx]Прайс!$A$2:$D$9;3;ЛОЖЬ)

После закрытия прайса Excel сам дописал в формулу полный путь к файлу. Путь у каждого свой, у меня это была рабочая папка, поэтому ниже он заменён на условный:

Вывод
=ВПР(A2;'C:\Папка\[prajs.xlsx]Прайс'!$A$2:$D$9;3;ЛОЖЬ)

Формула вернула 1 490 для клавиатуры и продолжила показывать это значение и с закрытым прайсом. Дальше я поменял цену в закрытом файле прайса на 1 590 и открыл заказ без обновления связей: в ячейке осталось старое 1 490. После обновления связи появилось 1 590. Отсюда два практических вывода:

  • значения из другой книги Excel хранит у себя как копию и обновляет только при обновлении связей. При открытии файла он обычно спрашивает об этом, соглашайтесь, если источнику доверяете;
  • если прайс переименовать или перенести в другую папку, связь сломается. Для файлов, которые путешествуют по почте, надёжнее скопировать прайс отдельным листом в ту же книгу.

Если данные приходят в виде нескольких выгрузок CSV, их удобно сначала склеить в одну таблицу, как в статье Как объединить несколько CSV-файлов в один, и уже по ней искать через ВПР.

Почему ВПР выдаёт #Н/Д и другие ошибки

Я собрал типичные ошибки на одном листе и посчитал в Excel. Слева описание, справа настоящий результат и текст формулы (выведен функцией Ф.ТЕКСТ):

Ошибки ВПР в Excel: #Н/Д, #ССЫЛКА!, #ЗНАЧ!
Типичные ошибки ВПР и их исправление, Excel 2021
Что видите Причина Что делать
#Н/Д Искомого значения нет в первом столбце таблицы Проверить, что ищете в правильном столбце, и что значение там правда есть
#Н/Д, хотя значение «точно есть» Лишний пробел в конце или начале строки СЖПРОБЕЛЫ(A2) вместо A2, или почистить данные
#Н/Д с числовыми кодами В одной таблице число, в другой число, сохранённое как текст Привести к одному типу: --A2 или ЗНАЧЕН(A2) превращают текст в число
#ССЫЛКА! Номер столбца больше, чем столбцов в таблице (у меня 5 при таблице из 4 столбцов) Расширить таблицу или исправить номер
#ЗНАЧ! Номер столбца меньше 1 (у меня 0) Нумерация начинается с 1
#ИМЯ? Опечатка в имени функции или текст без кавычек Писать текст в кавычках: "A-104"
Чужое значение без ошибки Пропущен четвёртый аргумент или шкала не отсортирована Указать ЛОЖЬ, для шкал отсортировать

Лишние пробелы: СЖПРОБЕЛЫ

Артикул «A-103 » с пробелом на конце на глаз не отличить от «A-103», но для ВПР это разные строки: результат #Н/Д. Формула =ВПР(СЖПРОБЕЛЫ(B7);Прайс!$A$2:$D$9;2;ЛОЖЬ) убирает пробелы по краям и находит «Клавиатуру». Пробелы чаще всего приносят выгрузки из 1С, сайтов и CRM. Если пробелы сидят в самом прайсе, а не в искомом значении, СЖПРОБЕЛЫ в формуле не поможет: прайс нужно почистить (вспомогательный столбец с СЖПРОБЕЛЫ, затем «Вставить как значения»).

Числа как текст

В моём примере коды товаров 1001, 1002, 1003 записаны числами, а искомое «1002» хранится как текст (так бывает после копирования с сайта или из выгрузки). Итог #Н/Д. Тот же поиск с --B10 (двойной минус превращает текст в число) возвращает «Свитч», и обычное число в ячейке тоже находит «Свитч». Такие ячейки Excel обычно помечает зелёным треугольником в углу, а текстовые числа по умолчанию прижаты к левому краю.

ЕСЛИОШИБКА: вместо #Н/Д свой текст

Если отсутствие значения нормальная ситуация (например в заказ вписали товар, которого нет в прайсе), оберните формулу:

Вывод
=ЕСЛИОШИБКА(ВПР(B8;Прайс!$A$2:$D$9;2;ЛОЖЬ);"нет в прайсе")

Для A-110 Excel показал «нет в прайсе». Только не оборачивайте в ЕСЛИОШИБКА всё подряд с самого начала: она прячет любые ошибки, в том числе #ССЫЛКА! от неверного номера столбца. Сначала добейтесь, чтобы формула работала, потом добавляйте обёртку. Если нужно скрыть именно «не найдено», есть функция ЕСНД, она перехватывает только #Н/Д.

Функция ЕСЛИ в Excel с ВПР

ВПР часто кладут внутрь ЕСЛИ, чтобы принять решение по найденному значению. В заказе столбец «Наличие» сравнивает остаток из прайса (4-й столбец) с заказанным количеством:

Вывод
=ЕСЛИ(ВПР(A2;Прайс!$A$2:$D$9;4;ЛОЖЬ)>=D2;"в наличии";"не хватает")

Для флешки (остаток 20, заказано 3) Excel написал «в наличии», для беспроводной мыши (остаток 0, заказано 2) «не хватает». Это видно на картинке с заказом выше. По тому же принципу можно сделать =ЕСЛИ(ЕНД(ВПР(...));"новый клиент";"есть в базе"): ЕНД возвращает ИСТИНА, если ВПР ничего не нашла.

Excel: ВПР и сводные таблицы

ВПР и сводные таблицы встречаются в двух ситуациях.

ВПР перед сводной. В журнале продаж обычно есть только артикул и количество. Чтобы построить сводную по товарам и выручке, я добавил в журнал два столбца: =ВПР(B2;Прайс!$A$2:$D$9;2;ЛОЖЬ) для названия и =C2*ВПР(B2;Прайс!$A$2:$D$9;3;ЛОЖЬ) для суммы. Сводная по шести продажам показала: флешка 5 520 ₽, наушники 3 580 ₽, веб-камера 2 390 ₽, кабель HDMI 1 350 ₽, общий итог 12 840 ₽. Обычная сводная строится по одной таблице, поэтому недостающие столбцы удобно заранее подтянуть через ВПР.

ВПР по готовой сводной. Сводная на листе это обычные ячейки, и ВПР по ним работает: =ВПР(K2;$H:$I;2;ЛОЖЬ) нашла в сводной «Флешка 64 ГБ» и вернула 5 520. Ссылку лучше давать на целые столбцы, потому что после обновления сводная может вырасти или сжаться. При этом искать нужно по названию строки, а не по номеру позиции: порядок строк в сводной меняется.

Ограничения ВПР

Ищет только в первом столбце и возвращает только правее. Найти артикул по названию товара ВПР не может: название стоит во втором столбце, артикул в первом. Формула =ВПР("Веб-камера";Прайс!$A$2:$D$9;1;ЛОЖЬ) дала #Н/Д, потому что «Веб-камеру» Excel искал среди артикулов.

Возвращает первое совпадение. В маленькой таблице клиентов Иванов встречается дважды, с суммами 1 200 и 3 500 ₽. =ВПР("Иванов";$E$2:$F$4;2;ЛОЖЬ) вернула 1 200, вторую строку ВПР не видит. Для таблиц с повторами нужны СУММЕСЛИ (сложить всё) или ПРОСМОТРX с поиском с конца (взять последнее).

Номер столбца задан числом. Если вставить в прайс новый столбец между артикулом и ценой, формула продолжит брать третий столбец, где теперь будет уже не цена. Excel об этом не предупредит.

Когда лучше ИНДЕКС+ПОИСКПОЗ или ПРОСМОТРX

ИНДЕКС и ПОИСКПОЗ

Связка работает во всех версиях Excel и снимает ограничение «только вправо». ПОИСКПОЗ находит номер строки, ИНДЕКС достаёт значение из любого столбца:

Вывод
=ИНДЕКС(Прайс!$A$2:$A$9;ПОИСКПОЗ(A2;Прайс!$B$2:$B$9;0))

Для «Веб-камеры» формула вернула артикул A-107. Ноль в ПОИСКПОЗ означает точный поиск, как ЛОЖЬ в ВПР. Столбцы указываются отдельными диапазонами, поэтому вставка новых столбцов в прайс формулу не ломает.

ПРОСМОТРX

Новая функция поиска, которую Microsoft называет улучшенной версией ВПР. По справке Microsoft она есть в Excel для Microsoft 365, Excel 2024 и Excel 2021, а в Excel 2016 и Excel 2019 её нет. В моём Excel 2021 она работает:

Вывод
=ПРОСМОТРX(A3;Прайс!$B$2:$B$9;Прайс!$A$2:$A$9;"нет")

Аргументы: что ищем, где ищем, откуда брать результат, что вывести, если не нашли. Для «Веб-камеры» результат A-107, для «Монитора», которого нет в прайсе, текст «нет». Точный поиск включён по умолчанию, ЕСЛИОШИБКА не нужна. А шестой аргумент -1 ищет с конца: =ПРОСМОТРX("Иванов";$E$2:$E$4;$F$2:$F$4;"нет";0;-1) вернула последнюю сумму Иванова, 3 500.

Если файл откроют в Excel 2019 или старше, вместо результатов ПРОСМОТРX там будет ошибка. Для таких случаев остаётся ВПР или ИНДЕКС+ПОИСКПОЗ.

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

Google Таблицы. Функция есть, английское название VLOOKUP, в русской справке Google записана так: ВПР(запрос; диапазон; индекс; [отсортировано]). Логика та же, что в Excel, и по умолчанию четвёртый аргумент тоже ИСТИНА, поэтому Google прямо рекомендует указывать ЛОЖЬ. Русские названия функций работают, если в настройках Google Таблиц снят флажок «Всегда использовать названия функций на английском языке». Разделитель аргументов зависит от региональных настроек таблицы (Файл → Настройки): если формула с точками с запятой не принимается, замените их на запятые. ПРОСМОТРX в Google Таблицах тоже есть, под именем XLOOKUP.

LibreOffice Calc. Функция называется ВПР, синтаксис по справке: ВПР(Критерий поиска; Массив; Индекс [; Сортированный диапазон поиска]), аргументы разделяются точкой с запятой. Четвёртый аргумент по умолчанию тоже считается ИСТИНА, так что ЛОЖЬ или 0 нужно писать и здесь. Функция XLOOKUP появилась в LibreOffice 24.8.

Файл vpr-primer.xlsx из этой статьи можно открыть и в Google Таблицах, и в LibreOffice: ВПР там есть. Сам я проверял пример только в Excel, а ПРОСМОТРX в старых версиях LibreOffice покажет ошибку.

Частые вопросы

Формула ВПР в Excel для чайников: как запомнить?

«Что ищем; где ищем; какой столбец отдать; ЛОЖЬ». Искомое должно стоять в первом столбце таблицы, адрес таблицы закрепляется знаками $, последний аргумент ЛОЖЬ. Этого хватает для девяти задач из десяти.

Почему ВПР выдаёт #Н/Д, хотя значение есть в таблице?

Почти всегда это лишний пробел или число, сохранённое как текст. Проверьте длину ячеек функцией ДЛСТР: у «A-103» она 5, у «A-103 » 6. Для пробелов используйте СЖПРОБЕЛЫ, для чисел двойной минус --A2.

Как сделать ВПР в Excel из другого листа?

Во время ввода формулы, на втором аргументе, щёлкните по ярлычку нужного листа и выделите таблицу. Excel подставит адрес вида Прайс!A2:D9, останется нажать F4 для знаков $.

Можно ли с помощью ВПР найти значение слева?

Нет, ВПР возвращает только столбцы правее столбца поиска. Используйте ИНДЕКС+ПОИСКПОЗ или ПРОСМОТРX, либо переставьте столбцы в таблице.

Можно ли вернуть сразу несколько столбцов?

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

Чем ВПР отличается от ГПР?

ГПР делает то же самое, но ищет в первой строке таблицы слева направо и возвращает значение из строки ниже. Нужна она редко, когда таблица развёрнута горизонтально.

Источники (8)