Функция VLOOKUP в Excel
Функция VLOOKUP в Excel — это мощный инструмент, который позволяет искать заданное значение, сопоставляя его с первым столбцом таблицы или диапазона по вертикали, а затем возвращать соответствующее значение из другого столбца в той же строке. Несмотря на свою полезность, VLOOKUP может показаться сложной для начинающих пользователей. В этом руководстве вы освоите VLOOKUP благодаря пошаговому разбору аргументов, полезным примерам и решениям распространённых ошибок, с которыми вы можете столкнуться при использовании этой функции.
Связанные видео
Пошаговое объяснение аргументов
Как показано на скриншоте выше, функция 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.
- Приближённое совпадение - Установите этот аргумент в TRUE, 1 или оставьте пустым.
Примеры
В этом разделе приведены примеры, которые помогут вам лучше понять работу функции 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, как ближайший результат.
- В случае приближённого совпадения значения в первом столбце table_array должны быть отсортированы по возрастанию. В противном случае VLOOKUP может вернуть неверное значение.
- Здесь я заблокировал диапазон таблицы ($B$6:$C$12) в функции VLOOKUP, чтобы быстро ссылаться на один и тот же набор данных при поиске по нескольким значениям.
Пример2: Использование VLOOKUP с несколькими критериями
В этом разделе показано, как использовать VLOOKUP с несколькими условиями в Excel. Как видно на скриншоте ниже, если вы хотите определить зарплату по заданным имени (в ячейке H5) и отделу (в ячейке H6), выполните следующие шаги.
Шаг1: Добавьте вспомогательный столбец для объединения значений из столбцов поиска
В данном случае необходимо создать вспомогательный столбец для объединения значений из столбцов Имя и Отдел.
- Добавьте вспомогательный столбец слева от вашего диапазона данных и задайте ему заголовок. Смотрите скриншот:
- В этом вспомогательном столбце выберите первую ячейку под заголовком, введите следующую формулу в Строку формул, и нажмите Enter.
=C6&" "&D6
Примечание: В этой формуле используется амперсанд (&) для объединения текста из двух столбцов в одну строку.- C6 — это первое имя из столбца Имя для объединения, D6 — первый отдел из столбца Отдел для объединения.
- Значения этих двух ячеек объединяются с пробелом между ними.
- Выделите ячейку с результатом, затем перетащите маркер автозаполнения вниз, чтобы применить формулу к другим ячейкам этого столбца.
Шаг2: Примените функцию VLOOKUP с заданными критериями
Выберите ячейку, в которую хотите вывести результат (здесь я выбираю I7), введите следующую формулу в Строку формул и нажмите Enter.
=VLOOKUP(I5& " "&I6,B6:F12,5,FALSE)
Результат
- Вспомогательный столбец должен быть первым столбцом диапазона данных.
- Теперь столбец зарплаты — это пятый столбец диапазона данных, поэтому в формуле используем число 5 в качестве индекса столбца.
- Также необходимо объединить критерии в I5 и I6 (I5& " "&I6) так же, как и во вспомогательном столбце, и использовать объединённое значение в качестве аргумента lookup_value в формуле.
- Вы также можете напрямую указать оба условия в аргументе lookup_value и разделить их пробелом (если условия — текстовые, не забудьте заключить их в кавычки).
=VLOOKUP("Albee IT",B6:F12,5,FALSE)
- Лучшее решение — поиск по нескольким критериям за секундыФункция Многокритериальный поиск в Kutools для Excel позволяет легко выполнять поиск по нескольким условиям всего за несколько секунд. Получите30-дневную полнофункциональную бесплатную пробную версию прямо сейчас!
Распространённые ошибки 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 (или оставили его пустым) для приближённого совпадения, а столбец для поиска не отсортирован по возрастанию, результат может быть некорректным.
Решение
Отсортируйте столбец для поиска по возрастанию, чтобы решить эту проблему. Для этого выполните следующие шаги:
- Выделите ячейки данных в столбце для поиска, перейдите на вкладку Данные, нажмите Сортировка от наименьшего к наибольшему в группе Сортировка и фильтр.
- В диалоговом окне Предупреждение о сортировке выберите опцию Развернуть выделение и нажмите 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, основываясь на порядке строк в диапазоне.
Связанные статьи
20+ примеров VLOOKUP для начинающих и продвинутых пользователей Excel
В этом руководстве показано, как использовать функцию VLOOKUP в Excel на десятках базовых и продвинутых примеров пошагово.
VLOOKUP справа налево
Если вам нужно найти определённое значение в любом другом столбце и вернуть соответствующее значение слева, методы из этого руководства помогут вам выполнить эту задачу.
VLOOKUP снизу вверх
В этом руководстве приведены два способа поиска совпадающего значения снизу вверх.
Выполнить VLOOKUP с учётом регистра
Если вы хотите выполнить VLOOKUP с учётом регистра в Excel, способ из этого руководства вам поможет.
VLOOKUP с сохранением исходного форматирования
В этом руководстве показан способ сохранить всё форматирование результирующей ячейки при использовании VLOOKUP в Excel.
Лучшие инструменты для повышения продуктивности работы с Office
Ускорьте работу в Excel с Kutools для Excel и ощутите новую степень эффективности. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени. Нажмите здесь, чтобы выбрать нужную вам функцию...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите редактирование и чтение с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна, а не в отдельных окнах.
- Увеличьте свою продуктивность на50% и сократите сотни лишних кликов мышью каждый день!
Содержание
- Связанные видео
- Пошаговое объяснение аргументов
- Синтаксис и аргументы
- Примеры использования VLOOKUP
- Точное совпадение и приближённое совпадение
- VLOOKUP с несколькими условиями
- Распространённые ошибки и их решения
- Ошибка #N/A
- Ошибка #VALUE
- Ошибка #REF
- Некорректное значение
- Прочие примечания по функции
- Связанные статьи
- Лучшие инструменты для повышения продуктивности в Office