ВПР в Excel: примеры, ошибки, XLOOKUP

Коротко: ВПР (в английском Excel — VLOOKUP) подтягивает значение из другой таблицы по совпадению ключа: берёт код товара в вашей таблице, находит его в справочнике и возвращает цену или название. Это ручной аналог SQL-джойна. Главные грабли: приблизительное совпадение по умолчанию, ошибка #Н/Д из-за лишних пробелов и разных типов данных, невозможность искать влево и один ключ вместо составного. XLOOKUP решает половину этих проблем, а на объёмах от десятков тысяч строк матчинг честнее и быстрее делать через JOIN в SQL.

Что такое ВПР и почему это «JOIN для Excel»?

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

В английской версии — =VLOOKUP(lookup_value; table_array; col_index_num; range_lookup).

Представьте два листа. На первом — заказы с колонкой product_id. На втором — справочник товаров: product_id, name, price. Чтобы к каждому заказу подтянуть название, вы пишете:

Здесь A2 — код из строки заказа, Справочник!$A$2:$C$500 — таблица, 2 — вернуть второй столбец (название), ЛОЖЬ — искать точное совпадение. Обратите внимание на $ — абсолютные ссылки, чтобы диапазон не «съезжал» при протягивании формулы вниз.

По смыслу это ровно то же, что делает реляционная база:

SELECT o.order_id,
       o.product_id,
       p.name,
       p.price
FROM orders o
LEFT JOIN products p
  ON o.product_id = p.product_id;

Точное или приблизительное совпадение — когда что выбирать?

Четвёртый аргумент ВПР — самый коварный. Он определяет режим поиска:

• ЛОЖЬ (или 0) — точное совпадение. Функция ищет ровно ваше значение и, если не находит, возвращает #Н/Д.
• ИСТИНА (или 1), либо аргумент вообще опущен — приблизительное совпадение. Функция предполагает, что первый столбец отсортирован по возрастанию, и возвращает ближайшее значение, не превышающее искомое.

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

Приблизительное совпадение реально нужно в одном сценарии — разбивка по диапазонам. Например, шкала скидок или грейдов:

Здесь =ВПР(B2; Шкала!$A$2:$B$5; 2; ИСТИНА) для заказа на 63 000 вернёт 10%, потому что 50 000 — ближайшая граница снизу. Ключевое условие: столбец с границами обязан быть отсортирован по возрастанию, иначе результат непредсказуем. В SQL то же самое выражается через CASE или неравенство в условии соединения — и там сортировка не требуется, что надёжнее.

Почему ВПР возвращает #Н/Д и как это починить?

• Лишние пробелы. Код А-100 с пробелом на конце не равен А-100. Лечится через =СЖПРОБЕЛЫ(A2) (TRIM) с обеих сторон.
• Разные типы данных. Классика: в одной таблице 12345 — это число, в другой — текст (число, сохранённое как текст, с зелёным уголком в ячейке). Для Excel это разные значения. Приведите оба к одному типу: текст через =ТЕКСТ(A2;"0"), число — умножением на 1 или через ЗНАЧЕН.
• Искомое не в первом столбце диапазона. ВПР ищет строго в левом столбце таблицы. Если ключ во втором столбце — поиск не сработает никогда.
• Неполный диапазон. Забыли зафиксировать $, протянули формулу вниз — и нижняя часть справочника «уехала» из области поиска.
• Разный регистр и похожие символы. Латинская C и кириллическая С выглядят одинаково, но это разные символы. Частая беда в кодах, набранных вручную.

Чтобы #Н/Д не ломал сводные и графики, оборачивайте формулу в ЕСЛИОШИБКА:

Но злоупотреблять этим нельзя. ЕСЛИОШИБКА прячет проблему, а не решает её — вы просто перестаёте видеть, сколько ключей не сматчилось. В аналитике доля несматченных строк — это диагностика качества данных. В SQL её считают одним запросом:

