Функция ВПР в Excel: как перенести данные из одной таблицы в другую

Цены, остатки и фамилии редко лежат в одной таблице: заказ приходит из учётной программы, а справочник ведёт соседний отдел. ВПР в Excel ищет значение только в первом столбце указанного диапазона^[1], поэтому справочник заранее выстраивают ключом влево.

Рабочий путь у ВПР в Excel короткий: найти в справочнике столбец с ключом, ввести формулу с четырьмя аргументами, проверить результат на ключе, которого в справочнике нет, исправить ошибку и протянуть формулу на свои данные. Самая коварная ловушка — не код ошибки, а правдоподобная чужая цена: без ЛОЖЬ в конце формула на несортированном прайсе молча промахивается.

Ключевые факты: ВПР в Excel

• ВПР просматривает только крайний левый столбец диапазона и отдаёт значение из столбца, номер которого задан третьим аргументом.
• Номер столбца отсчитывается с 1 от крайнего левого столбца таблицы, а номер больше её ширины даёт ошибку #ССЫЛКА!^[1]
• Функция находит значение только слева направо, поэтому столбец результата обязан стоять правее ключа^[2].
• Справка Microsoft указывает ВПР для Microsoft 365, Excel 2024, Excel 2021, Excel 2019 и Excel 2016^[1].
• В Google Таблицах ВПР ведёт себя так же, как в Excel: без последнего аргумента поиск идёт приблизительно^[3].

Как работает ВПР в Excel: синтаксис и аргументы

ВПР в Excel сравнивает искомое значение с первым столбцом таблицы и возвращает ячейку из столбца с заданным номером. По справке Microsoft формула записывается так: =ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])^[1]. В английском интерфейсе та же функция называется VLOOKUP, буквально «вертикальный просмотр», а в поиске её часто набирают как «впр в эксель».

Номер столбца считается внутри таблицы, а не на листе. Для диапазона $F$2:$H$5 столбец F получает номер 1, G — 2, H — 3: отсчёт идёт с единицы от крайнего левого столбца^[1]. Номер больше ширины диапазона ВПР не примет и покажет #ССЫЛКА!, а номер меньше единицы — #ЗНАЧ!.

Искомое значение и первый столбец обязаны совпадать по типу. Справка Microsoft прямо советует не хранить числа и даты как текст^[1]: артикул, выгруженный из учётной программы текстом, функция не сопоставит с тем же артикулом-числом в прайсе. ВПР в Excel принимает вторым аргументом и именованный диапазон или таблицу^[1] — правило про ключ в первом столбце от этого не меняется.

От синтаксиса переходим к практике: ниже — прайс, заказ и одна формула, протянутая на весь столбец.

Пример ВПР: цена из прайса

ВПР сводит заказ без цен и прайс с ценами и остатками одной формулой на весь столбец. Заказ занимает столбцы A–D, прайс — столбцы F–H того же листа.

ВПР в Excel заполнит столбец C за шесть действий.

1. Встаньте в C2 и наберите =ВПР(.
2. Щёлкните A2 — ключ строки заказа — и поставьте ;.
3. Выделите прайс F2:H5 и нажмите F4, чтобы диапазон превратился в $F$2:$H$5.
4. Допишите ;2;ЛОЖЬ) и нажмите Enter: в ячейке появится цена чая, 250.
5. Протяните формулу до C4 двойным щелчком по маркеру заполнения и сверьте каждую цену с прайсом.
6. В D2 введите =B2*C2 и тоже протяните вниз.

После протягивания в C3 окажется 1200, в C4 — 90, а суммы в D2:D4 составят 1000, 2400 и 900. Чтобы вместо цены взять остаток, достаточно сменить номер столбца: =ВПР(A2;$F$2:$H$5;3;ЛОЖЬ) вернёт 8. Так ВПР в Excel переносит данные из одной таблицы в другую без ручного копирования. Прайс на отдельном листе указывают вместе с именем листа: =ВПР(A2;Прайс!$A$2:$C$5;3;ЛОЖЬ).

Без знака доллара диапазон сползает при протягивании: в C3 формула ищет уже в F3:H6, в C4 — в F4:H7, и нижние строки теряют верх прайса. Как работают $ и клавиша F4, разобрано на странице про абсолютные и смешанные ссылки. Отработайте эти шаги в тренажёре: урок подменяет прайс и проверяет, что формула не сломалась на чужих данных.

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

