ВПР возвращает неправильное значение: 6 причин, о которых молчат. Разбор частых причин, почему ВПР (VLOOKUP) в Excel находит не те значения: приблизительный поиск, несортированный диапазон, дубликаты, пробелы и типы данных. Диагностика + файл для практики.
В прошлой части мы разобрали, почему формула может не работать вообще.
Теперь сложнее. Формула работает. Ошибки нет. Но возвращает не то значение. Это опаснее. Потому что вы верите результату.
Сегодня — полный разбор ВПР и всех реальных причин “тихих” ошибок.
Файл с тестовой таблицей лежит в Telegram — там 6 сценариев, где ВПР считает неправильно.
Мини-кейс
Формула:
=ВПР(A2;F2:G20;2;ИСТИНА)
Вы ищете цену товара по коду. Ожидаете 5 480. Получаете 4 950. Ошибка нет. Но цифра не та. Начинаем диагностику.
Причина 1. Используется ИСТИНА вместо ЛОЖЬ
Это главная ловушка. Последний аргумент ВПР определяет тип поиска:
ИСТИНА — приблизительный поиск
ЛОЖЬ — точное совпадение
Если указан ИСТИНА, Excel ищет ближайшее значение, а не точное.
Проверка
Посмотрите последний аргумент формулы. Если там ИСТИНА или 1 — вы используете приблизительный поиск.
Решение №1 (стандартное)
Заменить на:
=ВПР(A2;F2:G20;2;ЛОЖЬ)
Решение №2 (современное)
Использовать XLOOKUP (если версия позволяет):
=XLOOKUP(A2;F2:F20;G2:G20)
Он по умолчанию ищет точное совпадение.
Причина 2. Диапазон не отсортирован (при ИСТИНА)
Если используется ИСТИНА, диапазон должен быть строго отсортирован по возрастанию. Если нет — Excel будет возвращать случайно “ближайшее” значение.
Проверка
Отсортируйте первый столбец вручную. Если результат изменился — причина найдена.
Решение
Либо сортировать данные, либо перейти на ЛОЖЬ.
Причина 3. Дубликаты в первом столбце
ВПР всегда возвращает первое найденное совпадение. Если в таблице два одинаковых кода с разной ценой — получите первое.
Проверка
Выделить столбец → Условное форматирование → Найти дубликаты.
Решение №1
Удалить дубликаты.
Решение №2 (более гибкое)
Использовать ИНДЕКС + ПОИСКПОЗ с дополнительным условием.
Причина 4. Текст и число выглядят одинаково
Код “00125” и 125 — разные значения. Визуально одинаково. Логически — нет.
Проверка
=A2=B2
Если ЛОЖЬ — типы разные.
Решение №1
Привести оба к числу: =ЗНАЧЕН(A2)
Решение №2
Привести к тексту: =ТЕКСТ(A2;"00000")
Причина 5. Скрытые пробелы
Если код скопирован из веб-сайта, перед ним может быть невидимый символ.
Проверка
=ДЛСТР(A2)
Если длина больше ожидаемой — есть лишние символы.
Решение
=СЖПРОБЕЛЫ(A2)
или
=ПОДСТАВИТЬ(A2;" ";"")
Причина 6. Столбец для возврата указан неверно
Третий аргумент ВПР — номер столбца в диапазоне. Если диапазон F2:H20, а указан “2”, возвращается второй столбец, а не третий.
Проверка
Посчитать вручную: какой столбец внутри диапазона является нужным?
Решение
Проверить нумерацию. Либо перейти на XLOOKUP, где столбец указывается явно.
Диагностический алгоритм для ВПР
Когда результат неправильный:
- Проверяем последний аргумент (ЛОЖЬ?)
- Проверяем сортировку (если ИСТИНА)
- Проверяем дубликаты
- Проверяем типы данных
- Проверяем пробелы
- Проверяем номер столбца
Не меняйте всё сразу. Идите сверху вниз.
Что если…
…нужно искать “влево”?
ВПР не умеет.
Используйте ИНДЕКС + ПОИСКПОЗ.
…таблица постоянно расширяется?
Закрепите диапазон через Ctrl+T.
…формула копируется вниз и ломается?
Проверьте абсолютные ссылки ($).
Контроль результата
После исправления:
- Проверьте 5 случайных строк вручную.
- Сравните результат с фильтрацией по коду.
- Убедитесь, что нет дубликатов.
В Telegram — файл с 6 сценариями:
— приблизительный поиск
— несортированный диапазон
— дубликаты
— разные типы
— пробелы
— неверный номер столбца
Там же — альтернативные формулы.
В следующей части разберём, почему таблица начинает тормозить даже на мощном компьютере.
Напишите в комментариях: вы чаще ошибаетесь с ИСТИНА или с типами данных?