ВПР — самая полезная функция в таблицах: она находит значение в другой таблице и подтягивает к нему нужные данные. Классический пример: есть список артикулов в отчёте и есть прайс-лист, надо сопоставить цены, не листая тысячу строк вручную.
Как устроена формула
У функции четыре аргумента: что искать, где искать, какой столбец вернуть и тип поиска. Выглядит это так:
=ВПР(A2; Прайс!A:D; 3; ЛОЖЬ)Здесь A2 — артикул, который ищем; Прайс!A:D — диапазон, где идёт поиск; 3 — номер столбца внутри этого диапазона, значение из которого нужно вернуть; ЛОЖЬ — требование точного совпадения. В англоязычной версии функция называется VLOOKUP, а вместо ЛОЖЬ пишут FALSE.
Почему номер столбца считают не от начала листа
Третий аргумент — это порядковый номер внутри выбранного диапазона, а не буква столбца на листе. Если диапазон начинается с C, то цены из столбца E будут третьим столбцом: C — первый, D — второй, E — третий. Из-за этой путаницы формула чаще всего и возвращает не те данные.
Точный поиск против приблизительного
| Последний аргумент | Что делает | Когда нужен |
|---|---|---|
| ЛОЖЬ (FALSE) | Ищет точное совпадение | Почти всегда: артикулы, фамилии, номера |
| ИСТИНА (TRUE) | Берёт ближайшее меньшее | Диапазоны: шкала скидок, налоговые ставки |
Приблизительный поиск требует, чтобы первый столбец был отсортирован по возрастанию, иначе результат будет случайным. Если сомневаетесь — ставьте ЛОЖЬ.
Ошибка Н/Д: что проверить
- Лишние пробелы. Значение выглядит одинаково, но в одной таблице есть пробел в конце. Лечится функцией СЖПРОБЕЛЫ.
- Число как текст. Артикул из выгрузки часто приходит текстом, а в прайсе лежит числом — для таблицы это разные вещи.
- Значения правда нет. Тогда Н/Д — правильный ответ, а не ошибка.
- Диапазон съехал. При копировании формулы вниз ссылка на прайс должна быть закреплена знаками доллара или указана целыми столбцами.
Чтобы вместо Н/Д показывать прочерк или ноль, оберните формулу так:
=ЕСЛИОШИБКА(ВПР(A2; Прайс!A:D; 3; ЛОЖЬ); "не найдено")Что использовать вместо ВПР
В современных версиях появилась функция ПРОСМОТРX (XLOOKUP): она умеет искать и влево, и вправо, не требует номера столбца и сама подставляет значение при отсутствии совпадения. Если у вас свежая версия, начинайте сразу с неё. Универсальная замена для старых версий — связка ИНДЕКС и ПОИСКПОЗ: она работает в любую сторону и не ломается при вставке столбцов.
Частые ошибки
- Считают номер столбца от начала листа, а не от начала диапазона.
- Не закрепляют диапазон и получают мусор при копировании формулы.
- Оставляют приблизительный поиск на неотсортированных данных.
- Прячут ошибки через ЕСЛИОШИБКА, не разобравшись, почему совпадений нет.
Что запомнить
Искомое значение — всегда в первом столбце диапазона, номер столбца считается внутри диапазона, а последний аргумент почти всегда ЛОЖЬ. Больше половины ошибок Н/Д — это пробелы и числа, сохранённые как текст. Если версия позволяет, переходите на ПРОСМОТРX и забудьте про номера столбцов.
Коротко: вопросы и ответы
Можно ли искать влево? ВПР — нет. Используйте ПРОСМОТРX или связку ИНДЕКС и ПОИСКПОЗ.
Почему формула вернула не ту цену? Скорее всего, включён приблизительный поиск или неверно посчитан номер столбца.
Работает ли ВПР между файлами? Да, но при закрытом файле-источнике данные не обновляются. Надёжнее скопировать прайс на отдельный лист.
Комментарии