Функція VLOOKUP

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

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

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

Створення завдання

Це відео входить до навчального курсу Функція VLOOKUP: коли та як її використовувати.

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

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

Синтаксис

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

Наприклад:

  • =VLOOKUP(105;A2:C7;2;TRUE)

  • =VLOOKUP("Самойленко";B2:E7;2;FALSE)

Ім’я аргументу

Опис

шукане_значення    (обов’язково)

Значення, яке потрібно перевірити. Значення, яке потрібно знайти, має бути в першому стовпці діапазону клітинок, зазначених у таблиці .

Наприклад, якщо таблиця охоплює клітинки B2:D7, то шукане_значення має бути в стовпці B. Див. рисунок нижче. Шукане_значення може бути значенням або посиланням на клітинку.

таблиця    (обов’язково)

Діапазон клітинок, у якому функція VLOOKUP шукатиме шукане_значення та повернуте значення.

Перший стовпець у діапазоні клітинок має містити шукане_значення (наприклад, "Прізвище" на рисунку нижче). Діапазон клітинок також має містити повернуте значення, яке ви хочете знайти (наприклад, "Ім’я" на рисунку нижче).

Докладні відомості про вибір діапазонів на аркуші.

номер_стовпця    (обов’язково)

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

точність_пошуку    (необов’язково)

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

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

  • Значення FALSE (хибність) шукає точне значення у першому стовпці.

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

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

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

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

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

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

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

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

На зображенні нижче показано, як настроїти функцію VLOOKUP, щоб вона повертала ціну на гальмівні диски(85,73).

Приклад застосування функції VLOOKUP
  1. D13 – це шукане_значення (значення, яке потрібно знайти).

  2. B2–E11 (виділено жовтим у таблиці) – це таблиця (діапазон, якій містить шукане значення).

  3. 3 – це номер_стовпця (номер стовпця в таблиці, який містить значення, що повертається). У цьому прикладі третій стовпець таблиці – Ціна на запчастини, тому формула виводитиме значення з цього стовпця.

  4. FALSE – це точність _ пошуку, тому результат буде точним збігом.

  5. Результат обчислення формули VLOOKUP – 85,73 (вартість гальмівних дисків).

Приклади

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

Приклад 1

Приклад 1: застосування функції VLOOKUP

Приклад 2

Приклад 2: застосування функції VLOOKUP

Приклад 3

Приклад 3: застосування функції VLOOKUP

Приклад 4

Приклад 4: застосування функції VLOOKUP

Приклад 5

Приклад 5: застосування функції VLOOKUP

Проблема

Помилка

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

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

#N/A у клітинці

  • Якщо аргумент точність_пошуку має значення TRUE, а значення аргументу шукане_значення менше за найменше значення в першому стовпці таблиці, буде повернуто значення помилки #N/A.

  • Якщо аргумент точність_пошуку має значення FALSE, значення помилки свідчить про те, що точний номер не знайдено.

Докладні відомості про виправлення помилки #N/A у функції VLOOKUP див. в цій статті.

#REF! у клітинці

Якщо значення аргументу номер_стовпця перевищує кількість стовпців у таблиці, буде повернуто значення помилки #REF!.

Докладні відомості про виправлення помилки #REF! у функції VLOOKUP див. в цій статті.

#VALUE! у клітинці

Якщо значення аргументу таблиця менше за 1, буде повернуто значення помилки #VALUE!.

Докладні відомості про виправлення помилки #VALUE! у функції VLOOKUP див. в цій статті.

#NAME? у клітинці

Значення помилки #NAME? зазвичай вказує на те, що у формулі немає лапок. Щоб знайти прізвище особи, переконайтеся, що його взято в лапки у формулі. Наприклад, введіть прізвище Самойленко у формулі =VLOOKUP("Самойленко";B2:E7;2;FALSE).

Докладні відомості про виправлення помилки #NAME! див. в цій статті.

Дія

Результат

Використовуйте абсолютні посилання для значення точність_пошуку

Абсолютні посилання дають змогу заповнити формулу вниз, щоб вона завжди була спрямована в один і той самий діапазон пошуку.

Дізнайтесь, як використовувати абсолютні посилання на клітинку.

Не зберігайте числа чи дати як текст.

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

Відсортуйте перший стовпець

Відсортуйте перший стовпець таблиці, перш ніж використовувати функцію VLOOKUP, коли для аргументу точність_пошуку встановлено значення TRUE.

Використовуйте символи узагальнення

Якщо аргумент точність_пошуку має значення FALSE (хибність), а як аргумент шукане_значення використовується текст, в аргументі шукане_значення можна використовувати символи узагальнення – знак питання (?) і зірочку (*). Знак питання відповідає будь-якому окремому символу, а зірочка – будь-якій послідовності символів. Якщо потрібно знайти справжній знак питання або зірочку, перед відповідним символом введіть тильду (~).

Наприклад, =VLOOKUP("Fontan?",B2:E7,2,FALSE) шукатиме всі екземпляри прізвища Самойленко, у яких остання буква може відрізнятися.

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

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

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

Відповіді на запитання щодо конкретних функцій

Опублікуйте запитання на форумі спільноти Microsoft Excel

Допомога в удосконаленні програми Excel

Маєте пропозиції щодо того, як покращити наступну версію програми Excel? Тоді перегляньте розділи на веб-сайті Excel User Voice.

Пов’язані теми

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

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

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

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

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×