Згідно з доповіддю Функція Excel VLOOKUP це потужний інструмент, який допомагає вам шукати вказане значення шляхом зіставлення в першому стовпці таблиці або діапазону по вертикалі, а потім повертати відповідне значення з іншого стовпця в тому ж рядку. Незважаючи на те, що функція VLOOKUP неймовірно корисна, початківцям іноді буває складно її опанувати. Цей підручник має на меті допомогти вам освоїти 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 для точної відповідності. Тепер формула виглядає так:

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

Натисніть клавішу Enter, щоб отримати результат

Пояснюючи один за одним кожен аргумент у наведеному вище прикладі, тепер набагато легше зрозуміти синтаксис і аргументи функції VLOOKUP.

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

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

• Пошук_значення (обов’язково): значення (справжнє значення або посилання на клітинку), яке ви шукаєте. Пам’ятайте, що це значення має бути в першому стовпці table_array.
• Масив_таблиці (обов’язково): діапазон комірок містить як стовпець пошукового значення, так і стовпець повернутого значення.
• Col_index (обов’язково): ціле число представляє номер стовпця, який містить повернуте значення. Він починається з числа 1 для крайнього лівого стовпця table_array.
• Пошук_діапазону (необов’язково): логічне значення, яке визначає, чи потрібно, щоб функція VLOOKUP знаходила приблизну чи точну відповідність.
  • Приблизний збіг - Установіть для цього аргументу значення ІСТИНА, 1 або залишити порожній.
    Важливий: щоб знайти приблизний збіг, значення в першому стовпці таблиці table_array потрібно відсортувати в порядку зростання, якщо VLOOKUP поверне неправильний результат.
  • Точна відповідність - Установіть для цього аргументу значення ПОМИЛКОВИЙ or 0.

прикладів

У цьому розділі наведено кілька прикладів, які допоможуть вам мати більш повне розуміння функції VLOOKUP.

Приклад 1: точна та приблизна відповідність у VLOOKUP

Якщо ви плутаєтеся з точною та приблизною відповідністю під час використання VLOOKUP, цей розділ може допомогти вам усунути цю плутанину.

Точна відповідність у VLOOKUP

У цьому прикладі я збираюся знайти відповідні імена на основі оцінок, указаних у діапазоні E6:E8, тому я вводжу наступну формулу в клітинку F6 і перетягую маркер автозаповнення до F8. У цій формулі останній аргумент задається як ПОМИЛКОВИЙ щоб виконати пошук точної відповідності.

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

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

Приблизний збіг у VLOOKUP

Ви все ще використовуєте наведений вище приклад, якщо ви зміните останній аргумент на ІСТИНА, VLOOKUP виконає приблизний пошук відповідності. Якщо відповідності не знайдено, буде знайдено наступне за величиною значення, яке є меншим за значення пошуку, і повернеться відповідний результат.

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

Оскільки оцінка 98 не існує, VLOOKUP знаходить наступне за величиною значення, яке менше за 98, тобто 95, і повертає назву оцінки 95 як найближчий результат.

Приклад 2: використовуйте VLOOKUP із кількома критеріями

У цьому розділі показано, як використовувати VLOOKUP із кількома умовами в Excel. Як показано на знімку екрана нижче, якщо ви намагаєтеся знайти зарплату на основі наданого імені (у клітинці H5) і відділу (у клітинці H6), виконайте наведені нижче дії, щоб це зробити.

Крок 1: додайте допоміжний стовпець, щоб об’єднати значення зі стовпців пошуку

У цьому випадку нам потрібно створити допоміжний стовпець для об’єднання значень із Назва стовпець і відділ колонка.

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

   =C6&" "&D6

   
   примітки: у цій формулі ми використовуємо амперсанд (&), щоб об’єднати текст у двох стовпцях і отримати єдиний фрагмент тексту.

   • C6 це перша назва Назва колонка для приєднання, D6 є першим відділом в відділ колонка для приєднання.
   • Значення цих двох клітинок об’єднані через пробіл між ними.
3. Виберіть цю клітинку результату, а потім перетягніть Ручка автозаповнення вниз, щоб застосувати цю формулу до інших комірок у тому самому стовпці.
   
   

