Функція VLOOKUP

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

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

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

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

Ваш браузер не підтримує відео. Інсталюйте Microsoft Silverlight, Adobe Flash Player або Internet Explorer 9.

Поради.:

Основне завдання функції VLOOKUP – упорядкування ваших даних, щоб значення, яке ви шукаєте (фрукт), розташовувалося ліворуч від повернутого значення, що потрібно знайти (кількість).
Якщо ви абонент MicrosoftCopilot 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. *Шукане_значення* може бути значенням або посиланням на клітинку.
таблиця (обов’язково)	Діапазон клітинок, у якому функція VLOOKUP шукатиме *шукане_значення* та повернуте значення. Можна використовувати іменований діапазон або таблицю, а замість посилань на клітинки в аргументі можна використовувати імена. Перший стовпець у діапазоні клітинок має містити *lookup_value*. Діапазон клітинок також має містити повернуте значення, яке потрібно знайти. Докладні відомості про вибір діапазонів на аркуші.
номер_стовпця (обов’язково)	Номер стовпця (починаючи з 1 для найбільш лівого стовпця *table_array*), який містить повернуте значення.
точність_пошуку (необов’язково)	Логічне значення, що вказує, який саме збіг потрібно знайти за допомогою функції VLOOKUP: приблизний чи точний. Приблизний збіг – 1/TRUE припускає, що перший стовпець таблиці відсортовано числовим або за алфавітом, а потім шукатиме найближче значення. Це стандартний метод, якщо не вказано інший. Наприклад, =VLOOKUP(90;A1:B100;2;TRUE). Точний збіг – 0/FALSE шукає точне значення в першому стовпці. Наприклад, =VLOOKUP("Коваль";A1:B100;2;ХИБНІСТЬ).

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

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

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

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

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

Приклади

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

Приклад 1

=VLOOKUP (B3;B2:E7;2;FALSE)

Функція VLOOKUP шукає Фонтану в першому стовпці (стовпець B) у table_array B2:E7 і повертає Олів'є з другого стовпця (стовпець C) table_array. Значення False повертає точний збіг.

Приклад 2

=VLOOKUP (102;A2:C7;2;FALSE)

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

Приклад 3

=IF(VLOOKUP(103;A1:E7;2;FALSE)="Souse","Located","Not found")

Функція IF перевіряє, чи функція VLOOKUP повертає Сузу як прізвище працівника, що повертає значення 103 (lookup_value) у діапазоні A1:E7 (table_array). Оскільки прізвище, яке відповідає 103, – Leal, умова IF хибна, а не знайдено.

Приклад 4

=INT(YEARFRAC(DATE(2014;6;30);VLOOKUP(105;A2:E7;5;FLASE);1))

Функція VLOOKUP шукає дату народження працівника, який відповідає 109 (lookup_value) у діапазоні A2:E7 (table_array) і повертає 04.03.1955. Після цього YEARFRAC віднімає цю дату народження від 06.06.2014 і повертає значення, яке потім перетворюється на ціле число 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.

У цьому прикладі повернуте значення – Берк , тобто прізвище, яке відповідає 105.

Проблема	Помилка
Повернуто помилкове значення	Якщо параметр *точність_пошуку* має значення TRUE (істина) або його не зазначено, перший стовпець необхідно відсортувати за алфавітом або в числовому порядку. Якщо перший стовпець не відсортовано, повернуте значення бути несподіваним. Відсортуйте перший стовпець або використовуйте FALSE (хибність) для пошуку точного збігу.
#N/A у клітинці	Якщо аргумент *точність_пошуку* має значення TRUE, а значення аргументу *шукане_значення* менше за найменше значення в першому стовпці *таблиці, буде повернуто значення помилки #N/A. Якщо аргумент точність_пошуку* має значення FALSE, значення помилки свідчить про те, що точний номер не знайдено. Докладні відомості про виправлення помилки #N/A у функції VLOOKUP див. в цій статті.
#REF! у клітинці	Якщо значення *номер_стовпця* більше кількості стовпців у *таблиці-масиві*, . Докладні відомості про виправлення помилки #REF! у функції VLOOKUP див..
#VALUE! у клітинці	Якщо значення *таблиця_масив* менше за 1, . Докладні відомості про виправлення помилки #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).

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