VLOOKUP в Excel — как использовать

Ноутбук с таблицей, круговой диаграммой и столбчатой диаграммой

Быстрые ссылки

Что такое VLOOKUP в Excel?
Как выполнить VLOOKUP в Excel
Как связать листы Excel с помощью VLOOKUP

Краткое содержание

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

Важно: VLOOKUP ищет по первому (самому левому) столбцу указанного диапазона и возвращает значение только из столбцов справа от него. Если структура таблицы не позволяет — используйте альтернативы (INDEX+MATCH или XLOOKUP в современных версиях Excel).

Что такое VLOOKUP в Excel?

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

Определение в одну строку: VLOOKUP ищет lookup_value в первом столбце table_array и возвращает значение из указанного номера столбца col_index_num справа от этого столбца.

Синтаксис:

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

Пример формулы из исходного примера:

=VLOOKUP(I3, A2:E26, 2)

Объяснение аргументов:

lookup_value — значение для поиска (в примере: I3, значение 4).
table_array — диапазон, содержащий таблицу (A2:E26).
col_index_num — номер столбца в table_array, из которого возвращается значение (в примере 2).
range_lookup — необязательный логический аргумент: FALSE (точное совпадение) или TRUE (приблизительное; по умолчанию TRUE).

Примечание о локали: в русской локали Excel в формулах часто используется точка с запятой как разделитель аргументов: =VLOOKUP(I3;A2:E26;2;FALSE). Оставьте форму, которую ожидает ваша версия Excel.

Ограничения VLOOKUP — тезисно:

Ищет только в первом столбце table_array.
Возвращает данные только справа от столбца поиска.
При вставке или перестановке столбцов col_index_num может перестать соответствовать.

Если структура данных не позволяет, применяйте альтернативы (см. раздел «Альтернативы»).

Как выполнить VLOOKUP в Excel — пошагово

Ниже подробный пошаговый сценарий с примерами и пояснениями. Я приведу оба синтаксиса: с запятой (англ. локаль) и с точкой с запятой (русская локаль).