SELECT COUNT(*) AS total,
       COUNT(p.product_id) AS matched,
       COUNT(*) - COUNT(p.product_id) AS unmatched
FROM orders o
LEFT JOIN products p
  ON o.product_id = p.product_id;

Если unmatched внезапно вырос — сломался справочник или изменился формат кодов. Это тот же класс проблем, что и дубли и потери при объединении данных: невидимые в Excel, они искажают все метрики ниже по цепочке — от выручки до удержания.

Как искать по двум условиям?

ВПР умеет искать только по одному ключу. А в реальности нужно совпадение по паре: город плюс товар, дата плюс менеджер, регион плюс канал. Три рабочих способа.

Способ 1. Столбец-склейка. Добавляете в обе таблицы служебный столбец, где соединяете ключи:

Получаете Москва|А-100. Дальше обычный ВПР по этому склеенному ключу. Разделитель (|) обязателен, иначе 12 + 3 и 1 + 23 дадут одинаковый 123 и вы получите ложные совпадения. Способ простой, но замусоривает таблицу и ломается, если исходные ключи меняются.

Способ 2. СУММПРОИЗВ (SUMPRODUCT) — если нужно вернуть число:

Каждое условие даёт массив из 1 и 0, произведение оставляет единицу только там, где совпали оба ключа, и умножает на нужный столбец. Работает, только когда совпадение уникально.

Способ 3. XLOOKUP по составному массиву (о нём ниже) — самый чистый в современных версиях.

Честно говоря, поиск по двум условиям — это признак того, что задача переросла Excel. В SQL составной ключ соединяется естественно, без костылей:

SELECT s.city,
       s.product_id,
       pr.plan_price
FROM sales s
LEFT JOIN price_list pr
  ON s.city = pr.city
 AND s.product_id = pr.product_id;

Просто добавляете второе условие в ON через AND. Ни склеек, ни служебных столбцов. Потренировать соединение по нескольким полям и другие join-паттерны можно прямо в SQL-тренажёре на реальных таблицах.

Почему ВПР не умеет левый поиск и что делать?

Жёсткое ограничение: ВПР возвращает значение только из столбца, который находится правее столбца поиска. Искомое всегда в первом (левом) столбце диапазона, а вернуть можно колонку с индексом 2, 3 и так далее — то есть вправо. Если ключ у вас справа, а нужное значение слева — ВПР бессилен.

Пример: справочник, где в столбце A — название, в столбце B — код. Вы знаете код, хотите название. ВПР по коду искать название слева не сможет. Два выхода.

Классика — ИНДЕКС + ПОИСКПОЗ (INDEX + MATCH):

Современный способ — XLOOKUP, которому вообще всё равно, где находится столбец возврата относительно столбца поиска.

Для баз данных проблемы «лево-право» не существует в принципе: в JOIN порядок столбцов не имеет значения, вы выбираете в SELECT любые поля из любой таблицы. Это одна из причин, почему аналитики довольно быстро уходят из Excel в SQL — там нет геометрических ограничений на расположение данных.

Чем XLOOKUP лучше ВПР?

XLOOKUP (в русской версии тоже XLOOKUP или ПРОСМОТРX) появился в Excel 365 и 2021 и закрывает почти все боли ВПР. Синтаксис:

Что стало лучше:

• Левый поиск из коробки. Массив поиска и массив возврата — независимые диапазоны, порядок не важен. Никаких ИНДЕКС/ПОИСКПОЗ.
• Точное совпадение по умолчанию. Не нужно помнить про ЛОЖЬ — режим 0 (точное) включён сразу. Это убирает целый класс тихих ошибок.
• Встроенная обработка «не найдено». Четвёртый аргумент заменяет ЕСЛИОШИБКА: =XLOOKUP(A2; коды; названия; "нет в справочнике").
• Устойчивость к вставке столбцов. Нет жёсткого номера столбца — возвращается конкретный диапазон.
• Поиск снизу вверх через режим поиска -1 — удобно, когда нужно последнее совпадение (например, последняя цена товара).

