Функция ВПР (VLOOKUP) в Excel и Google Sheets. Полное руководство

Кратко

Что делает:
Ищет значение в первом столбце таблицы и возвращает значение из указанного столбца в той же строке .

Где работает:
✅ Excel (все версии)
✅ Google Sheets (поддерживается)
✅ МойОфис Таблицы

Главное ограничение:
Ищет только в первом столбце диапазона и возвращает значение только из столбца справа

Сложность:
★★☆☆☆ — Средняя

Для чего нужна функция

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

Основные сценарии использования:

• Подстановка цен и названий — по коду товара найти его описание или цену.
• Объединение таблиц — добавить информацию из справочника в основной отчёт.
• Поиск информации о сотрудниках — по ID найти имя, отдел или должность.
• Работа с тарифами и ставками — поиск процента по сумме или категории.

Важное предупреждение: Если вы используете Excel для Microsoft 365 или Excel 2024, рассмотрите функцию ПРОСМОТРX (XLOOKUP). Это улучшенная версия ВПР, которая работает в любом направлении и по умолчанию возвращает точные совпадения.

Совместимость

Платформа	Поддержка VLOOKUP	Примечание
Excel (все версии)	✅ Да	Доступна во всех версиях
Google Sheets	✅ Да	Полная поддержка
МойОфис Таблицы	✅ Да

Синтаксис

Структура функции одинакова в Excel и Google Sheets. Разделитель аргументов зависит от региональных настроек: ; или , .

=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])

=VLOOKUP(lookup_value; table_array; col_index_num; [range_lookup])

Разбор аргументов

Аргумент	Обязательный	Описание
искомое_значение (lookup_value)	✅ Да	Значение для поиска. Должно находиться в первом столбце таблицы .
таблица (table_array)	✅ Да	Диапазон ячеек для поиска. Первый столбец должен содержать искомое_значение .
номер_столбца (col_index_num)	✅ Да	Номер столбца (начиная с 1 для первого столбца таблицы), из которого возвращается значение .
интервальный_просмотр (range_lookup)	❌ Нет	Логическое значение: • ИСТИНА (TRUE) или опущен — приблизительное совпадение (первый столбец должен быть отсортирован) . • ЛОЖЬ (FALSE) — точное совпадение.

Простые примеры

Пример 1. Поиск цены по коду товара (точное совпадение)

Исходные данные: Таблица товаров в диапазоне A2:C7 — колонка A содержит коды, колонка B — названия, колонка C — цены.

Задача: Найти цену товара с кодом из ячейки A2.

Формула: =ВПР(A2; A2:C7; 3; ЛОЖЬ)

Результат: Цена товара из третьего столбца диапазона .

Объяснение: ЛОЖЬ в четвёртом аргументе означает, что требуется точное совпадение . Это самый частый сценарий использования ВПР.

Примечание: ЛОЖЬ и 0 в четвёртом аргументе означают точный поиск. В этой статье используется ЛОЖЬ, потому что такой вариант понятнее при чтении формулы.

Пример 2. Поиск по текстовому значению

Формула: =ВПР("Иванов"; B2:E7; 2; ЛОЖЬ)

Результат: Значение из второго столбца диапазона B2:E7 в строке, где первый столбец содержит «Иванов».

Объяснение: Текст в кавычках можно использовать как искомое_значение.

Пример 3. Приблизительное совпадение (для диапазонов)

Задача: Найти размер комиссии по сумме продаж из ячейки A2 по таблице диапазонов .

Формула: =ВПР(A2; E2:F6; 2; ИСТИНА)

Результат: Соответствующая комиссия для суммы продаж.

Объяснение: ИСТИНА находит ближайшее меньшее значение. Важно: первый столбец таблицы должен быть отсортирован по возрастанию .

Практические сценарии использования

Продажи: подстановка названия товара в заказ

Задача: В таблице заказов есть только код товара. Нужно подставить его название из справочника.

Формула: =ВПР(B2; 'Справочник товаров'!A:B; 2; ЛОЖЬ)

Результат: Название товара по его коду.

HR: поиск сотрудника по ID

Задача: По идентификатору сотрудника найти его отдел.

Формула: =ВПР(A2; 'Список сотрудников'!A:D; 3; ЛОЖЬ)

Результат: Название отдела из третьего столбца таблицы сотрудников.

Финансы: расчёт скидки по сумме

Задача: По сумме заказа найти размер скидки из таблицы диапазонов.

Формула: =ВПР(D2; F2:G6; 2; ИСТИНА)

Результат: Размер скидки, соответствующий сумме .

Важно: Для приблизительного поиска первый столбец должен быть отсортирован по возрастанию .

Бухгалтерия: подстановка ставки НДС

Задача: По коду товара найти ставку НДС из справочника.

Формула: =ВПР(A2; 'Ставки НДС'!A:B; 2; ЛОЖЬ)

Результат: Ставка НДС для товара.

Частые ошибки пользователей

Забывают указать ЛОЖЬ

Ситуация: =ВПР(A2; A:B; 2) — возвращает неправильное значение или ошибку.

Причина: Если четвёртый аргумент опущен, ВПР по умолчанию ищет приблизительное совпадение .

Решение: Всегда указывайте ЛОЖЬ для точного совпадения: =ВПР(A2; A:B; 2; ЛОЖЬ).

Искомое значение не в первом столбце

