ВПР в Excel: как пользоваться, ошибка #Н/Д и чем заменить

Коротко: что делает ВПР

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

• Формула для точного поиска: =ВПР(B2;Клиенты!$A$2:$D$8;3;ЛОЖЬ). Четвёртый аргумент ЛОЖЬ (или 0) обязателен: без него Excel ищет приблизительно.
• #Н/Д при точном поиске значит «ключ не найден». Причины — ключа действительно нет, в ячейке лишний пробел или число сохранено как текст.
• Если ключ в справочнике встречается дважды, ВПР молча берёт первое совпадение сверху.
• Пустая ячейка справочника превращается в 0.
• ПРОСМОТРX по умолчанию ищет точно, умеет брать столбец левее ключа и выводить свой текст вместо #Н/Д, но в Excel 2016 и 2019 её нет.
• В SQL та же операция — LEFT JOIN, только дубль ключа в справочнике там не прячется, а размножает строки.

Синтаксис ВПР: четыре аргумента

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

Ключ ВПР ищет только в первом, левом столбце таблицы, и номер столбца отсчитывается от него: 1 — сам ключ, 2 — соседний справа. Номер больше ширины диапазона даст #ССЫЛКА!. Столбец левее ключа ВПР не вернёт — для этого есть ПРОСМОТРX и ИНДЕКС с ПОИСКПОЗ.

В Google Таблицах функция тоже называется ВПР (VLOOKUP). Аргументы там подписаны запрос, диапазон, индекс, [отсортировано], а умолчание то же: без последнего аргумента поиск приблизительный. Google советует ставить ЛОЖЬ и пишет, что из нескольких подходящих строк вернётся первая.

Пример: платежи и справочник клиентов из базы «Маяк»

Возьмём шесть платежей за 16 августа 2026 года из учебной базы «Маяк» — той же, на которой работает SQL-песочница. Лист «Платежи»: в столбце A номер платежа, в B user_id, в C сумма. В строках 2–7 платежи 901, 903, 904, 906, 908 и 911 от пользователей 60, 972, 1021, 1481, 2392 и 2727 на 19, 19, 19, 29, 39 и 19 — всего 144.

Лист «Клиенты» — справочник, выгруженный 31 июля: user_id, дата регистрации, страна, канал привлечения. Семь клиентов, отсортированы по user_id. Двух вещей в нём нет, и это не подстроено: в базе у пользователя 1021 не заполнена страна, а пользователь 2727 зарегистрировался 1 августа, уже после выгрузки.

Перед формулой убедитесь, что ключ совпадает по смыслу и по типу: одинаковый заголовок id ещё не значит, что в обеих системах это номер клиента. Здесь оба столбца — номер пользователя, и оба хранят его числом.

Формула и результат: где ВПР ответил верно, а где нет

В ячейку D2 листа «Платежи» пишем =ВПР(B2;Клиенты!$A$2:$D$8;3;ЛОЖЬ) и протягиваем до D7. Знаки $ закрепляют диапазон справочника: при протягивании меняется только ссылка на ключ — B2, B3 и дальше до B7.

Платёж 911 получил #Н/Д: пользователя 2727 в справочнике нет. Это честная ошибка — её видно и можно посчитать. Опаснее платёж 904: у пользователя 1021 страна пустая, а Excel возвращает пустую ячейку как 0. В сводной появится страна «0», которую легко принять за мусор, а не за клиента с незаполненным полем.

Чтобы отличать «не найден» от «пусто», подпишите #Н/Д через ЕСНД: =ЕСНД(ВПР(B2;Клиенты!$A$2:$D$8;3;ЛОЖЬ);"нет в справочнике"). ЕСЛИОШИБКА тоже сработает, но заодно спрячет #ССЫЛКА! от неверного номера столбца, а это ошибка самой формулы, и её нужно видеть.

ВПР из другого листа и другой книги

Ссылка на другой лист — это имя листа и восклицательный знак перед диапазоном: Клиенты!$A$2:$D$8. Имя с пробелом Excel берёт в апострофы. Адрес не обязательно набирать: начните формулу, выделите диапазон мышью на листе справочника, а F4 добавит знаки $.

