Неделя супер-распродажиClaude Skills — скидка 20%
Tips

Как использовать VLOOKUP в Excel: полное руководство

Powerdrill Bloom·
Как использовать VLOOKUP в Excel: полное руководство

Чтобы выполнить VLOOKUP в Excel, введите =VLOOKUP( в ячейку и добавьте четыре аргумента. Это искомое значение, диапазон таблицы для поиска, номер возвращаемого столбца и FALSE для точного совпадения. Например, формула =VLOOKUP(B2,$F$2:$H$5,3,FALSE) находит значение из ячейки B2 и возвращает значение из третьего столбца таблицы.

В этом руководстве рассказывается, как работает VLOOKUP, как шаг за шагом построить формулу и как искать значения на другом листе. Мы также разберем точное и приблизительное совпадение, частые ошибки и случаи, когда лучше использовать XLOOKUP. Инструкции основаны на страницах поддержки Microsoft, актуальных на 13 ноября 2026 года.

Что делает VLOOKUP

VLOOKUP находит значение в первом столбце таблицы и возвращает значение из той же строки. Буква «V» означает «vertical» (вертикальный), так как поиск выполняется сверху вниз по столбцу.

На странице поддержки Microsoft приводятся два типичных примера использования. Вы можете «найти цену автозапчасти по ее артикулу» или «найти имя сотрудника по его идентификатору (ID)».

Типичные задачи включают:

  • Цены. Перенос цены товара в список заказов по его коду.
  • Имена. Добавление имени сотрудника в отчет по его ID.
  • Категории. Добавление региона или отдела в каждую строку выгрузки продаж.
  • Ставки. Поиск налоговой ставки или размера комиссии в справочной таблице.
  • Сопоставление списков. Проверка того, какие элементы из одного списка присутствуют в другом.

У VLOOKUP есть одно важное правило, определяющее всю работу функции. Microsoft называет его «секретом VLOOKUP». Вам нужно организовать данные так, чтобы искомое значение «находилось слева от возвращаемого значения (Amount), которое вы хотите найти».

Как работает формула VLOOKUP

Синтаксис состоит из четырех частей. Microsoft описывает его следующим образом:

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

Аргумент Что он означает Пример
lookup_value Значение, которое вы хотите найти B2, код товара
table_array Диапазон для поиска, в первом столбце которого находится искомое значение $F$2:$H$5
col_index_num Номер столбца в указанном диапазоне, из которого нужно вернуть значение (отсчет начинается с 1) 3, для столбца с ценой
range_lookup FALSE для точного совпадения, TRUE для приблизительного совпадения FALSE

Microsoft объясняет подсчет столбцов на примере. Если диапазон — B2:D11, то «столбец B считается первым, C — вторым и так далее».

Последний аргумент является необязательным, но опускать его рискованно. Microsoft отмечает: «Если ничего не указать, значением по умолчанию всегда будет TRUE или приблизительное совпадение». Для большинства поисков, таких как ID и коды, требуется значение FALSE.

Сначала подготовьте данные

Большинство проблем с VLOOKUP связаны с данными, а не с самой формулой. Несколько простых проверок помогут сэкономить время в будущем.

  • Поместите столбец поиска на первое место. Искомое значение должно находиться в самом левом столбце диапазона таблицы.
  • Используйте уникальные значения в строках. Каждый код или ID должен встречаться в справочной таблице только один раз.
  • Храните числа как числа. Microsoft предупреждает, что если числа сохранены как текст, «VLOOKUP может вернуть неверное или непредвиденное значение».
  • Удалите лишние пробелы. Пробелы в начале или конце строки мешают сопоставлению. Функция TRIM помогает их удалить.
  • Используйте заголовки. Понятные заголовки упрощают подсчет номера столбца.

Если столбец поиска находится справа от нужного вам значения, VLOOKUP не сможет его извлечь. Вы можете переместить столбец или использовать функцию XLOOKUP, о которой пойдет речь ниже.

Еще две полезные привычки упростят работу с формулами. Во-первых, отформатируйте справочную таблицу как «умную» таблицу Excel или присвойте ей имя. Microsoft отмечает, что для аргумента table_array «можно использовать именованный диапазон или таблицу». Именованный диапазон, например Products, читать гораздо легче, чем $F$2:$H$5. Во-вторых, храните справочные таблицы на отдельном листе. Тогда никто случайно не отсортирует и не удалит строки при редактировании основных данных.

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

Ниже описан весь процесс для базового поиска с точным совпадением. В этом примере список заказов находится в столбцах от A до C, а таблица товаров — в столбцах от F до H.

  1. Нажмите на ячейку для вывода результата. Например, D2 рядом с первым заказом.
  2. Начните вводить формулу. Введите =VLOOKUP(.
  3. Добавьте искомое значение. Нажмите на ячейку B2 (код товара), затем введите запятую.
  4. Добавьте диапазон таблицы. Выделите диапазон F2:H5, нажмите клавишу F4, чтобы зафиксировать его как $F$2:$H$5, затем введите запятую.
  5. Добавьте номер столбца. Введите 3 для столбца с ценой, затем запятую.
  6. Выберите тип совпадения. Введите FALSE, закройте скобку и нажмите Enter.

Готовая формула выглядит так: =VLOOKUP(B2,$F$2:$H$5,3,FALSE).

Затем протяните формулу вниз по столбцу. Дважды щелкните по маркеру автозаполнения в углу ячейки D2, и Excel скопирует ее до последней строки.

Шаг 4 очень важен для автозаполнения. Согласно рекомендациям Microsoft, следует использовать абсолютные ссылки, чтобы вы могли «протянуть формулу вниз, и она всегда ссылалась на один и тот же диапазон поиска». Без знаков доллара диапазон будет смещаться на одну строку вниз при каждом копировании, и последующие формулы не найдут нужные данные.

VLOOKUP между двумя листами

Справочные таблицы часто находятся на другом листе. VLOOKUP справляется с этим, используя имя листа в указании диапазона.

На странице Microsoft приводится следующий пример:

=VLOOKUP(A2,'Client Details'!A:F,3,FALSE)

Здесь 'Client Details' — это имя листа, заключенное в одинарные кавычки, так как оно содержит пробел. A:F — диапазон на этом листе. Формула ищет значение из ячейки A2 и возвращает данные из третьего столбца.

Проще всего создать такую формулу с помощью кликов мыши:

  1. Начните вводить =VLOOKUP( и нажмите на ячейку с искомым значением.
  2. Введите запятую, а затем перейдите на вкладку другого листа.
  3. Выделите диапазон таблицы на этом листе и нажмите F4, чтобы зафиксировать его.
  4. Укажите номер столбца, введите FALSE и нажмите Enter.

Excel сам подставит имя листа. Этот же метод работает и для другой рабочей книги, если оба файла открыты во время создания формулы.

Точное совпадение против приблизительного

Четвертый аргумент меняет логику поиска VLOOKUP. Неправильный выбор этого аргумента — частая причина неверных результатов.

Точное совпадение (FALSE или 0). Microsoft отмечает, что в этом режиме функция «ищет точное совпадение в первом столбце». Если значение не найдено, возвращается ошибка #N/A. Используйте этот режим для ID, кодов, имен и любых других данных, требующих точного совпадения.

Приблизительное совпадение (TRUE или 1). Microsoft указывает, что в этом режиме предполагается, что «первый столбец таблицы отсортирован по возрастанию или по алфавиту, и функция ищет ближайшее значение». Используйте его для диапазонов, например для налоговых ставок или шкал оценок.

Вот типичный пример использования приблизительного совпадения. В таблице комиссионных указаны пороги продаж: 0, 10 000 и 50 000 со ставками 2%, 3% и 5% соответственно. Продажа на сумму 32 000 попадает в диапазон между 10 000 и 50 000, поэтому VLOOKUP вернет значение 3%.

Чтобы это работало, пороговые значения должны быть отсортированы по возрастанию. Microsoft предупреждает: если первый столбец не отсортирован, «возвращаемое значение может оказаться неожиданным».

При точном совпадении также поддерживаются подстановочные знаки для текста. Microsoft отмечает, что при использовании FALSE и текстового искомого значения можно использовать вопросительный знак и звездочку. «Вопросительный знак заменяет любой один символ. Звездочка заменяет любую последовательность символов». В примере =VLOOKUP("Fontan?",B2:E7,2,FALSE) функция найдет Fontana с любой последней буквой. Чтобы найти настоящий вопросительный знак или звездочку, введите перед ними тильду (~) впереди.

Как исправить частые ошибки VLOOKUP

На странице Microsoft перечислены наиболее распространенные проблемы и причины их возникновения.

Ошибка Что пошло не так Как исправить
#N/A При значении FALSE точное совпадение не найдено Проверьте орфографию, лишние пробелы и числа, сохраненные как текст
#REF! Номер столбца превышает количество столбцов в диапазоне Расширьте диапазон или уменьшите номер столбца
#VALUE! Аргумент меньше 1 Убедитесь, что номер столбца равен 1 или больше
#NAME? Обычно отсутствуют кавычки вокруг текста в формуле Заключите текст в кавычки, например "Fontana"
Неверное значение Использование TRUE или пустого типа совпадения для неотсортированного столбца Используйте FALSE или отсортируйте первый столбец

Если ошибки #N/A ожидаемы (например, для новых товаров, которых еще нет в таблице), оберните формулу в функцию IFERROR:

=IFERROR(VLOOKUP(B2,$F$2:$H$5,3,FALSE),"Не найдено")

Это позволит вывести понятный текст вместо ошибки. Используйте этот прием с осторожностью: текстовое обозначение может скрыть реальную проблему в данных, например опечатку в коде.

Microsoft также упоминает ошибку #SPILL! в более новых версиях Excel. Она возникает, когда искомое значение ссылается на весь столбец (например, A:A). Вместо этого сошлитесь на одну ячейку, например A2.

VLOOKUP против XLOOKUP

Сейчас Microsoft рекомендует использовать более новую функцию. На странице поддержки VLOOKUP функция XLOOKUP называется «улучшенной версией VLOOKUP». Также добавляется, что XLOOKUP «работает в любом направлении и по умолчанию возвращает точные совпадения».

Синтаксис XLOOKUP выглядит следующим образом:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Тот же поиск цены с помощью XLOOKUP выглядит так: =XLOOKUP(B2,$F$2:$F$5,$H$2:$H$5). Здесь не нужно высчитывать номер столбца.

Возможность VLOOKUP XLOOKUP
Направление поиска Столбец поиска должен быть самым левым Возвращаемый столбец может быть с любой стороны
Совпадение по умолчанию Приблизительное (TRUE) Точное
Ссылка на столбец Номер, который нужно высчитывать Диапазон, который вы выделяете
Сообщение при отсутствии значения Требуется функция IFERROR Встроенный аргумент if_not_found
Доступность Все версии Отсутствует в Excel 2016 и Excel 2019

Последняя строка таблицы имеет значение. Microsoft отмечает, что «XLOOKUP недоступна в Excel 2016 и Excel 2019». Если вы делитесь файлами с пользователями этих версий, VLOOKUP по-прежнему остается более надежным выбором.

Для старых версий есть третий вариант. В руководстве по поиску Microsoft говорится, что «функция VLOOKUP может искать значения только слева направо». Для поиска влево предлагается использовать «вместо нее комбинацию функций INDEX и MATCH». MATCH находит номер строки искомого значения, а INDEX возвращает значение из этой строки в любом столбце. Формула получается длиннее, но она работает во всех версиях Excel. Для нашего примера с ценой она будет выглядеть так: =INDEX($H$2:$H$5,MATCH(B2,$F$2:$F$5,0)).

Пример использования VLOOKUP

Вот небольшой заказ на офисные принадлежности. Таблица товаров находится в диапазоне F1:H5, а заголовки — в строке 1.

ID товара Название Цена
P-101 Блокнот 4.50
P-102 Ручка 1.20
P-103 Степлер 8.75
P-104 Папка 2.30

Список заказов находится в диапазоне A1:C5. Каждый заказ содержит ID заказа, ID товара и количество.

Заказ ID товара Количество Цена (VLOOKUP) Итого по строке
1001 P-103 2 8.75 17.50
1002 P-101 10 4.50 45.00
1003 P-104 5 2.30 11.50
1004 P-105 1 #N/A —

Формула в ячейке D2 выглядит как =VLOOKUP(B2,$F$2:$H$5,3,FALSE) и протянута вниз до D5. Итоговая сумма по строке в ячейке E2 рассчитывается по формуле =C2*D2.

Заказ 1004 возвращает ошибку #N/A, так как товара P-105 нет в таблице товаров. Это полезная информация, а не ошибка в формуле. Кому-то нужно добавить этот товар или исправить код.

Сумма по трем корректным строкам составляет 17.50 + 45.00 + 11.50, что в итоге дает 74.00. Как только товар P-105 будет добавлен в таблицу, его цена появится автоматически.

Выполнение VLOOKUP в Excel с помощью ИИ

Когда вы сопоставляете большие файлы со множеством столбцов, написание и проверка формул поиска занимают много времени. Инструмент на базе ИИ может сопоставить таблицы за вас и отметить строки, в которых нет совпадений.

Вот как это сделать за три шага с помощью Powerdrill Bloom.

Шаг 1. Загрузите файлы Excel

Загрузите оба файла или одну рабочую книгу с обеими таблицами. На странице объединения файлов CSV указано, что вы можете настроить объединение «в соответствии с конкретными совпадениями столбцов, порядком сортировки или другими заданными вами критериями».

Убедитесь, что у обеих таблиц есть общий ключевой столбец, например ID товара или ID сотрудника.

Загрузка двух файлов Excel для сопоставления в стиле VLOOKUP в Powerdrill Bloom

Шаг 2. Запросите сопоставление на естественном языке

Опишите нужный вам поиск. Например: «Добавь цену из таблицы товаров к каждому заказу по ID товара и выведи список всех заказов без совпадений».

Для последующего анализа результатов Excel AI Assistant «автоматически определяет тенденции, корреляции и ключевые метрики в ваших данных Excel».

Шаг 3. Проверьте результаты и экспортируйте их

Выборочно проверьте несколько строк на соответствие исходной таблице, особенно строки без совпадений. Затем скачайте объединенный файл или запросите сводный отчет. Краткий список несопоставленных ID часто оказывается самым полезным результатом, так как он показывает, какие записи требуют исправления в источнике.

О другом способе объединения файлов без формул читайте в нашем руководстве как объединить два файла Excel без VLOOKUP.

Проверка сопоставленных строк перед экспортом объединенного файла

Распространенные ошибки, которых следует избегать

Эти проблемы возникают при работе с VLOOKUP чаще всего.

  • Пропуск типа совпадения. По умолчанию используется приблизительное совпадение. Вводите FALSE для ID и кодов.
  • Относительные диапазоны таблиц. Без знаков $ диапазон будет смещаться при протягивании формулы вниз.
  • Подсчет столбцов по листу. Считайте столбцы от первого столбца диапазона, а не от столбца A.
  • Столбец поиска находится справа. VLOOKUP ищет только вправо. Переместите столбец или используйте XLOOKUP.
  • Скрытые пробелы. Значение «P-101 » с пробелом на конце не совпадет с «P-101».
  • Числа, сохраненные как текст. Код, введенный как текст, не совпадет с тем же кодом, сохраненным как число.
  • Дублирующиеся ключи. VLOOKUP возвращает только первое найденное совпадение, поэтому дубликаты скрывают последующие строки.
  • Скрытие всех ошибок. Функция IFERROR полезна, но перед отправкой файла проверьте, какие именно ошибки она скрывает.

Если вы готовы сопоставлять и анализировать большие файлы без использования формул, попробуйте Powerdrill Bloom, загрузив выгрузку заказов и список товаров.

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

Как выполнить VLOOKUP в Excel?

Введите =VLOOKUP(искомое_значение, диапазон_таблицы, номер_столбца, FALSE). Например, формула =VLOOKUP(B2,$F$2:$H$5,3,FALSE) находит значение B2 в первом столбце диапазона F2:H5 и возвращает значение из третьего столбца.

Почему VLOOKUP возвращает ошибку #N/A?

При точном совпадении ошибка #N/A означает, что значение не было найдено. Проверьте данные на наличие опечаток, лишних пробелов и чисел, сохраненных в текстовом формате.

Как выполнить VLOOKUP с другого листа?

Укажите имя листа в диапазоне, например =VLOOKUP(A2,'Client Details'!A:F,3,FALSE). Если кликнуть по вкладке другого листа во время создания формулы, Excel добавит его имя автоматически.

Что означает FALSE в VLOOKUP?

Значение FALSE указывает на необходимость точного совпадения. Значение TRUE (или если оставить аргумент пустым) задает приблизительное совпадение, для которого требуется отсортированный первый столбец.

Что использовать: VLOOKUP или XLOOKUP?

Используйте XLOOKUP, если у всех пользователей установлен Excel для Microsoft 365, Excel 2021 или более поздняя версия. Эта функция работает в любом направлении и по умолчанию ищет точное совпадение. Используйте VLOOKUP, если файлами будут пользоваться люди со старыми версиями Excel 2016 или Excel 2019.

Источники: Microsoft Support, функция VLOOKUP · Microsoft Support, функция XLOOKUP · Microsoft Support, поиск значений с помощью VLOOKUP, INDEX или MATCH.