Подготовьте таблицу: убедитесь, что столбец, по которому будете искать, находится слева от возвращаемого столбца.
Выберите ячейку для результата (в примере — F6).
В строке формул начните ввод: =VLOOKUP( или =ВПР( — в русской локали функция называется ВПР.
Укажите lookup_value: кликните ячейку E6 (ВПР/ VLOOKUP возьмёт её значение).
Введите запятую или точку с запятой и выделите table_array (A2:C9).
Введите номер столбца с нужным значением (в примере «2» для столбца «Отзывы»).
Для точного совпадения укажите FALSE (или ЛОЖЬ в русской локали). Для приблизительного совпадения — TRUE (ИСТИНА) или можно опустить.
Закройте скобку и нажмите Enter.

Пример (англ. локаль):

=VLOOKUP(E6,A2:C9,2,FALSE)

Пример (русская локаль Excel):

=ВПР(E6;A2:C9;2;ЛОЖЬ)

Визуальные подсказки: при клике на функцию Excel подскажет синтаксис и подсветит диапазоны — используйте это, чтобы убедиться, что выбран нужный диапазон.

Заполнение нескольких ячеек (несколько элементов)

Если нужно выполнить поиск для списка значений (несколько строк), делайте следующее:

Напишите формулу для первой строки, используя абсолютный адрес для table_array.
Сделайте table_array абсолютным с помощью клавиши F4 (Excel добавит $: $A$2:$C$9).
Протяните (или двойной щелчок по маркеру автозаполнения) результат вниз.

Финальная формула:

=VLOOKUP(E6,$A$2:$C$9,2,FALSE)

Или в русской локали:

=ВПР(E6;$A$2:$C$9;2;ЛОЖЬ)

Важно: если вы используете приближённый поиск (TRUE/ИСТИНА), исходный столбец с данными должен быть отсортирован по возрастанию — иначе результаты будут неверны.

Как связать разные листы с помощью VLOOKUP

VLOOKUP позволяет брать таблицу с другого листа (или даже из другой книги). Это полезно, когда вы хотите оставить исходную таблицу нетронутой и работать с её данными в объединённом или агрегированном листе.

Пошагово:

На листе-целевике выберите ячейку для результата (например B3).
Начните ввод формулы: =VLOOKUP(
Укажите lookup_value: нажмите на ячейку с именем продукта (например A3).
Введите запятую и переключитесь на источник (листаните на лист с таблицей).
Выделите table_array на листе-источнике и нажмите F4 для абсолютной ссылки.
Введите номер столбца и FALSE/ЛОЖЬ для точного совпадения, закройте скобку и нажмите Enter.

Итоговая формула:

=VLOOKUP(A3, Sheet1!$A$2:$C$9, 3, FALSE)

Русская локаль (если лист называется Лист1):

=ВПР(A3;Лист1!$A$2:$C$9;3;ЛОЖЬ)

Подсказка: если вы тянете формулу вниз для нескольких позиций, убедитесь, что lookup_value меняется относительным образом (A3 → A4), а table_array остаётся зафиксированным ($A$2:$C$9).

Частые ошибки и как их исправлять

#N/A — означает, что точного совпадения не найдено (при использовании FALSE) или приближённый поиск не дал результата. Проверьте опечатки, пробелы и формат данных (число vs текст).
Неверный столбец — если вы вставляли/удаляли столбцы, col_index_num может уже не указывать на нужный. Проверьте и скорректируйте номер столбца.
Проблемы с пробелами — незаметные пробелы в начале/конце строки приводят к отсутствию совпадения. Используйте TRIM (СЖПРОБЕЛЫ) для очистки значений.
Различие форматов — числа в виде текста не будут сопоставлены с реальными числами. Приведите формат: VALUE() или изменение формата столбца.
Неподходящая локаль — символы разделителя аргументов (, vs ;). Используйте тот, что принят в вашей установке Excel.

Альтернативные подходы и когда использовать их

INDEX + MATCH (ВЫБОР+ПОИСКПОЗ): гибкий вариант — MATCH находит позицию в любом столбце, INDEX возвращает значение по относительной позиции; позволяет искать влево и не зависеть от номера столбца.
XLOOKUP (новые версии Excel: Microsoft 365, Excel 2019+): мощнее VLOOKUP — может искать в любом направлении, поддерживает значения по умолчанию при отсутствии совпадения, работает с диапазонами разной длины.
LOOKUP / HLOOKUP: HLOOKUP — горизонтальный аналог VLOOKUP; LOOKUP — старая устаревшая функция, используемая редко.

Рекомендация: если ваша версия Excel поддерживает XLOOKUP, используйте её для новых решений. Для совместимости с устаревшими версиями оставайтесь на VLOOKUP или INDEX+MATCH.

Практические примеры и шаблоны формул (шпаргалка)

Базовая формула (точное совпадение):

=VLOOKUP(lookup_value, table_array, col_index_num, FALSE)

Приблизительное совпадение (требует сортировки):

=VLOOKUP(lookup_value, table_array, col_index_num, TRUE)

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

=VLOOKUP(A3, Sheet1!$A$2:$C$100, 3, FALSE)

Пример с индексацией при необходимости поиска влево (INDEX+MATCH):

=INDEX(B2:B100, MATCH(E6, A2:A100, 0))

XLOOKUP (если доступна):

=XLOOKUP(E6, A2:A100, B2:B100, "Не найдено", 0)

Замечание: в русской локали функции называются иначе: INDEX = ИНДЕКС, MATCH = ПОИСКПОЗ, XLOOKUP = XПООИСК (в зависимости от версии локализации). Уточните названия в вашей версии Excel.

Ментальные модели и эвристики

Модель «телефонной книги»: ищите слева справа — VLOOKUP смотрит в левой «строке» и отдаёт значение справа.
Эвристика надёжности: всегда ставьте FALSE/ЛОЖЬ, если ожидаете точных соответствий (артикулы, номера, имена).
Подготовка данных: нормализуйте ключи (уберите лишние пробелы, приведите регистр, единый формат дат), это уменьшает ошибки.
Безопасность формул: фиксируйте table_array абсолютными ссылками ($A$2:$C$9) перед автозаполнением.

Роль-ориентированные контрольные списки

Для аналитика:

Убедиться в уникальности ключа в первом столбце.
Привести типы данных к единому формату.
Использовать абсолютные ссылки при автозаполнении.
Сохранить копию исходной таблицы перед перестановкой столбцов.

Для бухгалтера:

Проверить соответствие валют/форматов перед сравнением.
Использовать VLOOKUP для проверки прайс-листов и связей между документами.
При массовых импортах проверять на дубли и пропуски (#N/A).

Для студента/учащегося:

Практиковать с небольшими таблицами сначала вручную, потом автоматизировать.
Использовать TRIM и VALUE при подготовке данных.

Краткая методология внедрения VLOOKUP в проекте (mini-SOP)

Соберите все источники данных и определите ключ для поиска.
Приведите ключи к единому виду (формат, регистр, пробелы).
На листе-результате создайте расчётную ячейку и протестируйте формулу на нескольких строках.
Зафиксируйте диапазон (F4) и протяните формулу.
Проверьте результаты выборочно на корректность.
Документируйте использованные формулы и диапазоны.

Отладка: план реагирования на ошибки (runbook)

Если появляется #N/A — проверьте логические значения аргумента range_lookup (FALSE vs TRUE).
Сравните формат искомого значения и данных в таблице (текст vs число).
Проверить отсутствующие пробелы: использовать TRIM/СЖПРОБЕЛЫ.
Проверить, не скрыта ли строка с нужным значением.
Проверить наличие дубликатов ключей — они могут вызвать непредсказуемое поведение.
Если столбцы менялись — проверьте col_index_num.

Примеры отказов и крайние случаи

Когда VLOOKUP не подходит:

Когда нужный столбец находится левее столбца поиска.
Когда требуется двусторонний поиск (влево и вправо) и таблица часто меняет структуру.
При необходимости возвращать несколько значений для одного ключа (в этом случае используйте сводные таблицы, фильтры или Power Query).

Edge-case: если в table_array есть дубликаты lookup_value, VLOOKUP вернёт первое найденное совпадение сверху. Это может ввести в заблуждение при агрегировании данных.

Совместимость и миграционные заметки

Старые версии Excel (до 2019) не имеют XLOOKUP — используйте VLOOKUP или INDEX+MATCH для обратной совместимости.
В российской локали разделитель аргументов — точка с запятой (;). Также названия функций локализованы: VLOOKUP → ВПР, MATCH → ПОИСКПОЗ, INDEX → ИНДЕКС.
При переносе книг между различными локалями Excel иногда меняет разделители аргументов: проверьте формулы после открытия файла на другом компьютере.

Безопасность и приватность

VLOOKUP сам по себе не раскрывает данных, но при связывании листов убедитесь, что вы не подтягиваете конфиденциальные значения в общедоступные документы.
При использовании внешних ссылок (книги) удостоверьтесь в надёжности источника данных.

Быстрый чек-лист производительности

Фиксируйте диапазоны, чтобы Excel не переобрабатывал динамические области каждый раз.
Для очень больших таблиц рассмотрите использование Power Query или баз данных вместо массивных VLOOKUP в листе.

Краткая сводка по функциям (1‑строчный глоссарий)

VLOOKUP / ВПР — вертикальный поиск по первому столбцу.
HLOOKUP / ГПР — горизонтальный поиск по первой строке.
INDEX / ИНДЕКС — возвращает значение из диапазона по номеру строки и столбца.
MATCH / ПОИСКПОЗ — находит позицию значения в диапазоне.
XLOOKUP — современная универсальная функция поиска (если доступна).

Шпаргалка — частые формулы

Точное совпадение (англ. локаль): =VLOOKUP(A2,$D$2:$F$100,3,FALSE)
Точное совпадение (русская локаль): =ВПР(A2;$D$2:$F$100;3;ЛОЖЬ)
INDEX+MATCH: =INDEX($C$2:$C$100, MATCH(A2,$A$2:$A$100,0))
XLOOKUP (если есть): =XLOOKUP(A2,$A$2:$A$100,$C$2:$C$100,”Не найдено”,0)

Заключение

VLOOKUP — базовая и часто необходимая функция для быстрого поиска данных в таблицах Excel. Она проста в использовании, но имеет ограничения: поиск всегда слева направо и зависимость от номера столбца. Для более гибких сценариев рассмотрите INDEX+MATCH или XLOOKUP. Всегда проверяйте типы данных, избавляйтесь от лишних пробелов и фиксируйте диапазоны при автозаполнении.

Важно: начинайте с малого (несколько строк), проверяйте формулу вручную и только затем масштабируйте на весь массив данных.

Краткое резюме:

VLOOKUP — удобен для быстрого поиска и объединения таблиц.
Для точности используйте аргумент FALSE/ЛОЖЬ.
При необходимости поиска влево используйте INDEX+MATCH или XLOOKUP.

Примечание: если вы хотите, я могу подготовить отдельный файл с примерами, поддерживаемыми версиями формул и тестовыми таблицами для практики.

VLOOKUP в Excel: полное руководство