Справочник в другой книге работает так же, в ссылке появляется имя файла в квадратных скобках. Связь хрупкая: файл переименовали или перенесли — и формула смотрит в старую копию. Для еженедельного отчёта надёжнее загрузить справочник через Power Query.

Диапазон $A$2:$D$8 сам не растёт, когда в справочник дописывают клиентов. Новый клиент в строке 9 для формулы не существует, и его платежи получат #Н/Д. Выходов два: ссылаться на целые столбцы Клиенты!$A:$D или оформить справочник как таблицу Excel (Ctrl+T) — её диапазон расширяется вместе с новыми строками.

Интервальный просмотр: ЛОЖЬ против ИСТИНА

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

Вот что это значит для нашего примера. Уберите ЛОЖЬ из формулы для платежа 911 — справочник отсортирован, формально всё по правилам. Пользователя 2727 в нём нет, но ошибки не будет: Excel найдёт ближайший меньший ключ 2392 и вернёт KZ. Ни красной ячейки, ни предупреждения: Казахстан получит лишние 19, а дыра в справочнике останется незамеченной. На несортированном справочнике приблизительный поиск может вернуть чужую строку даже для ключа, который там есть.

Приблизительный поиск нужен для шкал, а не для идентификаторов. Допустим, комиссия от 0 ₽ — 5%, от 10 000 ₽ — 4%, от 50 000 ₽ — 3% (пороги условные). Для заказа на 12 500 ₽ формула с ИСТИНА вернёт 4% — последнюю ступень, не превышающую сумму; точный поиск дал бы #Н/Д.

Ошибка #Н/Д в ВПР: ключа нет, число как текст, лишний пробел

При точном поиске #Н/Д значит одно: Excel не нашёл в первом столбце значение, равное искомому. «Равное» здесь строже, чем кажется глазу. Число 972 и текст «972» выглядят одинаково, но для ВПР это разные значения. Ключ «1481 » с пробелом в конце не равен числу 1481.

Число как текст обычно приезжает из CSV; признаки — выравнивание по левому краю и зелёный треугольник в углу ячейки. Microsoft в справке ВПР советует не хранить числа и даты как текст. Быстрое исправление — ЗНАЧЕН(B3) в формуле, надёжное — правильный тип столбца при загрузке.

С пробелами есть ловушка второго порядка. СЖПРОБЕЛЫ убирает пробелы в начале и в конце, но возвращает текст: «1481 » превращается в «1481», а не в число, и #Н/Д остаётся. Для числовых ключей нужна пара ЗНАЧЕН(СЖПРОБЕЛЫ(A6)), результат которой вставляют обратно в справочник как значения. И ещё одно: СЖПРОБЕЛЫ удаляет только обычный пробел с кодом 32. Неразрывный пробел с кодом 160, который часто приезжает со скопированных веб-страниц, она не трогает — его убирают через ПОДСТАВИТЬ(A6;СИМВОЛ(160);"").

Когда #Н/Д много, найдите один ключ в справочнике через Ctrl+F. Нашёлся — дело в типе или невидимых символах. Не нашёлся — справочник неполный, и это вопрос к источнику данных.

Дубли ключа: ВПР берёт первое совпадение

Справочники копятся годами: клиента заводят повторно, новую выгрузку вставляют под старой. Допустим (учебная вставка), в строку 9 листа «Клиенты» дописали пользователя 1481 со страной BY — клиент переехал, старую строку не удалили. ВПР идёт сверху вниз, останавливается на строке 6, и платёж 906 остаётся в России.

О втором совпадении ВПР не сообщает. SQL-соединение размножило бы платёж, и сумма выросла бы; здесь меняется только подпись. Дубли стоит искать до формулы: СЧЁТЕСЛИ(Клиенты!$A:$A;B5) в соседнем столбце покажет, сколько раз ключ встречается в справочнике. Всё, что больше 1, требует решения, какая из строк правильная.

