ВПР в Excel VLOOKUP вертикальный просмотр подстановка данных Excel формулы как сделать ВПР поиск значений Excel XLOOKUP ИНДЕКС ПОИСКПОЗ
Функция ВПР (VLOOKUP) — это один из самых востребованных инструментов Excel. По разным оценкам, именно запросы «как сделать ВПР в Excel» и «как пользоваться функцией ВПР» входят в топ-3 самых популярных поисковых запросов по Excel. В этой статье вы найдёте пошаговую инструкцию, живые примеры формул с возможностью копирования, разбор типичных ошибок и современные альтернативы.
Содержание
- 1. Что такое ВПР и зачем она нужна
- 2. Синтаксис функции ВПР
- 3. Пошаговая инструкция: как сделать ВПР
- 4. Примеры использования ВПР
- 5. Точное и приблизительное совпадение
- 6. Типичные ошибки ВПР и как их исправить
- 7. Современная замена: функция ПРОСМОТРX (XLOOKUP)
- 8. Альтернатива: ИНДЕКС + ПОИСКПОЗ
- 9. Как скрыть ошибки с помощью ЕСЛИОШИБКА
- 10. FAQ: частые вопросы о ВПР
1. Что такое ВПР и зачем она нужна
ВПР (в англоязычной версии — VLOOKUP, от Vertical Lookup, «вертикальный просмотр») — это функция поиска, которая находит значение в крайнем левом столбце таблицы и возвращает значение из указанного столбца той же строки.
Зачем использовать ВПР:
- Быстро подтянуть данные из одной таблицы в другую по общему ключу (ID, название, артикул)
- Автоматизировать связывание прайс-листов с заказами
- Добавить в отчёт цены, категории, ФИО менеджеров из справочников
- Избежать ручного копирования при работе с тысячами строк
Важно: ВПР ищет данные только в первом (левом) столбце указанного диапазона. Если искомое значение находится правее возвращаемого — ВПР не подойдёт, используйте ИНДЕКС+ПОИСКПОЗ или XLOOKUP.
2. Синтаксис функции ВПР
Формула ВПР состоит из 4 аргументов:
Синтаксис ВПР=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])Копировать
| Аргумент | Описание | Обязательный |
|---|---|---|
| искомое_значение | То, что ищем (ячейка или текст/число) | Да |
| таблица | Диапазон, где ищем. Ключевой столбец — всегда первый слева | Да |
| номер_столбца | Порядковый номер столбца в диапазоне, откуда берём результат | Да |
| интервальный_просмотр | ЛОЖЬ — точное совпадение; ИСТИНА — приблизительное | Нет (по умолчанию ИСТИНА) |
3. Пошаговая инструкция: как сделать ВПР
1Подготовьте данные. Убедитесь, что в обеих таблицах есть общий столбец (ключ). Например, «Артикул» или «Название товара».
2Выберите ячейку, куда нужно подтянуть результат.
3Введите формулу. Начните с =ВПР( или =VLOOKUP( в английской версии.
4Укажите искомое значение — кликните на ячейку с ключом из первой таблицы.
5Выделите таблицу-источник (включая ключевой столбец и столбец с нужными данными). Зафиксируйте диапазон клавишей F4 (добавятся знаки $).
6Введите номер столбца с результатом (считая от левого края выделенного диапазона).
7Укажите ЛОЖЬ (или 0) для точного совпадения. Закройте скобку и нажмите Enter.
8Протяните формулу вниз за маркер автозаполнения (маленький квадратик в правом нижнем углу ячейки).
4. Примеры использования ВПР
Пример 1: Подстановка цен из прайс-листа
Есть таблица заказов (столбец A — «Товар», нужен столбец с ценой) и прайс-лист на листе «Прайс» (столбец A — «Товар», столбец B — «Цена»).
Формула ВПР для подстановки цены=ВПР(A2;Прайс!$A$2:$B$100;2;ЛОЖЬ)Копировать
Как это работает: Excel ищет название товара из A2 в первом столбце диапазона A2:B100 на листе «Прайс». Когда находит — возвращает значение из второго столбца (цена).
Пример 2: Поиск сотрудника по ID
В таблице с ID сотрудников (столбец A) нужно подтянуть их должности из справочника (ID в столбце D, должность в столбце F).
ВПР с поиском по ID=ВПР(A2;$D$2:$F$50;3;ЛОЖЬ)Копировать
Пример 3: ВПР между разными файлами
Если прайс находится в другом файле Excel (например, «Прайс.xlsx»):
ВПР из другого файла=ВПР(A2;'[Прайс.xlsx]Лист1'!$A$2:$B$100;2;ЛОЖЬ)Копировать
Совет: При работе с внешними файлами оба документа должны быть открыты. Для надёжности лучше собирать данные в одной книге на разных листах.
5. Точное и приблизительное совпадение
Точное совпадение (ЛОЖЬ / 0)
Используйте, когда нужно найти именно то значение, которое указано. Если совпадения нет — Excel вернёт ошибку #Н/Д.
Точный поиск=ВПР("Яблоки";A2:C10;3;ЛОЖЬ)Копировать
Приблизительное совпадение (ИСТИНА / 1)
Используется для диапазонов: скидки, налоговые ставки, уровни премий. Таблица должна быть отсортирована по возрастанию по ключевому столбцу.
Приблизительный поиск (диапазон скидок)=ВПР(350;A2:B10;2;ИСТИНА)Копировать
| Параметр | Когда использовать | Что вернёт, если нет совпадения |
|---|---|---|
| ЛОЖЬ (0) | Текстовые ключи, артикулы, ID, названия | Ошибка #Н/Д |
| ИСТИНА (1) | Числовые диапазоны, шкалы, тарифы | Ближайшее меньшее значение |
6. Типичные ошибки ВПР и как их исправить
Ошибка #Н/Д
Причины:
- Искомого значения нет в первом столбце таблицы
- Лишние пробелы или непечатаемые символы в данных
- Разный формат данных (число vs текст)
- Неправильно указан номер столбца
Исправление: обрезка пробелов=ВПР(СЖПРОБЕЛЫ(A2);$D$2:$F$50;3;ЛОЖЬ)Копировать
Ошибка #ЗНАЧ!
Номер столбца меньше 1 или больше количества столбцов в диапазоне.
Ошибка #ССЫЛКА!
Диапазон таблицы указан неверно или был удалён.
ВПР возвращает не тот результат
Проверьте, что ключевой столбец — самый левый в выделенном диапазоне. ВПР не умеет искать справа налево.
7. Современная замена: функция ПРОСМОТРX (XLOOKUP)
В Excel 365 и Excel 2021+ появилась функция ПРОСМОТРX (XLOOKUP) — улучшенная версия ВПР. Она решает главные проблемы: ищет в любом направлении, не требует номера столбца, корректно обрабатывает отсутствие совпадений.
XLOOKUP — замена ВПР=ПРОСМОТРX(A2;Прайс!$A$2:$A$100;Прайс!$B$2:$B$100;"Не найдено")Копировать
Преимущества XLOOKUP:
- Поиск в любом направлении (влево, вправо, вверх, вниз)
- Не нужно считать номер столбца — указываете отдельные массивы
- Встроенный параметр для значения при отсутствии совпадения
- Работает быстрее на больших данных
8. Альтернатива: ИНДЕКС + ПОИСКПОЗ
Для старых версий Excel (2016, 2019 и ранее) лучшая альтернатива ВПР — комбинация ИНДЕКС + ПОИСКПОЗ. Она гибче: ищет в любом столбце и возвращает из любого.
ИНДЕКС + ПОИСКПОЗ=ИНДЕКС($F$2:$F$50;ПОИСКПОЗ(A2;$D$2:$D$50;0))Копировать
Как работает: ПОИСКПОЗ находит позицию значения A2 в столбце D, а ИНДЕКС возвращает соответствующее значение из столбца F.
9. Как скрыть ошибки с помощью ЕСЛИОШИБКА
Чтобы вместо #Н/Д отображался понятный текст (например, «Нет в прайсе»), оберните ВПР в функцию ЕСЛИОШИБКА (IFERROR):
ВПР + ЕСЛИОШИБКА=ЕСЛИОШИБКА(ВПР(A2;Прайс!$A$2:$B$100;2;ЛОЖЬ);"Нет в прайсе")Копировать
XLOOKUP + IFERROR (англ.)=IFERROR(VLOOKUP(A2,Price!$A$2:$B$100,2,FALSE),"Not found")Копировать
10. FAQ: частые вопросы о ВПР
Может ли ВПР искать по двум критериям?
Стандартная ВПР — нет. Но можно создать вспомогательный столбец, объединив два критерия (например, =A2&B2), и искать по нему.
ВПР по двум критериям=ВПР(A2&B2;$D$2:$F$100;3;ЛОЖЬ)Копировать
Почему ВПР не работает с числами?
Возможно, числа сохранены как текст. Преобразуйте их: выделите столбец → Данные → Текст по столбцам → Готово.
Как ускорить работу ВПР на 100 000+ строк?
Используйте XLOOKUP или перейдите на Power Query. Также помогает ограничение диапазона (не целые столбцы A:A, а конкретный диапазон).
ВПР или XLOOKUP — что выбрать в 2026 году?
Если у вас Excel 365 или 2021+ — однозначно XLOOKUP. Для совместимости со старыми версиями — ВПР или ИНДЕКС+ПОИСКПОЗ.
Как сделать ВПР в Google Таблицах?
Синтаксис идентичен, но используется английское название:
VLOOKUP в Google Sheets=VLOOKUP(A2,Price!$A$2:$B$100,2,FALSE)Копировать
Итог: Функция ВПР — мощный инструмент для подстановки данных в Excel. Освоив её синтаксис и типичные ошибки, вы сможете в разы ускорить работу с таблицами. А если ваш Excel поддерживает XLOOKUP — смело переходите на него: это будущее поиска в таблицах.