Крок 2. Застосуйте функцію VLOOKUP із заданими критеріями

Виберіть клітинку, у яку ви хочете вивести результат (тут я вибираю I7), введіть наступну формулу в Рядок формули, а потім натисніть вводити.

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

Результат

Поширені помилки VLOOKUP і способи їх вирішення

У цьому розділі перелічено типові помилки, з якими ви можете зіткнутися під час використання VLOOKUP, і надано рішення для їх усунення.

Повертається помилка #N/A

Найпоширенішою помилкою VLOOKUP є помилка #N/A, яка означає, що Excel не вдалося знайти потрібне значення. Ось кілька причин, чому VLOOKUP може повертати помилку #N/A.

Причина 1: шукане значення не міститься в першому стовпці table_array

Одним із обмежень Excel VLOOKUP є те, що він дозволяє дивитися лише зліва направо. Отже, пошукові значення мають бути в першому стовпці table_array.

Як показано на знімку екрана нижче, я хочу повернути ім’я на основі вказаної посади. Тут шукане значення (менеджер по збуту) знаходиться у другому стовпці table_array, а значення, що повертається, знаходиться ліворуч від стовпця пошуку, тому VLOOKUP повертає помилку #N/A.

Рішення

Ви можете застосувати будь-яке з наведених нижче рішень, щоб виправити цю помилку.

• Переставте колонки
  
  Ви можете змінити порядок стовпців, щоб розмістити стовпець пошуку в першому стовпці table_array.
• Використовуйте функції INDEX і MATCH разом
  
  Тут ми використовуємо функції INDEX і MATCH разом як альтернативу VLOOKUP для вирішення цієї проблеми.

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

  
• Використовуйте функцію XLOOKUP (доступна в Excel 365, Excel 2021 і пізніших версіях)
  

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

Причина 2: Значення пошуку не знайдено в стовпці пошуку (точна відповідність)

Однією з найпоширеніших причин, чому VLOOKUP повертає помилку #N/A, є те, що значення, яке ви шукаєте, не знайдено.

Як показано в наведеному нижче прикладі, ми збираємося знайти ім’я на основі наданої оцінки 98 у E6. Однак ця оцінка не існує в першому стовпці діапазону даних, тому VLOOKUP повертає результат помилки #N/A.

Рішення

Щоб виправити цю помилку, ви можете спробувати одне з наведених нижче рішень.

• Якщо ви хочете, щоб функція VLOOKUP шукала наступне за величиною значення, яке є меншим за значення пошуку, змініть останній аргумент ПОМИЛКОВИЙ (точна відповідність) до ІСТИНА (приблизний збіг). Для отримання додаткової інформації див Приклад 1: точна та наближена відповідність за допомогою VLOOKUP.
• Щоб уникнути зміни останнього аргументу та отримати нагадування, якщо шукане значення не знайдено, ви можете додати функцію VLOOKUP до функції IFERROR:
  

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

Причина 3: Значення пошуку менше за найменше значення в стовпці пошуку (приблизний збіг)

Як показано на знімку екрана нижче, ви виконуєте приблизний пошук відповідності. Значення, яке ви шукаєте (ідентифікаційний номер 1001 у цьому випадку), менше, ніж найменше значення 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 for Excel допомагає легко конвертувати діапазон комірок із тексту в номер і навпаки. Отримайте 30-денну повнофункціональну безкоштовну пробну версію зараз!
  

Причина 5: table_array не є постійним під час перетягування формули VLOOKUP до інших клітинок

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

рішення

Вам потрібно заблокувати табличний масив, щоб він залишався постійним, додавши a $ поставити знак перед рядками та стовпцями в посиланнях на клітинки. Щоб дізнатися більше про абсолютне посилання в Excel, перегляньте цей посібник: Абсолютний довідник Excel (як зробити та використовувати).

Повертається помилка #VALUE

Наступні умови можуть призвести до того, що VLOOKUP повертає результат помилки #VALUE.

Причина 1: Значення пошуку перевищує 255 символів

Як показано на знімку екрана нижче, значення пошуку в клітинці H4 перевищує 255 символів, тому VLOOKUP повертає результат помилки #VALUE.

Рішення