Если для ваших данных верно правило «последняя запись актуальна», ПРОСМОТРX найдёт её: шестой аргумент -1 включает поиск снизу вверх. Но правильнее держать в справочнике одну строку на ключ и проверять это при обновлении.

Вставили столбец — ВПР вернул не тот

Номер столбца в ВПР — число, а не ссылка. Вставьте между датой регистрации и страной столбец с месяцем регистрации: Excel расширит диапазон до $A$2:$E$8, но тройка останется тройкой, и вместо стран в отчёт поедут «2026-06» и «2026-07». Ошибки нет, формула выглядит как вчера.

Защита — вычислять номер столбца по заголовку: ПОИСКПОЗ("country";Клиенты!$A$1:$E$1;0) вернёт 4 после вставки и 3 до неё. Ещё проще перейти на ПРОСМОТРX или ИНДЕКС+ПОИСКПОЗ: они ссылаются на сам столбец результата, а такая ссылка при вставке сдвигается вместе со столбцом.

Удаление столбца заметнее, но не всегда. Если номер станет больше ширины диапазона, вы увидите #ССЫЛКА!. Если столбцов останется достаточно, формула молча вернёт соседний.

Абсолютные ссылки: почему формула верна в первой строке и врёт ниже

Если написать диапазон без $ — Клиенты!A2:D8 — и протянуть формулу вниз, диапазон поедет вместе с ней: в D3 это уже A3:D9, в D4 — A4:D10. Верхние строки справочника выпадают из поиска. В исходном порядке платежей это не проявляется: все найденные ключи остаются внутри сдвинутых диапазонов. Но отсортируйте платежи по убыванию суммы (908, 906, 901, 903, 904, 911) — и половина формул смотрит мимо нужного клиента.

Справка Microsoft советует абсолютные ссылки на таблицу именно ради протягивания. Клавиша F4 при курсоре на ссылке переключает её вид: A2, $A$2, A$2, $A2. Ссылку на ключ (B2) оставляйте относительной — она и должна меняться от строки к строке.

Регистр букв ВПР не различает

Для ВПР «pro» и «PRO» — одно и то же. Это мешает там, где регистр значим: в промокодах и токенах. Допустим (пример условный), в справочнике промокодов сначала идёт aB3k со скидкой 10%, а ниже Ab3K со скидкой 30%. Формула =ВПР("Ab3K";Промокоды!$A$2:$B$3;2;ЛОЖЬ) вернёт 10% — первую строку, совпавшую без учёта регистра.

ПОИСКПОЗ, по справке Microsoft, регистр тоже не различает. Нужна СОВПАД: =ИНДЕКС(Промокоды!$B$2:$B$3;ПОИСКПОЗ(ИСТИНА;СОВПАД(Промокоды!$A$2:$A$3;"Ab3K");0)) вернёт 30% (в старых версиях Excel — ввод через Ctrl+Shift+Enter). В SQL сравнение строк учитывает регистр, и соединение по таким кодам ведёт себя иначе.

ВПР, ПРОСМОТРX или ИНДЕКС+ПОИСКПОЗ: что выбрать

Справка Microsoft называет ПРОСМОТРX улучшенной версией ВПР. Столбцы поиска и результата задаются отдельно, поиск по умолчанию точный, четвёртый аргумент — текст на случай, если ничего не нашлось: =ПРОСМОТРX(B2;Клиенты!$A$2:$A$8;Клиенты!$C$2:$C$8;"нет в справочнике"). В названии функции латинская X. Она есть в Excel для Microsoft 365, Excel 2021 и 2024 (в том числе для Mac) и в мобильных версиях; в Excel 2016 и 2019 её нет. В Google Таблицах есть XLOOKUP, тоже с точным поиском по умолчанию.

ИНДЕКС+ПОИСКПОЗ работает в любой версии: =ИНДЕКС(Клиенты!$C$2:$C$8;ПОИСКПОЗ(B2;Клиенты!$A$2:$A$8;0)). ПОИСКПОЗ находит номер строки ключа, ИНДЕКС берёт значение с этим номером из столбца результата. Ноль в ПОИСКПОЗ обязателен: без него, как и у ВПР, идёт приблизительный поиск, которому нужна сортировка по возрастанию.

