ВПР в Excel подтягивает значение из одной таблицы в другую по общему ключу: артикулу, ФИО, коду. Формула выглядит так: =ВПР(искомое; таблица; номер_столбца; ЛОЖЬ). Excel ищет искомое в первом столбце таблицы и возвращает то, что стоит в той же строке в столбце с указанным номером.
Ниже разберём синтаксис по аргументам, шесть рабочих примеров (прайс, склад, грейды по окладу, два условия, второй файл, вложенный ВПР со скидкой), ошибки #Н/Д и #ССЫЛКА! и две замены ВПР. Все формулы проверены в русской локали, разделитель аргументов «;». Если нужны и другие функции, есть подборка приёмов для быстрой работы в Excel.
CheckroiПодборка курсов по Microsoft Excel675 курсов • 63 школыСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→
Что такое ВПР и когда она нужна
ВПР расшифровывается как «вертикальный просмотр», в английской версии это VLOOKUP. Функция перебирает первый столбец диапазона сверху вниз, находит совпадение с искомым значением и отдаёт данные из той же строки.
Это одна из первых функций, которую спрашивают на собеседованиях аналитиков и которая есть в программе почти любого курса по Excel. Типовые задачи, где без ВПР приходится сверять таблицы глазами:
- в заказ подставить цену из прайса по артикулу;
- к списку клиентов подтянуть менеджера из справочника;
- сравнить два выгруженных отчёта и найти, чего нет во втором;
- по окладу определить грейд или процент премии.
Одно ограничение стоит знать сразу: ВПР ищет только по первому столбцу диапазона и возвращает данные из столбцов правее. Если ключ стоит правее нужного значения, придётся либо переставить столбцы, либо взять ПРОСМОТРX или связку ИНДЕКС + ПОИСКПОЗ, о них в конце статьи.
Синтаксис функции ВПР по аргументам
=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])| Аргумент | Что передать | Пример |
|---|---|---|
| искомое_значение | Ячейка с ключом, который ищем | A2 |
| таблица | Диапазон, где ключ стоит в первом столбце. Закрепляйте знаком $ | Прайс!$A$2:$C$100 |
| номер_столбца | Порядковый номер столбца внутри диапазона, из которого вернуть значение. Первый столбец диапазона = 1 | 3 |
| интервальный_просмотр | ЛОЖЬ (или 0) для точного совпадения, ИСТИНА (или 1) для приблизительного. Необязательный, по умолчанию ИСТИНА | ЛОЖЬ |
Номер столбца считается от начала диапазона, а не от столбца A листа. Если таблица начинается с колонки D, то колонка F внутри неё будет третьей.
Четвёртый аргумент можно пропустить, но тогда Excel включит приблизительный поиск. Для артикулов, ФИО и кодов это почти всегда ошибка, поэтому в 9 случаях из 10 пишите ЛОЖЬ. Официальное описание аргументов есть в справке Microsoft по функции ВПР.
Канал основателя Checkroi Вани Буявца3 700 человек читают мой Телеграм-канал про нейросетиСобрал промпты для Claude Code и ChatGPT, разборы ИИ-инструментов и лайфхаки по продвижению бизнеса в одном местеПрисоединитьсяКак сделать ВПР в Excel: пошаговая инструкция
Возьмём две таблицы на разных листах. На листе «Прайс» три столбца: артикул, товар, цена. На листе «Заказ» менеджер вводит артикул и количество, а цену нужно подтянуть автоматически.
- На листе «Заказ» встаньте в ячейку C2, где должна появиться цена.
- Введите
=ВПР(и щёлкните по A2, где записан артикул. - Поставьте «;», перейдите на лист «Прайс» и выделите диапазон A2:C6. Нажмите F4, чтобы ссылка стала
Прайс!$A$2:$C$6. - Поставьте «;» и укажите номер столбца с ценой внутри диапазона. Цена в третьей колонке, значит 3.
- Поставьте «;», напишите ЛОЖЬ, закройте скобку и нажмите Enter.
- Протяните формулу вниз за правый нижний угол ячейки. В длинном заказе заранее закрепите шапку таблицы, чтобы видеть названия столбцов при прокрутке.
Как формула растёт по шагам, если смотреть в строку формул:
=ВПР(A2;
=ВПР(A2; Прайс!$A$2:$C$6;
=ВПР(A2; Прайс!$A$2:$C$6; 3;
=ВПР(A2; Прайс!$A$2:$C$6; 3; ЛОЖЬ)Для артикула A-103 функция вернёт 890, для A-105 вернёт 2 490. В соседней ячейке останется умножить цену на количество: =C2*B2.
Если формулу удобнее собирать через окно, нажмите Shift+F3, в категории «Ссылки и массивы» выберите ВПР и заполните четыре поля. Результат тот же, но подсказки под каждым полем помогают не перепутать порядок аргументов.
Примеры ВПР для рабочих таблиц
Шесть ситуаций, которые чаще всего встречаются у менеджеров, бухгалтеров и аналитиков данных. Каждая формула проверена на тестовых данных.
Пример 1: цена из прайса по артикулу
Базовый случай из инструкции выше. Единственный нюанс, когда прайс большой и растёт: указывайте не фиксированный диапазон, а целые столбцы.
=ВПР(A2; Прайс!$A:$C; 3; ЛОЖЬ)Так новые позиции в прайсе подхватываются без правки формулы. Заголовок прайса в первый столбец попадёт тоже, но слову «Артикул» совпадений не найдётся, и на результат это не влияет.
Пример 2: остаток на складе с обработкой пустых значений
Лист «Остатки» содержит артикул и количество на складе. Товара может не оказаться в справочнике вовсе, и тогда ВПР выдаст #Н/Д. Оборачиваем её в ЕСЛИОШИБКА:
=ЕСЛИОШИБКА(ВПР(A2; Остатки!$A:$B; 2; 0); "нет на складе")Для артикула A-103 вернётся 7, для несуществующего A-999 вернётся текст «нет на складе» вместо ошибки. Ноль в четвёртом аргументе равен ЛОЖЬ, писать можно любой вариант.
Дальше по такому столбцу удобно строить фильтр или сводную таблицу: все «нет на складе» соберутся в одну группу.
Пример 3: грейд и премия по таблице окладов
Здесь пригодится приблизительный поиск, тот самый ИСТИНА. Справочник «Грейды» устроен так: в первом столбце нижняя граница оклада, во втором название грейда, в третьем процент премии.
| Оклад от | Грейд | Премия, % |
|---|---|---|
| 0 | Junior | 5 |
| 80 000 | Middle | 10 |
| 150 000 | Senior | 15 |
| 250 000 | Lead | 20 |
В таблице сотрудников оклад стоит в столбце C. Формулы для грейда и процента:
=ВПР(C2; Грейды!$A$2:$C$5; 2; ИСТИНА)
=ВПР(C2; Грейды!$A$2:$C$5; 3; ИСТИНА)Для оклада 120 000 функция вернёт Middle и 10: Excel берёт ближайшее значение снизу, то есть 80 000. Для 180 000 получится Senior и 15. Премия в рублях: =C2*E2/100, для 120 000 это 12 000.
Обязательное условие приблизительного поиска: первый столбец справочника отсортирован по возрастанию. На неотсортированной таблице та же формула для оклада 100 000 вернула #Н/Д, я проверял.
CheckroiПодборка курсов по Excel и Google-Таблицам487 курсов • 48 школСравните цены, школы, программу и найдите выгодные предложения по обучениюСравнить→
Пример 4: ВПР по двум условиям
Сама ВПР умеет искать только по одному ключу. Когда нужно найти выплату по ФИО и месяцу сразу, ключ склеивают в служебном столбце.
- В таблице выплат добавьте столбец A с формулой
=B2&"|"&C2, где B это ФИО, C это месяц. Получится «Иванов|апрель». - В таблице, куда подтягиваете данные, склейте искомое так же и ищите по нему.
=ВПР(A2&"|"&H2; Выплаты!$A$2:$D$5; 4; 0)Для Иванова за апрель формула вернула 72 000, для Петровой за март 125 000. Разделитель «|» нужен, чтобы «Иван|овапрель» и «Иванов|апрель» не совпали случайно. Тот же приём работает и с тремя условиями.
Если исходная таблица пришла одной колонкой вроде «Иванов, апрель, 72000», сначала разделите текст по столбцам, иначе ключ не собрать.
Пример 5: ВПР между двумя файлами
Прайс часто живёт в отдельной книге, которую присылает поставщик. ВПР умеет смотреть в другой файл, но собирать ссылку руками не нужно.
- Откройте обе книги: с заказом и с прайсом.
- В заказе начните формулу
=ВПР(A2;, затем через меню «Вид → Перейти в другое окно» переключитесь в книгу прайса и выделите диапазон мышью. - Допишите номер столбца и ЛОЖЬ, нажмите Enter.
=ВПР(A2; [Прайс.xlsx]Лист1!$A:$C; 3; ЛОЖЬ)Пока прайс открыт, ссылка выглядит как [Прайс.xlsx]Лист1!. Закроете файл, и Excel сам допишет полный путь к нему, формат описан в справке Microsoft о ссылках на другие книги. Два следствия: переименовали или переложили прайс, ссылка сломается; отправляете заказ коллеге, отправляйте и прайс.
Пример 6: вложенный ВПР, скидка по категории товара
Одна ВПР может стать аргументом другой. Задача: в справочнике «Товары» у каждой позиции есть категория, в справочнике «Скидки» у каждой категории свой процент, а в заказе только код и количество. Нужна итоговая сумма со скидкой.
Сначала внутренняя ВПР находит категорию по коду, потом внешняя по категории находит процент:
=ВПР(ВПР(A2; Товары!$A:$D; 3; 0); Скидки!$A:$B; 2; 0)Для термоса A-102 из категории «Посуда» вернётся 10. Итог с учётом количества:
=ВПР(A2; Товары!$A:$D; 4; 0)*(1-D2/100)*B2Три термоса по 1 290 со скидкой 10 % дали 3 483. Вложенность выше двух уровней читать уже тяжело, в таком случае разнесите шаги по соседним столбцам, как здесь: категория в C, скидка в D, сумма в E.
Ошибки ВПР: #Н/Д, #ССЫЛКА!, сдвиг диапазона
Список того, что ломает ВПР чаще всего. Проверял каждую ситуацию на тестовом файле, цифры ниже оттуда.
Ошибка 1: #Н/Д, значение не найдено
Самая частая. Причин четыре, и только первая очевидна.
- Ключа нет в справочнике. Артикул A-999 в прайсе отсутствует, ВПР честно возвращает #Н/Д. Лечится ЕСЛИОШИБКА или ЕСНД, как в примере 2.
- Лишние пробелы. Артикул «a-102 » с пробелом в конце не равен «A-102». Оборачивайте искомое в СЖПРОБЕЛЫ:
=ВПР(СЖПРОБЕЛЫ(A6); Прайс!$A$2:$C$6; 3; ЛОЖЬ). На тесте такая формула вернула 1 290 вместо #Н/Д. Регистр букв ВПР не различает, «a-102» и «A-102» для неё одно и то же. - Число сохранено как текст. Код 101 в справочнике записан числом, а в искомой ячейке лежит текст «101», часто после выгрузки из 1С, CRM или базы данных через DBeaver. Приведите тип:
=ВПР(ЗНАЧЕН(D1); A2:B3; 2; 0)или короче=ВПР(D1*1; A2:B3; 2; 0). - Приблизительный поиск по несортированному столбцу. Забыли ЛОЖЬ и справочник не отсортирован. Ставьте ЛОЖЬ.
Microsoft разбирает эти же причины на странице об исправлении ошибки #Н/Д, там же пример с СЖПРОБЕЛЫ.
Ошибка 2: #ССЫЛКА! и #ЗНАЧ!, неверный номер столбца
Диапазон $A$2:$C$6 содержит три столбца. Попросите четвёртый, и Excel вернёт #ССЫЛКА!. Укажите 0 или отрицательное число, получите #ЗНАЧ!. Обе ошибки описаны в справке Microsoft по ВПР, ссылка выше.
Решение: пересчитайте столбцы от начала диапазона. Или расширьте диапазон, если нужный столбец правее.
Ошибка 3: формула сползает при протягивании
Коварный случай, потому что первые строки работают. Если написать =ВПР(A2; Прайс!A2:C6; 3; ЛОЖЬ) без знаков $ и протянуть вниз, в четвёртой строке диапазон превратится в Прайс!A4:C8. Первые две строки прайса из поиска выпадут, и артикул A-101 внезапно даст #Н/Д.
Решение: выделите ссылку на диапазон в строке формул и нажмите F4, появится $A$2:$C$6. На Mac это Cmd+T. Сдвиг ссылок при протягивании сам по себе не баг, на нём построена, например, автоматическая нумерация строк, но диапазон поиска в ВПР должен стоять на месте.
Ошибка 4: без ЛОЖЬ формула подставляет чужое значение
Хуже #Н/Д только правдоподобная неправда. Формула =ВПР(A5; Прайс!$A$2:$C$6; 3) без четвёртого аргумента для несуществующего A-999 вернула на тесте 2 490, цену рюкзака A-105. Никакой ошибки, просто неверная цифра в заказе.
Решение одно: для точного поиска всегда указывайте ЛОЖЬ или 0.
Чем заменить ВПР: ПРОСМОТРX и ИНДЕКС + ПОИСКПОЗ
У ВПР два врождённых ограничения: ищет только в первом столбце и ломается, если вставить колонку внутрь диапазона (номер столбца остаётся старым). Две функции их не имеют.
ПРОСМОТРX
Функция доступна в Excel 2021, Excel 2024 и Microsoft 365, в Excel 2016 и 2019 её нет. Столбец поиска и столбец возврата указываются отдельно, а четвёртый аргумент сразу задаёт текст для ненайденных значений:
=ПРОСМОТРX(A2; Прайс!$A:$A; Прайс!$C:$C; "нет в прайсе")Поиск по умолчанию точный, номера столбцов считать не нужно, искать можно и влево. Если у вас актуальная версия Excel, для новых таблиц берите её.
ИНДЕКС + ПОИСКПОЗ
Работает во всех версиях Excel и в Google Таблицах. ПОИСКПОЗ находит номер строки, ИНДЕКС возвращает значение из этой строки нужного столбца:
=ИНДЕКС(Прайс!$C:$C; ПОИСКПОЗ(A2; Прайс!$A:$A; 0))Столбцы можно менять местами как угодно. Например, найти товар по цене, хотя цена стоит правее названия: =ИНДЕКС(Прайс!$B:$B; ПОИСКПОЗ(C2; Прайс!$C:$C; 0)). Для цены 890 вернётся «Ланч-бокс».
| Функция | Поиск влево | Версии Excel | Когда брать |
|---|---|---|---|
| ВПР | Нет | Все | Ключ в первом столбце, файл уходит коллегам со старым Excel |
| ПРОСМОТРX | Да | 2021, 2024, 365 | Новые таблицы в актуальной версии |
| ИНДЕКС + ПОИСКПОЗ | Да | Все | Ключ правее данных, нужна совместимость |
Канал основателя Checkroi Вани БуявцаЗабирайте промпты и обучение по нейросетям в моём Телеграм-каналеБольше 3 700 человек уже применяют Claude Code, ChatGPT и другие нейросети в работе, учёбе, бизнесе и жизниПерейти в каналВПР в Google Таблицах
В Google Таблицах функция даже в русском интерфейсе называется VLOOKUP, имена аргументов не переводятся. Порядок тот же: =VLOOKUP(A2; Прайс!A:C; 3; 0). Всё, что написано выше про закрепление диапазона и четвёртый аргумент, действует и здесь.
Синтаксис и примеры есть в справке Google по VLOOKUP. XLOOKUP там тоже работает, а вот окна «Мастер функций» нет, формулу набирают руками.
Задачи для тренировки
Три задачи по нарастающей. Данные придумайте сами, по 5–10 строк хватит, ответы под каждой задачей.
Задача 1: контакты клиентов
Лист «Клиенты»: ID, имя, почта. Лист «Заказы»: ID клиента, дата, сумма. Дополните заказы именем и почтой.
Ответ: =ВПР(A2; Клиенты!$A:$C; 2; 0) для имени и та же формула с номером столбца 3 для почты.
Задача 2: премия по грейду
Лист «Ставки»: должность, оклад, процент премии. Лист «Сотрудники»: табельный номер, ФИО, должность. Посчитайте премию в рублях для каждого.
Ответ: =ВПР(C2; Ставки!$A:$C; 2; 0)*ВПР(C2; Ставки!$A:$C; 3; 0)/100. Здесь поиск точный, потому что ключ текстовый. Если бы премия зависела от размера оклада, подошёл бы приблизительный поиск из примера 3.
Задача 3: проверка перед поиском
Сделайте так, чтобы для кода, которого нет в справочнике, ячейка показывала «нет в справочнике», но без ЕСЛИОШИБКА.
Ответ: =ЕСЛИ(СЧЁТЕСЛИ(Товары!$A:$A; A2)>0; ВПР(A2; Товары!$A:$D; 2; 0); "нет в справочнике"). СЧЁТЕСЛИ считает, сколько раз код встречается в первом столбце, и ВПР запускается только при совпадении. На тесте для A-999 вернулось «нет в справочнике», для A-102 вернулось «Термос».
Где освоить Excel системно
ВПР закрывает одну задачу, сверку таблиц. Дальше обычно упираются в сводные таблицы, Power Query и макросы, и здесь уже проще пройти курс, чем собирать знания по одной инструкции. Отдельно про то, кому Excel нужен как профессия, а кому как навык, мы писали в блоге.
В каталоге больше 700 курсов по Excel с фильтром по цене и формату, ниже пять из них.
| Курс | Школа | Стоимость со скидкой | В рассрочку | Длительность | Обзор курса от Checkroi |
|---|---|---|---|---|---|
| Excel Перейти на сайт курса |  Нетология | 29 000 ₽ | 1691 ₽/мес. | 8 недель | Обзор курса |
| Power BI & Excel PRO Перейти на сайт курса | Нетология | 38 500 ₽ | 3208 ₽/мес. | 2 месяца | Обзор курса |
| Excel + Google Таблицы с нуля до PRO Перейти на сайт курса |  Skillbox | 29 899 ₽ | 2965 ₽/мес. | 4 месяца | Обзор курса |
| Excel для работы Перейти на сайт курса |  Эдюсон | 16 500 ₽ | 1375 ₽/мес. | 2 недели | Обзор курса |
| Excel и Google-таблицы Перейти на сайт курса | Эдюсон | 39 900 ₽ | 3325 ₽/мес. | 1 месяц | Обзор курса |
Больше программ — в полном каталоге курсов по Microsoft Excel