Щоб обійти це обмеження, ви можете застосувати іншу функцію пошуку, яка може обробляти довші рядки. Спробуйте одну з наступних формул.

• INDEX і MATCH:
  

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

  
• Функція XLOOKUP (доступно в Excel 365, Excel 2021 і пізніших версіях):
  

  =XLOOKUP(H4,B5:B11,E5:E11)

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

Індекс стовпця вказує номер стовпця в масиві таблиці, який містить значення, яке потрібно повернути. Цей аргумент має бути позитивним числом, яке відповідає дійсному стовпцю в масиві таблиці.

Якщо ви введете індекс стовпця, менший за 1 (тобто нульовий або негативний), VLOOKUP не зможе знайти стовпець у масиві таблиці.

рішення

Щоб вирішити цю проблему, переконайтеся, що аргумент індексу стовпця у вашій формулі VLOOKUP є позитивним числом, яке відповідає дійсному стовпцю в масиві таблиці.

Повертається помилка #REF

У цьому розділі наведено одну з причин, чому VLOOKUP повертає помилку #REF, і надано способи вирішення цієї проблеми.

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

Як ви можете бачити на скріншоті нижче, масив таблиці має лише 4 стовпці. Однак індекс стовпця, який ви вказали у формулі VLOOKUP, становить 5, що перевищує кількість стовпців у масиві таблиці. У результаті VLOOKUP не зможе знайти стовпець і зрештою поверне помилку #REF.

Рішення

• Вкажіть правильний номер стовпця
  Переконайтеся, що аргумент індексу стовпця у формулі VLOOKUP є числом, яке відповідає дійсному стовпцю в масиві таблиці.
• Автоматично отримати номер стовпця на основі вказаного заголовка стовпця
  Якщо таблиця містить багато стовпців, у вас можуть виникнути проблеми з визначенням правильного номера стовпця. Тут ви можете вкласти функцію MATCH у функцію VLOOKUP, щоб знайти положення стовпця на основі заголовка певного стовпця.

  =VLOOKUP(G6,B6:E12,MATCH("Email",B5:E5,0),FALSE)

  примітки: У наведеній вище формулі MATCH("Електронна пошта";B5:E5; 0) функція використовується для отримання номера стовпця "Електронна пошта" у діапазоні дат B6:E12. Тут результат 4, який використовується як col_index у функції VLOOKUP.

Повертається неправильне значення

Якщо ви виявите, що VLOOKUP не повертає правильний результат, це може бути викликано наведеними нижче причинами

Причина 1: стовпець пошуку не відсортовано за зростанням

Якщо ви встановили останній аргумент як ІСТИНА (Або залишив його порожнім) для приблизного збігу, а стовпець пошуку не відсортовано за зростанням, результуюче значення може бути неправильним.

рішення

Сортування стовпця пошуку в порядку зростання може допомогти вам вирішити цю проблему. Для цього виконайте наведені нижче дії.

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

Причина 2: стовпець вставлено або видалено

Як показано на знімку екрана нижче, значення, яке я спочатку хотів повернути, знаходиться в четвертому стовпці масиву таблиці, тому я вказав номер col_index як 4. Коли вставляється новий стовпець, стовпець результату стає п’ятим стовпцем таблиці масиву, через що VLOOKUP повертає результат із неправильного стовпця.

Рішення

Ось два рішення для вас.

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

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

• Якщо ви завжди хочете повертати результат із певного стовпця, наприклад стовпця електронної пошти в цьому прикладі. Наведена нижче формула може допомогти автоматично зіставити індекс стовпця на основі заданого заголовка стовпця, незалежно від того, вставлено чи видалено стовпці з масиву таблиці.

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

Інші примітки щодо функцій

• VLOOKUP шукає значення лише зліва направо.
  Значення пошуку знаходиться в крайньому лівому стовпці, а значення результату має бути в будь-якому стовпці праворуч від стовпця пошуку.
• Якщо залишити останній аргумент порожнім, VLOOKUP за умовчанням використовує приблизний збіг.
• VLOOKUP виконує пошук без урахування регістру.
• Для кількох збігів VLOOKUP повертає лише перший збіг, знайдений у масиві таблиці, на основі порядку рядків у масиві таблиці.

Статті по темі