На чистом справочнике все три дают KZ, AM, 0, RU, KZ и #Н/Д (у ПРОСМОТРX — «нет в справочнике»). Разница — в том, как они переживают изменения справочника: для разовой сверки хватит ВПР с ЛОЖЬ, для живого справочника берите ПРОСМОТРX, а при старых версиях Excel у коллег — ИНДЕКС+ПОИСКПОЗ.

ВПР — это LEFT JOIN по одному ключу: то же на базе «Маяк»

Если перевести ВПР на язык баз данных, получится LEFT JOIN: левая таблица — та, где стоит формула, правая — справочник, условие соединения — равенство ключей. Все строки слева сохраняются, у ненайденных справа пусто: в SQL это NULL, в Excel — #Н/Д. Но ВПР берёт первое совпадение и не различает регистр, а SQL возвращает все совпадения и регистр учитывает.

Проверим на всей базе. В «Маяке» 1 251 платёж на 30 639. Справочником, как в примере, служат клиенты, зарегистрированные по 31 июля включительно, — 2 718 строк, ключ в них уникален. Запрос ниже — ВПР, протянутый на все платежи, и сразу сводная по результату, включая ненайденных клиентов и пустые страны. Выполнить его можно в SQL-песочнице на той же базе.

Без пары остались 256 платежей на 6 234 — 20,3% выручки: августовских клиентов справочник ещё не знает. В Excel это 256 ячеек #Н/Д, и сводная по найденным клиентам показала бы 24 405 вместо 30 639. Ещё 7 платежей на 233 нашли клиента без страны — в Excel там был бы 0.

WITH clients AS (
  SELECT user_id, country
  FROM users
  WHERE signup_date <= DATE '2026-07-31'
)
SELECT
  CASE
    WHEN c.user_id IS NULL THEN 'нет в справочнике'
    WHEN c.country IS NULL THEN 'найден, страна пустая'
    ELSE c.country
  END AS country,
  COUNT(*) AS payments,
  SUM(p.amount) AS revenue
FROM payments AS p
LEFT JOIN clients AS c ON c.user_id = p.user_id
GROUP BY 1
ORDER BY payments DESC;

Дубли в справочнике: SQL размножает строки, ВПР молчит

Повторим историю с дублем на всей базе. В запросе к справочнику добавлены две строки — это учебная вставка, в самой базе дублей нет: клиенту 320 приписана вторая страна RU (в справочнике у него KZ), клиенту 444 — AM (было RU). У каждого из них по три платежа.

Обычный LEFT JOIN возвращает каждую пару совпавших строк, поэтому шесть платежей этих клиентов удвоились: 1 257 строк вместо 1 251 и выручка 30 873 вместо 30 639, на 234 больше. Вариант «как ВПР» оставляет для ключа первую строку по порядку загрузки (row_order) и возвращает исходные 1 251 строку и 30 639. Сумма сходится, но клиенту 320 приписан KZ лишь потому, что эта строка выше.

У строк в базе нет порядка «сверху вниз», поэтому правило первого совпадения в SQL записывают явно. В Excel оно действует тоже, только его никто не выбирал. Как ловить размножение строк — в статье о JOIN без потерь и дублей.

WITH clients AS (
  SELECT user_id, country, 1 AS row_order
  FROM users
  WHERE signup_date <= DATE '2026-07-31'
  UNION ALL
  -- учебная вставка: повторные строки для двух клиентов
  SELECT user_id, country, row_order
  FROM (VALUES (320, 'RU', 2), (444, 'AM', 2)) AS added(user_id, country, row_order)
),
first_match AS (
  SELECT user_id, country
  FROM (
    SELECT user_id, country,
      ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY row_order) AS rn
    FROM clients
  ) AS ranked
  WHERE rn = 1
)
SELECT 'LEFT JOIN как есть' AS method,
  COUNT(*) AS rows_after,
  SUM(p.amount) AS revenue,
  COUNT(*) FILTER (WHERE p.user_id = 320) AS rows_user_320
