Функция VLOOKUP в Excel

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

• Пошаговое объяснение аргументов
• Синтаксис и аргументы
• Примеры использования VLOOKUP
  Точное совпадение и приближённое совпадение |VLOOKUP с несколькими условиями
• Распространённые ошибки VLOOKUP и их решения
  Ошибка #N/A | Ошибка #VALUE | Ошибка #REF | Некорректное значение

Примеры VLOOKUP .xlsx Ошибки VLOOKUP .xlsx

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

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

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

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

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

=VLOOKUP(

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

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

=VLOOKUP(G6

Шаг3: Укажите диапазон таблицы

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

=VLOOKUP(G6,B6:E12

=VLOOKUP(G6,$B$6:$E$12

Шаг4: Укажите столбец, из которого нужно вернуть значение

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

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

=VLOOKUP(G6,B6:E12,4

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

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

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

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

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

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

Пошагово разобрав каждый аргумент на приведённом выше примере, синтаксис и аргументы функции 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.

Приближённое совпадение в VLOOKUP

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

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

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

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

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

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

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

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

   =C6&" "&D6

   
   Примечание: В этой формуле используется амперсанд (&) для объединения текста из двух столбцов в одну строку.

   • C6 — это первое имя из столбца Имя для объединения, D6 — первый отдел из столбца Отдел для объединения.
   • Значения этих двух ячеек объединяются с пробелом между ними.
3. Выделите ячейку с результатом, затем перетащите маркер автозаполнения вниз, чтобы применить формулу к другим ячейкам этого столбца.
   
   

Шаг2: Примените функцию VLOOKUP с заданными критериями

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

=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)

Результат

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

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

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

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

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

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

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

Решения

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

• Переставьте столбцы
  
  Вы можете переставить столбцы так, чтобы столбец для поиска оказался первым в table_array.
• Используйте функции INDEX и MATCH вместе
  
  В этом случае мы используем функции INDEX и MATCH вместе как альтернативу VLOOKUP для решения данной проблемы.

  =INDEX(B6:B12,MATCH(F6,C6:C12,0))

  
• Используйте функцию XLOOKUP (доступна в Excel365, Excel2021 и более поздних версиях)
  

  =XLOOKUP(F6,C6:C12,B6:B12)

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

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

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

Решения

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

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

  =IFERROR(VLOOKUP(E8,$B$6:$C$12,2,FALSE),"Not found")

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

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

Решения

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

• Убедитесь, что значение для поиска больше или равно минимальному значению в столбце поиска.
• Если вы хотите, чтобы Excel уведомлял вас о том, что значение для поиска не найдено, просто вложите функцию VLOOKUP в функцию IFERROR следующим образом:
  

  =IFERROR(VLOOKUP(G6,B6:E12,4,TRUE),"Not found")

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

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

Решения

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

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

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

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

Решение

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

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

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

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

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

Решения

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

• INDEX и MATCH:
  

  =INDEX(E5:E11, MATCH(TRUE, INDEX(B5:B11=H4, 0), 0))

  
• Функция 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.

Решения

• Укажите корректный номер столбца
  Убедитесь, что аргумент индекса столбца в вашей формуле 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 (или оставили его пустым) для приближённого совпадения, а столбец для поиска не отсортирован по возрастанию, результат может быть некорректным.

Решение

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

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

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

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

Решения

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

• Вы можете вручную изменить номер индекса столбца, чтобы он соответствовал положению столбца возврата. Формулу следует изменить так:

  =VLOOKUP(H6,B6:F12,5,FALSE)

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

  =VLOOKUP(H6,B6:F12,MATCH("Email",B5:E5,0),FALSE)

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

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

Связанные статьи