Перейти к содержимому

Функция VLOOKUP в Excel

Author: Siluvia Last Modified: 2025-06-05

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

showing the usage of VLOOKUP function


Связанные видео


Пошаговое объяснение аргументов

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

Шаг1: Начните ввод функции VLOOKUP

Выберите ячейку (в данном случае H6), в которую будет выведен результат, затем начните ввод функции VLOOKUP в Строке формул.

=VLOOKUP(
Шаг2: Укажите значение для поиска

Сначала укажите значение для поиска (то, что вы ищете) в функции VLOOKUP. Здесь я ссылаюсь на ячейку G6, в которой содержится определённый идентификатор —1005.

=VLOOKUP(G6

showing the usage of VLOOKUP function

Примечание: Значение для поиска должно находиться в первом столбце диапазона данных.
Шаг3: Укажите диапазон таблицы

Далее укажите диапазон ячеек, содержащий как искомое значение, так и значение, которое вы хотите получить. В этом примере я выбираю диапазон B6:E12. Формула теперь выглядит так:

=VLOOKUP(G6,B6:E12

showing the usage of VLOOKUP function

Примечание: Если вы хотите скопировать функцию VLOOKUP для поиска нескольких значений в одном столбце и получить разные результаты, используйте абсолютные ссылки, добавив знак доллара, например так:
=VLOOKUP(G6,$B$6:$E$12
Шаг4: Укажите столбец, из которого нужно вернуть значение

Теперь укажите номер столбца, из которого требуется вернуть значение.

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

=VLOOKUP(G6,B6:E12,4

showing the usage of VLOOKUP function

Шаг5: Поискать приближённое или точное совпадение

В завершение определите, ищете ли вы приближённое совпадение или точное совпадение.

  • Чтобы найти точное совпадение, используйте FALSE в качестве последнего аргумента.
  • Для поиска приближённого совпадения используйте TRUE в качестве последнего аргумента или просто оставьте это поле пустым.

В этом примере я использую FALSE для точного совпадения. Формула теперь выглядит так:

=VLOOKUP(G6,B6:E12,4,FALSE

showing the usage of VLOOKUP function

Нажмите клавишу Enter, чтобы получить результат

showing the usage of VLOOKUP function

Пошагово разобрав каждый аргумент на приведённом выше примере, синтаксис и аргументы функции VLOOKUP становятся гораздо понятнее.


Синтаксис и аргументы

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

  • Lookup_value (обязательный): Значение (реальное значение или ссылка на ячейку), которое вы ищете. Помните, что это значение должно находиться в первом столбце table_array.
  • Table_array (обязательный): Диапазон ячеек, содержащий как столбец значения для поиска, так и столбец возвращаемого значения.
  • Col_index (обязательный): Целое число, обозначающее номер столбца, в котором находится возвращаемое значение. Отсчёт начинается с1 для самого левого столбца table_array.
  • Range_lookup (необязательный): Логическое значение, определяющее, хотите ли вы, чтобы VLOOKUP искал приближённое или точное совпадение.
    • Приближённое совпадение - Установите этот аргумент в TRUE, 1 или оставьте пустым.
      Важно: Для поиска приближённого совпадения значения в первом столбце table_array должны быть отсортированы по возрастанию, иначе VLOOKUP может вернуть неверный результат.
    • Точное совпадение — установите этот аргумент в FALSE или0.

Примеры

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

Пример1: Точное совпадение и приближённое совпадение в VLOOKUP

Если вы путаетесь между точным и приближённым совпадением при использовании VLOOKUP, этот раздел поможет вам разобраться.

Точное совпадение в VLOOKUP

В этом примере я ищу соответствующие имена на основе баллов, указанных в диапазоне E6:E8, поэтому ввожу следующую формулу в ячейку F6 и протягиваю маркер автозаполнения до F8. В этой формуле последний аргумент установлен как FALSE для выполнения поиска по точному совпадению.

=VLOOKUP(E6,$B$6:$C$12,2,FALSE)

Однако, поскольку балла98 нет в первом столбце диапазона данных, VLOOKUP возвращает ошибку #N/A.

showing the usage of VLOOKUP function

Примечание: Здесь я заблокировал диапазон таблицы ($B$6:$C$12) в функции VLOOKUP, чтобы быстро ссылаться на один и тот же  набор данных при поиске по нескольким значениям.
Приближённое совпадение в VLOOKUP

Используя тот же пример, если изменить последний аргумент на TRUE, VLOOKUP выполнит поиск по приближённому совпадению. Если совпадение не найдено, будет найдено ближайшее наименьшее значение и возвращён соответствующий результат.

=VLOOKUP(E6,$B$6:$C$12,2,TRUE)

Поскольку балла98 нет, VLOOKUP находит следующее наибольшее значение, меньшее98 — это95, и возвращает имя, соответствующее баллу95, как ближайший результат.

showing the usage of VLOOKUP function

Примечания:
  • В случае приближённого совпадения значения в первом столбце table_array должны быть отсортированы по возрастанию. В противном случае VLOOKUP может вернуть неверное значение.
  • Здесь я заблокировал диапазон таблицы ($B$6:$C$12) в функции VLOOKUP, чтобы быстро ссылаться на один и тот же набор данных при поиске по нескольким значениям.

Пример2: Использование VLOOKUP с несколькими критериями

В этом разделе показано, как использовать VLOOKUP с несколькими условиями в Excel. Как видно на скриншоте ниже, если вы хотите определить зарплату по заданным имени (в ячейке H5) и отделу (в ячейке H6), выполните следующие шаги.

showing the usage of VLOOKUP function

Шаг1: Добавьте вспомогательный столбец для объединения значений из столбцов поиска

В данном случае необходимо создать вспомогательный столбец для объединения значений из столбцов Имя и Отдел.

  1. Добавьте вспомогательный столбец слева от вашего диапазона данных и задайте ему заголовок. Смотрите скриншот:
  2. В этом вспомогательном столбце выберите первую ячейку под заголовком, введите следующую формулу в Строку формул, и нажмите Enter.
    =C6&" "&D6
    Примечание: В этой формуле используется амперсанд (&) для объединения текста из двух столбцов в одну строку.
    • C6 — это первое имя из столбца Имя для объединения, D6 — первый отдел из столбца Отдел для объединения.
    • Значения этих двух ячеек объединяются с пробелом между ними.
  3. Выделите ячейку с результатом, затем перетащите маркер автозаполнения вниз, чтобы применить формулу к другим ячейкам этого столбца.
Шаг2: Примените функцию VLOOKUP с заданными критериями

Выберите ячейку, в которую хотите вывести результат (здесь я выбираю I7), введите следующую формулу в Строку формул и нажмите Enter.

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)
Результат

showing the usage of VLOOKUP function

Примечания:
  • Вспомогательный столбец должен быть первым столбцом диапазона данных.
  • Теперь столбец зарплаты — это пятый столбец диапазона данных, поэтому в формуле используем число 5 в качестве индекса столбца.
  • Также необходимо объединить критерии в I5 и I6 (I5& " "&I6) так же, как и во вспомогательном столбце, и использовать объединённое значение в качестве аргумента lookup_value в формуле.
  • Вы также можете напрямую указать оба условия в аргументе lookup_value и разделить их пробелом (если условия — текстовые, не забудьте заключить их в кавычки).
    =VLOOKUP("Albee IT",B6:F12,5,FALSE)
  • Лучшее решение — поиск по нескольким критериям за секунды
    Функция Многокритериальный поиск в Kutools для Excel позволяет легко выполнять поиск по нескольким условиям всего за несколько секунд. Получите30-дневную полнофункциональную бесплатную пробную версию прямо сейчас!

Распространённые ошибки VLOOKUP и их решения

В этом разделе перечислены распространённые ошибки, с которыми вы можете столкнуться при использовании VLOOKUP, а также приведены решения для их устранения.

  Обзор распространённых ошибок VLOOKUP:
          
        Причина1: Значение для поиска не находится в первом столбце  
    Причина2: Значение для поиска не найдено  
  ------ Причина3: Значение для поиска меньше минимального значения  
    Причина4: Числа имеют формат текста  
      Причина5: Table_array не является постоянным  
         
  ------ Причина1: Значение для поиска превышает255 символов  
  Причина2: Col_index меньше1  
         
  ------ Причина1: Col_index больше количества столбцов  
   
         
  ------ Причина1: Столбец для поиска не отсортирован по возрастанию  
  Причина2: Столбец был вставлен или удалён  
         

Возвращается ошибка #N/A

Самая распространённая ошибка при работе с VLOOKUP — это ошибка #N/A, означающая, что Excel не смог найти искомое значение. Вот некоторые причины, по которым VLOOKUP может возвращать ошибку #N/A.

Причина1: Значение для поиска не находится в первом столбце table_array

Одно из ограничений VLOOKUP в Excel — поиск осуществляется только слева направо. Поэтому значения для поиска должны находиться в первом столбце table_array.

Как показано на скриншоте ниже, я хочу вернуть имя по заданной должности. Здесь значение для поиска (sales manager) находится во втором столбце table_array, а возвращаемое значение — левее столбца поиска, поэтому VLOOKUP возвращает ошибку #N/A.

showing the usage of VLOOKUP function

Решения

Вы можете воспользоваться любым из следующих решений для устранения этой ошибки.

  • Переставьте столбцы
    Вы можете переставить столбцы так, чтобы столбец для поиска оказался первым в table_array.
  • Используйте функции INDEX и MATCH вместе
    В этом случае мы используем функции INDEX и MATCH вместе как альтернативу VLOOKUP для решения данной проблемы.
    =INDEX(B6:B12,MATCH(F6,C6:C12,0))
    showing the usage of VLOOKUP function
  • Используйте функцию XLOOKUP (доступна в Excel365, Excel2021 и более поздних версиях)
    =XLOOKUP(F6,C6:C12,B6:B12)

Причина2: Значение для поиска не найдено в столбце поиска (точное совпадение)

Одна из самых частых причин возврата ошибки #N/A — отсутствие искомого значения.

Как показано в примере ниже, мы ищем имя по заданному баллу98 в E6. Однако такого балла нет в первом столбце диапазона данных, поэтому VLOOKUP возвращает ошибку #N/A.

showing the usage of VLOOKUP function

Решения

Чтобы исправить эту ошибку, попробуйте одно из следующих решений.

  • Если вы хотите, чтобы VLOOKUP искал следующее наибольшее значение, меньшее значения для поиска, измените последний аргумент с FALSE (точное совпадение) на TRUE (приближённое совпадение). Подробнее см. Пример1: Точное совпадение и приближённое совпадение в VLOOKUP.
  • Чтобы не менять последний аргумент и получать уведомление, если значение для поиска не найдено, оберните функцию VLOOKUP в функцию IFERROR:
    =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

Причина3: Значение для поиска меньше минимального значения в столбце поиска (приближённое совпадение)

Как показано на скриншоте ниже, вы выполняете поиск по приближённому совпадению. Искомое значение (ID1001) меньше минимального значения1002 в столбце поиска, поэтому VLOOKUP возвращает ошибку #N/A.

showing the usage of VLOOKUP function

Решения

Вот два решения для вас.

  • Убедитесь, что значение для поиска больше или равно минимальному значению в столбце поиска.
  • Если вы хотите, чтобы Excel уведомлял вас о том, что значение для поиска не найдено, просто вложите функцию VLOOKUP в функцию IFERROR следующим образом:
    =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

Причина4: Числа имеют формат текста

Как видно на скриншоте ниже, ошибка #N/A в этом примере вызвана несоответствием типов данных между ячейкой поиска (G6) и столбцом поиска (B6:B12) исходной таблицы. В G6 находится число, а значения в диапазоне B6:B12 — числа в текстовом формате.

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

showing the usage of VLOOKUP function

Решения

Чтобы решить эту проблему, необходимо преобразовать значение для поиска обратно в число. Вот два способа.

  • Используйте функцию Преобразовать в число
    Щёлкните по ячейке, которую хотите преобразовать из текста в число, выберите эту кнопку showing the usage of VLOOKUP function рядом с ячейкой и затем выберите Преобразовать в число.
    showing the usage of VLOOKUP function
  • Используйте удобный инструмент для пакетного преобразования между текстом и числами
    Функция Преобразовать между текстом и числами в Kutools для Excel позволяет легко преобразовать диапазон ячеек из текста в числа и наоборот. Получите30-дневную полнофункциональную бесплатную пробную версию прямо сейчас!

Причина5: Table_array не является постоянным при протягивании формулы VLOOKUP на другие ячейки

Как показано на скриншоте ниже, есть два значения для поиска в E6 и E7. После получения первого результата в F6, если протянуть формулу VLOOKUP из F6 в F7, возвращается ошибка #N/A. Это происходит потому, что ссылки на ячейки (B6:C12) по умолчанию относительные и смещаются при перемещении вниз по строкам. Диапазон таблицы смещается на B7:C13, который уже не содержит искомого балла73.

showing the usage of VLOOKUP function

Решение

Необходимо зафиксировать диапазон таблицы, чтобы он оставался постоянным, добавив знак $ перед строками и столбцами в ссылках на ячейки. Подробнее об абсолютных ссылках в Excel читайте в этом руководстве: Абсолютная ссылка в Excel (как создать и использовать).

showing the usage of VLOOKUP function

Возвращается ошибка #VALUE

Следующие условия могут привести к возврату ошибочного результата #VALUE при использовании VLOOKUP.

Причина1: Значение для поиска превышает255 символов

Как показано на скриншоте ниже, значение для поиска в ячейке H4 превышает255 символов, поэтому VLOOKUP возвращает ошибку #VALUE.

showing the usage of VLOOKUP function

Решения

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

  • INDEX и MATCH:
    =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))
    showing the usage of VLOOKUP function
  • Функция XLOOKUP (доступна в Excel365, Excel2021 и более поздних версиях):
    =XLOOKUP(H4,B5:B11,E5:E11)

Причина2: Аргумент col_index меньше1

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

Если вы введёте индекс столбца меньше1 (то есть ноль или отрицательное значение), VLOOKUP не сможет найти столбец в диапазоне.

Решение

Чтобы устранить эту ошибку, убедитесь, что аргумент индекса столбца в вашей формуле VLOOKUP — положительное число, соответствующее существующему столбцу в диапазоне.

Возвращается ошибка #REF

В этом разделе приведена одна из причин возврата ошибки #REF функцией VLOOKUP и способы её устранения.

Причина: Аргумент col_index больше количества столбцов

Как видно на скриншоте ниже, диапазон таблицы содержит только4 столбца. Однако в формуле VLOOKUP вы указали индекс столбца5, что больше количества столбцов в диапазоне. В результате VLOOKUP не сможет найти столбец и вернёт ошибку #REF.

showing the usage of VLOOKUP function

Решения

  • Укажите корректный номер столбца
    Убедитесь, что аргумент индекса столбца в вашей формуле VLOOKUP соответствует существующему столбцу в диапазоне.
  • Автоматически получать номер столбца по заданному заголовку
    Если в таблице много столбцов, определить правильный номер индекса может быть сложно. В этом случае можно вложить функцию MATCH в VLOOKUP, чтобы найти положение столбца по определённому заголовку.
    =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)
    Примечание: В приведённой выше формуле функция MATCH("Email",B5:E5,0) используется для получения номера столбца "Email" в диапазоне B6:E12. В данном случае результат —4, который используется как col_index в функции VLOOKUP.

Возвращается некорректное значение

Если вы заметили, что VLOOKUP возвращает неверный результат, это может быть вызвано следующими причинами

Причина1: Столбец для поиска не отсортирован по возрастанию

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

showing the usage of VLOOKUP function

Решение

Отсортируйте столбец для поиска по возрастанию, чтобы решить эту проблему. Для этого выполните следующие шаги:

  1. Выделите ячейки данных в столбце для поиска, перейдите на вкладку Данные, нажмите Сортировка от наименьшего к наибольшему в группе Сортировка и фильтр.
  2. В диалоговом окне Предупреждение о сортировке выберите опцию Развернуть выделение и нажмите OK.

Причина2: Столбец был вставлен или удалён

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

showing the usage of VLOOKUP function

Решения

Вот два решения для вас.

  • Вы можете вручную изменить номер индекса столбца, чтобы он соответствовал положению столбца возврата. Формулу следует изменить так:
    =VLOOKUP(H6,B6:F12,5,FALSE)
  • Если вы всегда хотите возвращать результат из определённого столбца, например, из столбца Email в этом примере, следующая формула поможет автоматически определить индекс столбца по заданному заголовку, независимо от вставки или удаления столбцов в диапазоне таблицы.
    =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

Прочие примечания по функции

  • VLOOKUP ищет значения только слева направо.
    Значение для поиска должно находиться в самом левом столбце, а возвращаемое значение — в любом столбце справа от столбца поиска.
  • Если последний аргумент оставить пустым, VLOOKUP по умолчанию использует приближённое совпадение.
  • VLOOKUP не учитывает регистр при поиске.
  • При наличии нескольких совпадений VLOOKUP возвращает только первое найденное совпадение в table_array, основываясь на порядке строк в диапазоне.

Лучшие инструменты для повышения продуктивности работы с Office

🤖 Kutools AI Aide: Совершенно новый подход к анализу данных на основе: Интеллектуальное выполнение |  Генерация кода  |  Создание пользовательских формул |  Анализ данных и построение диаграмм  |  Вызов Расширенных функций Kutools
Популярные функции: Найти, выделить или отметить дубликаты | Удалить пустые строки | Объединить столбцы или ячейки без потери данных |   Округлить без формулы ...
Супер ПОИСК: VLOOKUP по нескольким критериям | VLOOKUP с несколькими значениями | Многолистовой поиск | Распознавание нечетких соответствий ....
Расширенный раскрывающийся список: Быстро создать раскрывающийся список | Зависимый раскрывающийся список | Множественный выбор в раскрывающемся списке ....
Менеджер столбцов: Добавить определённое количество столбцов | Переместить столбцы | Переключить видимость скрытых столбцов | Сравнить диапазоны и столбцы ...
Избранные функции: Сетка фокусировки | Дизайн листа | Улучшенная строка формулы | Управление книгами и листами | Библиотека автотекста | Выбор даты | Объединить данные | Шифрование/расшифровка ячеек | Отправить письмо по списку | Супер фильтр | Специальный фильтр (фильтр ячеек с жирным/курсивом/зачёркнутым...) ...
Топ-15 наборов инструментов:12 текстовых инструментов (Добавить текст, Удалить определенные символы, ...) |50+ типов диаграмм (Диаграмма Ганта, ...) |40+ практических формул (Расчет возраста на основе даты рождения, ...) |19 инструментов вставки (Вставить QR-код, Вставить изображение по пути, ...) |12 инструментов преобразования (Преобразовать в слова, Конвертация валюты, ...) |7 инструментов для объединения и разделения (Расширенное объединение строк, Разделить ячейки, ...) | ... и многое другое

Ускорьте работу в Excel с Kutools для Excel и ощутите новую степень эффективности. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени. Нажмите здесь, чтобы выбрать нужную вам функцию...


Office Tab добавляет вкладки в Office и делает вашу работу намного проще

  • Включите редактирование и чтение с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна, а не в отдельных окнах.
  • Увеличьте свою продуктивность на50% и сократите сотни лишних кликов мышью каждый день!