Функція 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, потрібно задати чотири параметри.

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

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

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

Приклади

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

Приклад 1

Функція VLOOKUP (B3,B2:E7,2,FALSE) шукає Фонтану в першому стовпці (стовпець 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) і повертає 03/04/1955. Після цього YEARFRAC віднімає цю дату народження від 06.06.2014 і повертає значення, яке потім перетворюється на ціле число 59.

Приклад 5

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

Проблема	Помилка
Повернуто помилкове значення	Якщо range_lookup має значення TRUE або left out (Істина), перший стовпець потрібно відсортувати в алфавітному або числовому порядку. Якщо перший стовпець не відсортовано, повернуте значення бути несподіваним. Відсортуйте перший стовпець або використовуйте 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 або отримати підтримку в спільнотах.