Ситуация: Ищете по столбцу B, но диапазон начинается с A.

Причина: ВПР может искать только в первом столбце указанного диапазона .

Решение: Убедитесь, что столбец с искомым_значением — первый в таблице. Или используйте ИНДЕКС + ПОИСКПОЗ .

Неправильный номер столбца

Ситуация: =ВПР(A2; A:C; 4; ЛОЖЬ) — ошибка #ССЫЛКА!.

Причина: Номер столбца превышает количество столбцов в диапазоне .

Решение: Проверьте нумерацию столбцов: 1 — первый столбец диапазона, 2 — второй и т.д.

Лишние пробелы или невидимые символы

Ситуация: Внешне одинаковые значения, но ВПР возвращает #Н/Д.

Причина: Пробелы в начале или конце строки .

Решение: Используйте СЖПРОБЕЛЫ для очистки данных: =ВПР(СЖПРОБЕЛЫ(A2); A:B; 2; ЛОЖЬ).

#Н/Д ошибка

Ситуация: ВПР возвращает #Н/Д, хотя значение должно существовать.

Причина:

• Искомого значения нет в первом столбце .
• Разные форматы данных (число vs текст) .
• Пробелы или невидимые символы.

Решение:

1. Проверьте, что значение действительно существует.
2. Преобразуйте числа в текстовый формат (или наоборот).
3. Очистите данные от пробелов .

В таблице несколько одинаковых значений

Ситуация: В первом столбце таблицы несколько строк с одинаковым ключом.

Пример:

Код	Товар
100	Ноутбук
100	Монитор
100	Клавиатура

Формула: =ВПР(100; A2:B4; 2; ЛОЖЬ) вернёт Ноутбук (первое совпадение сверху).

Причина: При нескольких совпадениях ВПР возвращает первое найденное совпадение .

Решение: Проверьте уникальность ключевого столбца или используйте другие инструменты (например, ФИЛЬТР), если нужно получить несколько совпадений.

Сравнение VLOOKUP с альтернативами

Критерий	ВПР (VLOOKUP)	ПРОСМОТРX (XLOOKUP)	ИНДЕКС+ПОИСКПОЗ
Направление поиска	Только слева направо	Любое	Любое
Точное совпадение по умолчанию	❌ Нет	✅ Да	⚠️ Нужно указать 0 в ПОИСКПОЗ
Столбец поиска	Должен быть первым	Можно указать любой	Можно указать любой
Устойчивость к изменению структуры	⚠️ Может вернуть данные не из того столбца	Высокая	Высокая
Обработка отсутствия совпадения	Через ЕСЛИОШИБКА	Есть аргумент «еслиненайдено»	Через ЕСЛИОШИБКА
Доступность	Все версии	Excel 365, 2024, Google Sheets	Все версии

Полезные комбинации с другими функциями

ВПР + ЕСЛИОШИБКА — обработка ошибок

=ЕСЛИОШИБКА(ВПР(A2; A:B; 2; ЛОЖЬ); "Не найдено")

ВПР + СЖПРОБЕЛЫ — очистка от пробелов

=ВПР(СЖПРОБЕЛЫ(A2); A:B; 2; ЛОЖЬ)

ВПР + ЕСЛИ — защита от пустого значения

=ЕСЛИ(A2=""; ""; ВПР(A2; A:B; 2; ЛОЖЬ))

ВПР + СЖПРОБЕЛЫ + ЕСЛИОШИБКА — комплексная защита

=ЕСЛИОШИБКА(ВПР(СЖПРОБЕЛЫ(A2); A:B; 2; ЛОЖЬ); "Не найдено")

Часто задаваемые вопросы

Вопрос: В каких версиях Excel работает ВПР (VLOOKUP)?
Ответ: Во всех версиях Excel и Google Sheets .

Вопрос: Можно ли искать значение слева от столбца поиска?
Ответ: Нет. ВПР ищет только в первом столбце диапазона и возвращает значения только из столбцов справа . Для поиска в любом направлении используйте XLOOKUP или INDEX+MATCH .

Вопрос: Что будет, если опустить четвертый аргумент?
Ответ: ВПР будет искать приблизительное совпадение, что может привести к неожиданным результатам . Всегда указывайте ЛОЖЬ для точных совпадений.

Вопрос: Почему ВПР возвращает #Н/Д?
Ответ: Искомое значение не найдено в первом столбце , либо есть проблемы с форматами данных или пробелами .

Вопрос: Что лучше использовать — ВПР или XLOOKUP?
Ответ: Если у вас Excel для Microsoft 365 или Excel 2024, используйте XLOOKUP — она гибче и безопаснее . ВПР остаётся универсальным выбором для всех версий Excel.

Заключение

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

Ключевые правила:

1. искомое_значение должно быть в первом столбце таблицы .
2. Для точного совпадения всегда указывайте ЛОЖЬ .
3. При приблизительном поиске (ИСТИНА) первый столбец должен быть отсортирован .
4. Нумерация столбцов начинается с 1 для первого столбца диапазона .
5. ВПР возвращает только первое найденное совпадение сверху .
6. При частом использовании ВПР в новых версиях Excel рассмотрите переход на XLOOKUP — она работает в любом направлении и более устойчива к изменениям в таблице .

Освойте ВПР — и вы сможете эффективно связывать данные из разных таблиц, экономя часы ручной работы. Это один из ключевых навыков для любого пользователя Excel и Google Sheets.