FROM payments AS p
LEFT JOIN clients AS c ON c.user_id = p.user_id
UNION ALL
SELECT 'первое совпадение, как ВПР',
  COUNT(*),
  SUM(p.amount),
  COUNT(*) FILTER (WHERE p.user_id = 320)
FROM payments AS p
LEFT JOIN first_match AS f ON f.user_id = p.user_id
ORDER BY rows_after DESC;

Как проверить результат ВПР

После протягивания формулы посчитайте несколько чисел, прежде чем строить сводную. Сколько строк не нашли пару: =СУММПРОИЗВ(--ЕНД(D2:D7)). Сколько денег за ними стоит: =СУММПРОИЗВ(ЕНД(D2:D7)*C2:C7). Сколько строк получили 0 вместо значения: =СЧЁТЕСЛИ(D2:D7;0). И сколько раз каждый ключ встречается в справочнике: столбец =СЧЁТЕСЛИ(Клиенты!$A:$A;B2) с фильтром по значениям больше 1.

Затем сверьте суммы. ВПР не добавляет строк, поэтому сумма столбца C не меняется; проверять нужно, что итог сводной по странам равен общей сумме. Если #Н/Д и нули отфильтровали, итог окажется меньше. В нашем примере общая сумма 144, из них 19 приходится на #Н/Д и ещё 19 — на страну «0». Сводная только по найденным странам покажет 106, и разницу в 38 нужно объяснить, а не потерять.

В pandas аналог ВПР — merge с how="left" к справочнику без дублей. indicator=True показывает строки без пары, validate="many_to_one" останавливает расчёт при неуникальном ключе — проверка, которой ВПР не делает. Пустая страна 1021 здесь None, а не 0. Подробнее — в статье про groupby и merge.

import pandas as pd

payments = pd.DataFrame({
    "payment_id": [901, 903, 904, 906, 908, 911],
    "user_id": [60, 972, 1021, 1481, 2392, 2727],
    "amount": [19, 19, 19, 29, 39, 19],
})
clients = pd.DataFrame({
    "user_id": [60, 61, 972, 1021, 1481, 1641, 2392],
    "country": ["KZ", "RU", "AM", None, "RU", "RU", "KZ"],
})

# ВПР с ЛОЖЬ: первое совпадение сверху, число строк слева не меняется
lookup = clients.drop_duplicates("user_id", keep="first")
result = payments.merge(lookup, on="user_id", how="left",
                        validate="many_to_one", indicator=True)
print(result[["payment_id", "user_id", "country", "_merge"]].to_string(index=False))
print("нет в справочнике:", (result["_merge"] == "left_only").sum())
print("найден, страна пустая:",
      (result["country"].isna() & (result["_merge"] == "both")).sum())
print("сумма до и после:", payments["amount"].sum(), result["amount"].sum())

# дубль ключа: merge без проверки размножит платёж, validate остановит расчёт
clients_dup = pd.concat(
    [clients, pd.DataFrame({"user_id": [1481], "country": ["BY"]})],
    ignore_index=True,
)
print("строк после merge с дублем:",
      len(payments.merge(clients_dup, on="user_id", how="left")))
try:
    payments.merge(clients_dup, on="user_id", how="left", validate="many_to_one")
except pd.errors.MergeError as error:
    print("MergeError:", error)

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

Итог

ВПР — это LEFT JOIN по одному ключу с тремя молчаливыми правилами: первое совпадение сверху, пустая ячейка как 0 и приблизительный поиск, если не написать ЛОЖЬ. Пишите ЛОЖЬ, закрепляйте диапазон $, проверяйте дубли ключа и считайте #Н/Д с суммой за ними до построения отчёта. Для живого справочника берите ПРОСМОТРX или ИНДЕКС+ПОИСКПОЗ.

Дальше результат обычно идёт в сводную таблицу и дашборд в Excel — сверяйте итог на каждом шаге. Формулу подскажет и нейросеть, но проверки остаются за вами. Когда справочников много и они обновляются ежедневно, соединяйте таблицы запросом: начните с LEFT JOIN в SQL.