Сравнение в одной таблице:

Единственный минус XLOOKUP — он недоступен в Excel 2016 и 2019. Если файл открывают коллеги на старых версиях, формула вернёт #ИМЯ?. Поиск по двум условиям в XLOOKUP делается перемножением массивов прямо в аргументе поиска: =XLOOKUP(1; (город=A2)*(товар=B2); результат).

Где Excel упирается и как тот же матчинг делает SQL JOIN?

ВПР и XLOOKUP отлично работают на нескольких тысячах строк. Дальше начинаются стены:

• Лимит листа — 1 048 576 строк. Реальные выгрузки заказов или событий это превышают легко.
• Пересчёт тормозит. Каждая формула ВПР — отдельный поиск. 200 000 формул превращают файл в слайд-шоу, а автосохранение — в риск потерять полчаса работы.
• Нет истории и воспроизводимости. «Кто и когда протянул не тот диапазон» в Excel не отследить. SQL-запрос — это текст, который всегда даёт один результат.
• Один справочник за раз. Соединить пять таблиц в Excel — пять слоёв ВПР и молитва, чтобы ни один диапазон не съехал.

Тот же матчинг в базе выглядит спокойно и масштабируется на десятки миллионов строк:

SELECT o.order_id,
       c.city,
       p.name,
       p.price,
       o.qty * p.price AS revenue
FROM orders o
LEFT JOIN products p ON o.product_id = p.product_id
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE o.order_date >= '2026-01-01';

Три таблицы соединены за один запрос, тут же посчитана выручка, и всё это переживёт перезапуск без единого «уехавшего» диапазона.

Если вы больше про Python, чем про SQL, аналог ВПР — это merge в pandas:

import pandas as pd

orders = pd.read_excel("orders.xlsx")
products = pd.read_excel("products.xlsx")

result = orders.merge(
    products,
    on="product_id",
    how="left",       # LEFT JOIN: оставляем все заказы
    validate="m:1"    # ловим дубли в справочнике
)

# сколько ключей не сматчилось
unmatched = result["name"].isna().sum()
print(f"Не найдено товаров: {unmatched}")

Аргумент validate="m:1" — то, чего в ВПР нет вовсе: он падает с ошибкой, если в справочнике задвоился ключ и матчинг размножит строки. Это спасает от самой опасной тихой ошибки при объединении. Разбор всех режимов слияния есть в отдельном гайде про merge, join и concat в pandas, а как вообще читать Excel и CSV в датафрейм — в части про чтение и запись данных. Отработать merge на живых данных удобно в Python-тренажёре.

С чего начать переход с ВПР на JOIN

Логика идентична, синтаксис проще, чем кажется:

• «Искомое = первый столбец справочника» превращается в условие ON ключ_левой = ключ_правой.
• «Оставить все строки, даже без совпадения» — это LEFT JOIN (ровно поведение ВПР).
• «Только совпавшие строки» — INNER JOIN.
• Поиск по двум условиям — просто AND в блоке ON, без склеек и служебных столбцов.

JOIN — базовый навык на любом собеседовании аналитика, и его спрашивают буквально всегда. Возьмите первый модуль SQL-курса, чтобы разложить джойны по полочкам, а потом прогоните себя по реальным вопросам с собеседований. Если хочется живой обратной связи по коду и разбора ошибок — AI-наставник объяснит, почему запрос отдаёт дубли или пустоту. Под рукой держите шпаргалку по SQL для аналитика, а для промежуточного слоя между Excel и SQL посмотрите Power Query — он умеет объединять таблицы без формул и без лимита в миллион строк.

ВПР — отличный первый инструмент, чтобы понять саму идею матчинга. Но как только выгрузки перестают помещаться на лист, а справочников становится больше одного, честнее и быстрее делать то же самое через JOIN. Соберите первый рабочий запрос в SQL-тренажёре: первые пять задач открыты бесплатно, а дальше — полный доступ по Pro, если захотите пройти весь путь от SELECT до оконных функций.