Функція VLOOKUP

Застосовується до
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel 2024 Excel 2024 для Mac Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2016

Порада.

Спробуйте скористатися новою функцією XLOOKUP – вдосконаленою версією функції VLOOKUP, яка працює в будь-якому напрямку та за замовчуванням повертає точні збіги, що робить її простішою та зручнішою у використанні, ніж її попередниця.

Функція VLOOKUP використовується для пошуку елементів у таблиці або діапазоні за рядком. Наприклад, можна знайти ціну на запчастину до автомобіля за її номером або знайти ім'я працівника за його ідентифікаційним номером.

У найпростішому випадку функція VLOOKUP має такий вигляд:

=VLOOKUP(те, що ви хочете знайти, де ви бажаєте шукати, номер стовпця в діапазоні, який містить значення, яке потрібно повернути, повертає приблизний або точний збіг – як 1/TRUE або 0/FALSE).

Порада.

  • Основне завдання функції VLOOKUP – упорядкування ваших даних, щоб значення, яке ви шукаєте (фрукт), розташовувалося ліворуч від повернутого значення, що потрібно знайти (кількість).
  • Якщо у вас є передплатник Microsoft Copilot, Copilot може полегшити вставлення та використання функцій VLookup або XLookup. Див . статтю "Отримання аналітики даних за допомогою Copilot в Excel".

Технічні подробиці

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

Синтаксис

VLOOKUP(шукане_значення;таблиця;номер_стовпця;[точність_пошуку])

Наприклад:

  • =VLOOKUP(A2;A10:C20;2;TRUE)
  • =VLOOKUP("Самойленко";B2:E7;2;FALSE)
  • =VLOOKUP(A2;'Відомості про клієнта'! A:F,3,FALSE)
Ім’я аргументу Опис
шукане_значення (обов’язково) Значення, яке потрібно перевірити. Значення, яке потрібно знайти, має бути в першому стовпці діапазону клітинок, зазначених в аргументі table_array .
Наприклад, якщо таблиця охоплює клітинки B2:D7, lookup_value має бути в стовпці B.
Lookup_value може бути значенням або посиланням на клітинку.
таблиця (обов’язково) Діапазон клітинок, у якому функція VLOOKUP шукатиме lookup_value та повернуте значення. Можна використати іменований діапазон або таблицю, а також використовувати імена в аргументі замість посилань на клітинки.
Перший стовпець у діапазоні клітинок має містити lookup_value. Діапазон клітинок також має містити повернуте значення, яке ви хочете знайти.
номер_стовпця (обов’язково) Номер стовпця (починаючи з 1 для крайнього лівого стовпця table_array), який містить повернуте значення.
точність_пошуку (необов’язково) Логічне значення, що вказує, який саме збіг потрібно знайти за допомогою функції VLOOKUP: приблизний чи точний.
  • Приблизний збіг – 1/TRUE припускає, що перший стовпець у таблиці відсортовано за числовими або алфавітними значеннями, а потім буде знайдено найближче значення. Це стандартний метод, якщо не вказано інший. Наприклад, =VLOOKUP(90;A1:B100;2;TRUE).
  • Точний збіг – 0/FALSE шукає точне значення в першому стовпці. Наприклад, =VLOOKUP("Коваль";A1:B100;2;ХИБНІСТЬ).

Початок роботи

Щоб побудувати синтаксис функції VLOOKUP, потрібно задати чотири параметри.

  1. Шукане значення.
  2. Діапазон, який його містить. Пам’ятайте, що функція VLOOKUP працює належним чином, лише якщо шукане значення міститься в першому стовпці діапазону. Наприклад, якщо його розташовано в клітинці C2, діапазон має починатися зі стовпця C.
  3. Номер стовпця в діапазоні, який містить значення, що повертається. Наприклад, якщо вказати діапазон B2:D11, B вважатиметься першим стовпцем, C – другим і так далі.
  4. За необхідності можна задати TRUE, щоб шукати приблизне значення, або FALSE, щоб отримати точний збіг. Якщо нічого не вказано, за замовчуванням завжди використовуватиметься значення TRUE (приблизний збіг).

Тепер давайте об’єднаємо все описане вище разом:

= VLOOKUP (значення, яке потрібно знайти, діапазон, у якому його потрібно шукати, номер стовпця в діапазоні, який містить значення, що повертається, знак наближення (TRUE) або точний збіг (FALSE)).

Приклади

Нижче наведено кілька прикладів того, як можна використовувати функцію VLOOKUP.

Приклад 1

=VLOOKUP (B3,B2:E7,2,FALSE) Функція VLOOKUP шукає Фонтана в першому стовпці (стовпець B) у table_array B2:E7 і повертає значення

Приклад 2

=VLOOKUP (102,A2:C7,2,FALSE) VLOOKUP шукає точний збіг (FALSE) прізвища 102 (lookup_value) у другому стовпці (стовпець B) у діапазоні A2:C7 і повертає значення

Приклад 3

=IF(VLOOKUP(103;A1:E7;2;FALSE)=Соуза;Розташовано;Не знайдено) Функція IF перевіряє, чи функція VLOOKUP повертає прізвище Соузи як прізвище працівника, що відповідає 103 (lookup_value) у діапазоні A1:E7 (table_array). Оскільки прізвище, що відповідає 103, – Ліл, умова IF має значення False, тому відображається Not Found.