Точное или приблизительное совпадение: что ставить в конце формулы

ВПР с ЛОЖЬ в последнем аргументе ищет только точное совпадение, а с ИСТИНА или пустым местом — ближайшее меньшее значение. По справке Microsoft режим ИСТИНА требует сортировки первого столбца по возрастанию, иначе функция вернёт неверное значение^[1].

Приблизительный режим создан для шкал: скидка от суммы, ставка от дохода, оценка от балла. Формула =ВПР(7300;J2:K5;2;ИСТИНА) вернёт 10%: такого числа в столбце J нет, и функция берёт строку ближайшего меньшего порога, 5000. Для 12000 получится 15%, для 999 — 0%. Сумма меньше первого порога даст #Н/Д, ведь при ИСТИНА ошибка возникает, когда искомое меньше наименьшего значения первого столбца^[1]. Поэтому шкалу начинают с нуля.

Для названий и кодов приблизительный режим опасен. На прайсе из предыдущего раздела формула без последнего аргумента =ВПР(A2;$F$2:$H$5;2) вернёт для чая 480 — цену какао, и никакого предупреждения Excel не покажет.

Знаки ? и * срабатывают лишь при ЛОЖЬ и текстовом ключе: вопросительный знак заменяет один символ, звёздочка — любую последовательность, а тильда перед знаком ищет сам знак^[1]. Формула =ВПР("Ч?й";F2:G5;2;ЛОЖЬ) найдёт «Чай» и вернёт 250.

Именно поэтому разбор ошибок начинают с последнего аргумента и только потом смотрят на сами ключи.

Ошибки ВПР: #Н/Д и #ССЫЛКА!

Ошибки ВПР сводятся к трём причинам: ключ не найден в первом столбце, номер столбца выходит за пределы таблицы или текст в формуле записан без кавычек. Таблица ниже собирает симптомы из справки Microsoft^[1] и порядок действий.

Код #Н/Д при «видимо одинаковых» ключах появляется, когда в одном из них прячется лишний пробел или число сохранено как текст. Справка Microsoft советует очищать данные функциями СЖПРОБЕЛЫ и ПЕЧСИМВ^[1]: формула =ВПР(СЖПРОБЕЛЫ(A2);F2:G5;2;ЛОЖЬ) найдёт «Чай » с пробелом в конце и вернёт 250. Как вычистить пробелы сразу во всём столбце — на странице об удалении лишних пробелов в ячейках.

ЕСЛИОШИБКА не устраняет причину, а только прячет её. Сначала убедитесь, что ключа действительно нет в прайсе, и лишь затем заменяйте код ошибки понятным текстом: =ЕСЛИОШИБКА(ВПР("Мёд";$F$2:$H$5;2;ЛОЖЬ);"нет в прайсе") выведет «нет в прайсе». Для отчёта, который уходит руководителю, такая подпись честнее пустой ячейки.

Остаётся понять, в каких задачах функцию не спасёт даже идеально чистый справочник.

Когда ВПР не подойдёт

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

• Если столбец результата стоит левее столбца поиска, функция не дотянется до него ни при каком номере — нужна связка ИНДЕКС и ПОИСКПОЗ для поиска в любую сторону.
• При ключе из двух полей, например товар и склад, функция не примет второе условие: справка Google предлагает вспомогательный столбец со склейкой полей через &^[3].
• Если у ключа несколько строк, а нужны все, формула вернёт только первое совпадение^[3].
• Если в справочник регулярно вставляют столбцы, номер в формулах сместится и начнёт указывать на соседние данные.
• При работе с коллегами на Excel 2016 или Excel 2019 замена на новую функцию поиска не сработает: по справке Microsoft в этих версиях её нет^[4].

Совместимость остаётся главным козырем этой функции. По блоку «Применяется к» справка Microsoft относит функцию к Excel для Microsoft 365, Excel 2024, Excel 2021, Excel 2019 и Excel 2016, причём три первые версии включают Mac^[1].

Google Таблицы принимают ту же формулу с четырьмя аргументами — «запрос», «диапазон», «индекс» и «отсортировано», — и без последнего функция там тоже работает приблизительно^[3]. Индекс в Google Таблицах ограничен числом столбцов диапазона, а подстановочные знаки срабатывают только при ЛОЖЬ^[3]. Соседние функции поиска и подстановки собраны в обзоре основных формул и функций Excel.