ВПР в Excel: как работает функция, синтаксис по аргументам, примеры и ошибка #Н/Д
ВПР ищет значение в первом столбце таблицы и возвращает данные из указанного столбца той же строки. Рабочая формула в большинстве задач выглядит так: =ВПР(что_ищем; где_ищем; номер_столбца; 0). Ниже — разбор каждого аргумента, примеры «данные → формула → результат» и починка главной ошибки #Н/Д.
- Синтаксис: четыре аргумента ВПР
- Почему интервальный_просмотр почти всегда 0
- Пример: подтягиваем цены из прайса в заказы
- Знак $: почему диапазон обязательно закрепляют
- Ошибка #Н/Д: пять причин и лечение
- ЕСЛИОШИБКА: аккуратный отчёт вместо #Н/Д
- ВПР по двум условиям: вспомогательный столбец
- ВПР, ИНДЕКС+ПОИСКПОЗ или ПРОСМОТРX: что выбрать
- Коротко: рабочий чек-лист ВПР
- Вопросы и ответы
Синтаксис: четыре аргумента ВПР
ВПР (в английской версии Excel — VLOOKUP) ищет заданное значение в первом столбце указанного диапазона и возвращает содержимое любого столбца из той же строки. Официальное описание функции — в справке Microsoft по ВПР.
=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
| Аргумент | Что указывать | Типичная ошибка |
|---|---|---|
| искомое_значение | Что ищем: ссылка на ячейку (E2) или значение в кавычках ("М-205") | Ссылаются на столбец с ответом, а не со значением-ключом |
| таблица | Диапазон, в первом столбце которого лежат искомые значения: $A$2:$C$6 | Диапазон начинается не со столбца поиска |
| номер_столбца | Порядковый номер столбца внутри диапазона «таблица», из которого берётся ответ: 3 — третий столбец диапазона | Считают номер столбца листа, а нужен номер внутри диапазона |
| [интервальный_просмотр] | 0 — точное совпадение, 1 — приближённый поиск | Аргумент опускают, и Excel по умолчанию применяет приближённый режим |
Два жёстких ограничения классической ВПР: она ищет только в первом столбце диапазона «таблица» и возвращает данные только из столбцов правее него. Если ответ лежит слева от ключа, нужна связка ИНДЕКС+ПОИСКПОЗ или функция ПРОСМОТРX — их сравнение с ВПР дальше в статье.
Почему интервальный_просмотр почти всегда 0
Четвёртый аргумент задаёт режим поиска, и именно с ним связана половина «мистических» результатов ВПР.
| Значение | Режим | Требование к данным | Когда использовать |
|---|---|---|---|
| 0 (или ЛОЖЬ) | Точное совпадение: строка с таким ключом либо найдена, либо возвращается #Н/Д | Сортировка не нужна | Артикулы, ИНН, табельные номера, ФИО — абсолютное большинство задач |
| 1 (или ИСТИНА) | Приближённый поиск: берётся наибольшее значение, не превышающее искомое | Первый столбец обязан быть отсортирован по возрастанию | Ступенчатые шкалы: скидка от суммы, тарифные диапазоны, границы норм |
Главная ловушка: если аргумент не указан, Excel применяет приближённый режим. На несортированном справочнике формула не выдаст ошибку — она молча вернёт значение из чужой строки. Поэтому 0 в конце пишут всегда, даже когда «и так работает».
Пример уместного приближённого поиска — шкала скидок: от 0 руб. — 0%, от 10 000 руб. — 5%, от 50 000 руб. — 10%. Формула =ВПР(13140;$A$2:$B$4;2;1) вернёт 5%: сумма 13 140 уже перешагнула порог 10 000, но не достигла 50 000.
Пример: подтягиваем цены из прайса в заказы
Исходные данные: прайс расположен в диапазоне A1:C6, таблица заказов — в E1:G5. В столбец G нужно подтянуть цену по артикулу.
| Артикул (A) | Товар (B) | Цена, руб. (C) |
|---|---|---|
| К-101 | Клавиатура | 2 490 |
| М-205 | Мышь | 1 190 |
| Н-310 | Наушники | 3 990 |
| В-412 | Веб-камера | 4 590 |
| К-118 | Коврик | 390 |
В ячейку G2 вводим формулу и копируем вниз:
=ВПР(E2; $A$2:$C$6; 3; 0)
ВПР берёт артикул из E2, находит его в первом столбце диапазона A2:C6 и возвращает значение третьего столбца этого диапазона — цену.
| Артикул (E) | Кол-во (F) | Формула в G | Результат |
|---|---|---|---|
| М-205 | 3 | =ВПР(E2;$A$2:$C$6;3;0) | 1 190 |
| К-101 | 2 | =ВПР(E3;$A$2:$C$6;3;0) | 2 490 |
| В-412 | 1 | =ВПР(E4;$A$2:$C$6;3;0) | 4 590 |
| К-500 | 5 | =ВПР(E5;$A$2:$C$6;3;0) | #Н/Д — артикула нет в прайсе |
Сумму по строке считают, умножая результат ВПР на количество: =ВПР(E2;$A$2:$C$6;3;0)*F2 даст 1 190 × 3 = 3 570 руб. А #Н/Д в последней строке — не сбой, а честный ответ: такого артикула в прайсе нет. Что делать с такими строками — в двух следующих разделах.
Знак $: почему диапазон обязательно закрепляют
Если написать в G2 просто A2:C6 и протянуть формулу вниз, в G3 диапазон сместится на A3:C7, в G4 — на A4:C8. Верхние строки прайса выпадут из зоны поиска, и часть артикулов получит #Н/Д, хотя в справочнике они есть. Знаки доллара — $A$2:$C$6 — фиксируют диапазон при копировании: это абсолютная ссылка. Быстро расставить доллары можно клавишей F4, поставив курсор на ссылку в строке формул. Как устроены типы ссылок и когда закреплять только строку или только столбец — в статье об абсолютных и относительных ссылках.
Ошибка #Н/Д: пять причин и лечение
#Н/Д расшифровывается как «нет данных»: ВПР не нашла искомое значение в первом столбце диапазона. Как отмечает справка Microsoft об ошибке #Н/Д, чаще всего дело не в формуле, а в данных.
| Причина | Как обнаружить | Как исправить |
|---|---|---|
| Значения действительно нет в справочнике | Поиск по листу (Ctrl+F) не находит ключ | Дополнить справочник; строки-исключения обработать через ЕСЛИОШИБКА |
| Лишние пробелы: в конце ячейки или двойные внутри | =ДЛСТР(E2) показывает больше символов, чем видно | Прогнать оба столбца ключей через функцию СЖПРОБЕЛЫ |
| Число сохранено как текст в одной из таблиц | Зелёный треугольник в углу ячейки; =ЕТЕКСТ(E2) возвращает ИСТИНА | Выделить столбец, затем «Данные» — «Текст по столбцам» — «Готово»: значения станут числами |
| Буквы из другого алфавита: русская «С» вместо латинской «C» | Ctrl+F по точному значению не находит визуально одинаковую пару | Привести ключи к одному алфавиту заменой (Ctrl+H) |
| Включён приближённый режим: четвёртый аргумент пропущен или равен 1 | В конце формулы нет явного 0; искомое меньше минимального значения первого столбца | Дописать 0 четвёртым аргументом; единицу оставлять только на отсортированных шкалах |
Регистр не виноват: ВПР не различает строчные и прописные буквы — «мышь» и «МЫШЬ» для неё одно и то же значение. Если два внешне одинаковых текста не совпадают, ищите пробелы или буквы другого алфавита, а не разницу регистра. Отдельная история — искомое значение длиннее 255 символов: тут ВПР возвращает не #Н/Д, а ошибку #ЗНАЧ!; лечение — короткие коды-ключи или связка ИНДЕКС+ПОИСКПОЗ.
ЕСЛИОШИБКА: аккуратный отчёт вместо #Н/Д
Когда файл уходит руководителю или клиенту, россыпь #Н/Д выглядит как сломанный отчёт. Обёртка ЕСЛИОШИБКА подменяет любую ошибку заданным значением:
=ЕСЛИОШИБКА(ВПР(E2;$A$2:$C$6;3;0); "нет в прайсе")
Вместо текста можно вернуть 0 или пустую строку "". Важно: заворачивайте формулу в ЕСЛИОШИБКА только после сверки данных. На этапе поиска расхождений #Н/Д — полезный сигнал, и глушить его рано. Если же нужно не скрыть ошибку, а разветвить расчёт — «до порога одна ставка, после порога другая», — это задача функции ЕСЛИ.
ВПР по двум условиям: вспомогательный столбец
Сама по себе ВПР ищет по одному ключу. Когда условий два — например, тариф доставки зависит от артикула и города, — слева от таблицы добавляют вспомогательный столбец со «склеенным» ключом.
В таблице тарифов вставляем столбец A с формулой =B2&"|"&C2 — получаются ключи вида «М-205|Казань». Разделитель обязателен: без него разные пары значений могут склеиться в одинаковые ключи.
Формула поиска: =ВПР(E2&"|"&F2; $A$2:$D$4; 4; 0), где E2 — артикул, F2 — город, 4 — номер столбца с тарифом.
| Ключ (A) | Артикул (B) | Город (C) | Тариф, руб. (D) |
|---|---|---|---|
| М-205|Москва | М-205 | Москва | 350 |
| М-205|Казань | М-205 | Казань | 490 |
| К-101|Москва | К-101 | Москва | 420 |
Для заказа с артикулом М-205 и городом Казань формула склеит ключ «М-205|Казань» и вернёт 490. Вспомогательный столбец должен стоять первым в диапазоне поиска — ВПР смотрит только в него.
Подтянули данные ВПР — следующий уровень: сводные отчёты и автоматизация таблиц
Курс Excel + «Google Таблицы» с нуля до PRO превращает отдельные приёмы в систему работы с таблицами: обработка данных, расчёты и отчёты в Excel и «Google Таблицах», практические задания с обратной связью преподавателя. По окончании — удостоверение о повышении квалификации.
ВПР, ИНДЕКС+ПОИСКПОЗ или ПРОСМОТРX: что выбрать
У классической ВПР есть две альтернативы, и выбор между ними — вопрос версии Excel и требований к формуле.
| Возможность | ВПР | ИНДЕКС+ПОИСКПОЗ | ПРОСМОТРX |
|---|---|---|---|
| Поиск влево от столбца с ключом | Нет | Да | Да |
| Формула переживает вставку столбца в таблицу | Нет — номер столбца сбивается | Да | Да |
| Точный поиск по умолчанию | Нет, нужен явный 0 | Нет, нужен явный 0 в ПОИСКПОЗ | Да |
| Свой текст при «не найдено» без ЕСЛИОШИБКА | Нет | Нет | Да, аргумент «если_ничего_не_найдено» |
| Версии Excel | Все | Все | Microsoft 365, Excel 2021 и новее |
Доступность по документации Microsoft: ПРОСМОТРX работает в Microsoft 365 и в Excel начиная с версии 2021 и отсутствует в Excel 2016–2019 (справка по ПРОСМОТРX); связка ИНДЕКС и ПОИСКПОЗ доступна во всех версиях (обзор функций поиска Microsoft).
Практическое правило: в свежем Excel новые формулы удобнее писать на ПРОСМОТРX; если файл будут открывать в Excel 2016–2019 — надёжнее связка ИНДЕКС+ПОИСКПОЗ; ВПР остаётся стандартом там, где важна совместимость и формулу будут читать коллеги любого уровня.
Коротко: рабочий чек-лист ВПР
- Столбец с искомыми значениями — первый в диапазоне «таблица», ответ — только правее него.
- Четвёртый аргумент — всегда 0; единица допустима только на отсортированных ступенчатых шкалах.
- Диапазон поиска закрепляйте долларами перед копированием: $A$2:$C$6, клавиша F4.
- При #Н/Д сначала проверяйте данные: лишние пробелы, числа-как-текст, буквы другого алфавита.
- ЕСЛИОШИБКА — только после сверки; для новых файлов рассмотрите ПРОСМОТРX, для старых версий — ИНДЕКС+ПОИСКПОЗ.
Вопросы и ответы
- Может ли ВПР искать влево от столбца с ключом?
Нет, классическая ВПР возвращает значения только из столбцов правее столбца поиска. Варианты решения: перенести столбец с ключом в начало диапазона, использовать связку ИНДЕКС+ПОИСКПОЗ или функцию ПРОСМОТРX — им направление поиска безразлично.
- Работает ли ВПР между двумя разными файлами Excel?
Да. Начните вводить формулу, перейдите в окно второй книги и выделите диапазон — Excel сам подставит ссылку вида [Прайс.xlsx]Лист1!$A$2:$C$6. Когда файл-источник закрыт, ссылка хранит полный путь к нему, поэтому при переносе или переименовании источника формула сломается — связанные файлы держите вместе.
- Что вернёт ВПР, если в столбце поиска несколько одинаковых значений?
Только первое совпадение сверху — остальные строки функция игнорирует. Если нужны все совпадения, используйте функцию ФИЛЬТР в Microsoft 365 либо сводную таблицу.
- Почему ВПР медленно работает на больших таблицах?
Каждая формула точного поиска просматривает диапазон заново, и десятки тысяч строк с ВПР заметно замедляют пересчёт книги. Ограничьте диапазон реальными данными вместо ссылок на целые столбцы, а разовые сверки больших выгрузок делайте через Power Query.
- Можно ли сделать ВПР с учётом регистра букв?
Стандартными аргументами — нет: ВПР и ПРОСМОТРX регистр не различают. Для регистрозависимого поиска строят формулу массива на СОВПАД и ПОИСКПОЗ либо добавляют вспомогательный столбец с уникальными кодами вместо текстовых ключей.
- Чем ВПР отличается от ГПР и когда нужен горизонтальный поиск?
ГПР — зеркальная функция: она ищет ключ не в первом столбце, а в первой строке диапазона и возвращает значение из заданной строки того же столбца. Синтаксис тот же, только третьим аргументом идёт номер_строки: =ГПР(искомое_значение; таблица; номер_строки; 0). ГПР нужна для таблиц, развёрнутых горизонтально, — например, месяцы в первой строке, показатели ниже; правило про 0 в четвёртом аргументе действует так же.