Приклад 4

=INT(YEARFRAC(DATE(2014;6;30);VLOOKUP(105;A2:E7;5;FLASE);1)) VLOOKUP шукає дату народження працівника, яка відповідає 109 (lookup_value) у діапазоні A2:E7 (table_array), і повертає 03/04/1955. Потім функція YEARFRAC віднімає цю дату народження від 30.06.2014 і повертає значення, яке функція INY перетворює на ціле число 59.

Приклад 5

IF(ISNA(VLOOKUP(105,A2:E7,2,FLASE))=TRUE,Працівника не знайдено,VLOOKUP(105,A2:E7,2,FALSE)) Функція IF перевіряє, чи функція VLOOKUP повертає значення прізвища зі стовпця B для 105 (lookup_value). Якщо функція VLOOKUP знайде прізвище, то функція IF відобразить прізвище, в іншому разі функція IF поверне значення «Працівника не знайдено». Функція ISNA перевіряє, якщо функція VLOOKUP повертає #N/A, то замість помилки замість #N/A відображається фраза

Поширені проблеми

Проблема Помилка
Повернуто помилкове значення Якщо range_lookup має значення TRUE (істина) або його не зазначено, перший стовпець необхідно відсортувати за алфавітом або в числовому порядку. Якщо перший стовпець не відсортовано, повернуте значення бути несподіваним. Відсортуйте перший стовпець або використовуйте FALSE (хибність) для пошуку точного збігу.
#N/A у клітинці
  • Якщо range_lookup має значення TRUE, а значення в lookup_value менше за найменше значення в першому стовпці table_array, буде повернуто значення помилки #N/A.
  • Якщо range_lookup має значення FALSE, значення помилки #N/A вказує на те, що точне число не знайдено.
Докладні відомості про виправлення помилки #N/A у функції VLOOKUP див. в цій статті.
#REF! у клітинці Якщо col_index_num перевищує кількість стовпців у таблиці, ви отримаєте #REF! .
Докладні відомості про виправлення помилки #REF! у функції VLOOKUP, див. статтю Виправлення помилок #REF!.
#VALUE! у клітинці Якщо значення table_array менше 1, ви отримаєте #VALUE! .
Докладні відомості про виправлення помилки #VALUE! у функції VLOOKUP, див. статтю Виправлення помилки #VALUE! у функції VLOOKUP.
У клітинці відображається #NAME? Що #NAME? зазвичай вказує на те, що у формулі немає лапок. Щоб знайти прізвище особи, переконайтеся, що його взято в лапки у формулі. Наприклад, введіть прізвище Самойленко у формулі =VLOOKUP("Самойленко";B2:E7;2;FALSE).
Докладні відомості про виправлення помилки #NAME! див. в цій статті.
#РОЗГОРТАННЯ! у клітинці Ця конкретна помилка #SPILL! зазвичай означає, що формула використовує як посилання на неявний перетин значення шуканого стовпця. Наприклад, =VLOOKUP(A :A;A:C;2;FALSE). Цю проблему можна вирішити, прив'язавши посилання до підстановки з оператором @, наприклад: =VLOOKUP(@A:A;A:C,2;FALSE). Крім того, ви можете скористатися традиційним методом VLOOKUP і додати посилання не на цілий стовпець, а на одну клітинку: =VLOOKUP(A2;A:C;2;FALSE).

Практичні поради

Зробіть це Результат
Використання абсолютних посилань для range_lookup Абсолютні посилання дають змогу заповнити формулу вниз, щоб вона завжди була спрямована в один і той самий діапазон пошуку.
Дізнайтесь, як використовувати абсолютні посилання на клітинку.
Не зберігайте числа чи дати як текст. Під час пошуку дати або числових значень переконайтеся, що дані в першому стовпці table_array не зберігаються у вигляді тексту. У цьому разі функція VLOOKUP може повернути хибне або неочікуване значення.
Відсортуйте перший стовпець Відсортуйте перший стовпець table_array , перш ніж використовувати функцію VLOOKUP, якщо range_lookup має значення TRUE.
Використовуйте символи узагальнення Якщо range_lookup має значення FALSE, а lookup_value – це текст, у lookup_value можна використовувати символи узагальнення: знак питання (?) і зірочку (*). Знак питання відповідає будь-якому одному символу. Зірочка відповідає будь-якій послідовності символів. Якщо потрібно знайти власне знак питання або зірочку, перед відповідним символом введіть тильду (~).
Наприклад, =VLOOKUP("Fontan?",B2:E7,2,FALSE) шукатиме всі екземпляри прізвища Самойленко, у яких остання буква може відрізнятися.
Переконайтеся, що ваші дані не містять помилкові символи. Під час пошуку текстових значень у першому стовпці переконайтеся, що дані в ньому не містять пробілів на початку або в кінці, неузгоджених прямих (' або ") і фігурних (' або ") лапок або недрукованих символів. У таких випадках функція VLOOKUP може повернути хибне або неочікуване значення.
Щоб отримати точні результати, можливо, знадобиться видалити пробіли наприкінці клітинки після значень таблиці за допомогою функції CLEAN або TRIM.

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech у Excel або отримати підтримку в спільнотах.