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

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

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

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

Ваш браузер не підтримує відео.

Порада.: Основне завдання функції VLOOKUP – упорядкування ваших даних, щоб значення, яке ви шукаєте (фрукт), розташовувалося ліворуч від повернутого значення, що потрібно знайти (кількість).

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

Синтаксис 

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

Наприклад:

  • =VLOOKUP(A2;A10:C20;2;ІСТИНА)

  • =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, потрібно задати чотири параметри.

  1. Шукане значення.

  2. Діапазон, який його містить. Пам’ятайте, що функція VLOOKUP працює належним чином, лише якщо шукане значення міститься в першому стовпці діапазону. Наприклад, якщо його розташовано в клітинці C2, діапазон має починатися зі стовпця C.

  3. Номер стовпця в діапазоні, який містить значення, що повертається. Наприклад, якщо вказати діапазон B2:D11, B вважається першим стовпцем, C – другим і так далі.

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

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

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

Приклади

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

Приклад 1

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

Функція VLOOKUP шукає Fontana в першому стовпці (стовпці B) у table_array B2:E7 і повертає значення Olivier із другого стовпця (стовпця 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";"Не знайдено")

Функція 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.20 і повертає значення, яке потім перетворюється за допомогою 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 повертає значення Employee not found (Працівник не знайдено). Функція ISNA гарантує, що якщо функція VLOOKUP повертає #N/A, замість результату пошуку (A#N/A).



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

За допомогою VLOOKUP можна об'єднати кілька таблиць в одну, якщо одна з таблиць містить спільні поля з усіма іншими. Це може бути особливо корисно, якщо потрібно надати спільний доступ до книги користувачам, які мають старіші версії Excel, які не підтримують функції даних із кількома таблицями як джерела даних. Для цього об'єднавши джерела даних в одну таблицю та змінивши джерело даних функції даних на нову таблицю, цю функцію можна використовувати в старіших версіях Excel (за умови, що функція даних підтримується в старішій версії).

Аркуш зі стовпцями, які використовують VLOOKUP для отримання даних з інших таблиць

Тут стовпці A–F і H мають значення або формули, які використовують лише значення на аркуші, а в решті стовпців використовуються VLOOKUP і значення стовпців A (клієнтський код) і стовпець B (Attorney), щоб отримати дані з інших таблиць.

  1. Скопіюйте таблицю, яка містить спільні поля, на новий аркуш і надайте їй ім'я.

  2. На вкладці > Знаряддя даних >зв'язки, щоб відкрити діалогове вікно Керування зв'язками.

    Діалогове вікно «Керування зв'язками»
  3. Зверніть увагу на таке:

    • Поле, яке зв'яже таблиці (зазначене в дужках у діалоговому вікні). Це ім'lookup_value для формули VLOOKUP.

    • Ім'я пов'язаної таблиці підстановки. Це – table_array формулі VLOOKUP.

    • Поле (стовпець) пов'язаної таблиці підстановки з даними, які потрібно додати до нового стовпця. Ці відомості не відображаються в діалоговому вікні Керування зв'язками – потрібно переглянути пов'язану таблицю підстановки, щоб дізнатися, яке поле потрібно отримати. Потрібно занотувати номер стовпця (A=1), col_index_num у формулі.

  4. Щоб додати поле до нової таблиці, введіть формулу VLOOKUP у перший пустий стовпець, використовуючи відомості, зібрані на кроці 3.

    У нашому прикладі стовпець G використовує attorney (the lookup_value)для отримання даних bill Rate із четвертого стовпця(col_index_num = 4) з таблиці аркуша Attorneys (tblAttorneys) (table_array)з формулою =VLOOKUP([@Attorney];tbl_Attorneys;4;FALSE).

    У формулі також може використовуватися посилання на клітинку та посилання на діапазон. У нашому прикладі це буде =VLOOKUP(A2;'Attorneys'! A:D,4,FALSE).

  5. Продовжуйте додавати поля, доки не знадобляться всі потрібні поля. Якщо ви намагаєтеся підготувати книгу з функціями даних, які використовують кілька таблиць, змініть джерело даних функції на нову таблицю.

Проблема

Помилка

Повернуто помилкове значення

Якщо параметр точність_пошуку має значення 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! у клітинці

Зокрема, #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("Fontan?";B2:E7;2;FALSE) шукатиме всі екземпляри прізвище Fontana, у яких остання буква може відрізнятися.

Переконайтеся, що ваші дані не містять помилкові символи.

Під час пошуку текстових значень у першому стовпці таблиці переконайтеся, що дані в ньому не містять пробілів на початку або в кінці, неузгоджених прямих (' або ") і фігурних (‘ або “) лапок або недрукованих символів. У таких випадках функція VLOOKUP може повернути хибне або неочікуване значення.

Щоб отримати точні результати, можливо, знадобиться видалити пробіли наприкінці клітинки після значень таблиці за допомогою функції CLEAN або TRIM.

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

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

Додаткові відомості

Короткий довідник: оновлення VLOOKUP
Короткий довідник: поради з виправлення неполадок із VLOOKUP
Виправлення помилки #VALUE! у функції VLOOKUP
Виправлення помилки #N/A у функції VLOOKUP
Огляд формул в Excel
Способи уникнення недійсних формул
Виявлення помилок у формулах
Функції Excel (за алфавітом)
Функції Excel (за категоріями)
Функція VLOOKUP (безкоштовне ознайомлення)

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

Отримуйте нові функції раніше за інших
Приєднатися до оцінювачів Microsoft Office

Ця інформація корисна?

Наскільки ви задоволені якістю мови?
Що вплинуло на ваші враження?

Дякуємо за відгук!

×