Як виконати функцію VLOOKUP в Excel за допомогою розширених прикладів та хитрощів

Останнє оновлення: 20/06/2025
Автор: Ісаак
  • VLOOKUP дозволяє ефективно пов'язувати дані між таблицями.
  • Ключем до його роботи є правильна структура формули та чисті дані.
  • Використання абсолютних посилань та точного зіставлення дозволяє уникнути більшості помилок.

Як виконати функцію VLOOKUP в Excel з прикладами

Ви коли-небудь стикалися Переміщуєтеся між рядками та стовпцями в Excel, намагаючись об'єднати дані з різних таблиць, і не знаєте, як пришвидшити цей процес? Якщо ви працюєте зі складними електронними таблицями, ви знаєте, як нудно буває вручну шукати та пов’язувати інформацію. На щастя, існує фундаментальна функція, яка вирішує це завдання та заощаджує вам величезну кількість часу: функція VLOOKUP.

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

Що таке функція VLOOKUP в Excel?

VLOOKUP, який іспанською називається VLOOKUP, є функцією Microsoft Excel і Google Таблиці, що дозволяє шукати значення в першому стовпці таблиці та повертати пов’язані дані з іншого стовпця в тому ж рядку. Термін «вертикальний пошук» стосується пошуку вниз по стовпцю.

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

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

Як працює формула VLOOKUP?

Віртуальний пошук Excel

Формула VLOOKUP структурована наступним чином:

=BUSCARV(valor_búsqueda; intervalo; índice_columna; )

Кожен елемент функції має певну функцію:

  • значення_пошукуЦе дані, які потрібно знайти, зазвичай це клітинка, яка містить унікальний ключ, наприклад, код продукту або ім'я особи.
  • інтервалЦе діапазон комірок, де розташовані дані. Стовпець, у якому знаходяться дані. значення_пошуку ЗАВЖДИ має бути першим стовпцем у цьому діапазоні.
  • індекс_колонки: Це номер стовпця в діапазоні, з якого потрібно повернути результат. Пам’ятайте, що стовпець пошуку має номер 1.
  • : Це необов'язково. Вкажіть, чи хочете ви точний збіг (ФЕЙК або 0) або приблизний (ПРАВДА або 1). За замовчуванням це приблизний збіг, але на практиці майже завжди потрібно використовувати точний збіг.

Простий приклад знаходження ціни фрукта в таблиці буде таким:

=BUSCARV("Manzana"; A2:C10; 3; FALSO)

За допомогою цієї формули Excel шукатиме «Apple» у першому стовпці діапазону A2:C10. Якщо знайде, поверне значення з третього стовпця цього рядка (наприклад, ціну).

  Повний вступ до Rust: Практичний посібник для початківців із прикладами

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

Для чого використовується VLOOKUP? Практичні приклади

Функція VLOOKUP надзвичайно корисна, коли ви керуєте великими обсягами даних і вам потрібно зіставляти інформацію між різними таблицями. Ось кілька прикладів типового використання:

  • Дані про співробітників, що працюють у сфері реляцій: У вас є список змін та ще один список імен та посад. Функція VLOOKUP допомагає автоматично заповнити таблицю змін, використовуючи номер співробітника як ключ.
  • Зіставте запаси з цінами: Зі списку товарів, що є в наявності, ви можете додати ціну кожного з них, знайшовши його в таблиці цін.
  • Автоматичне оновлення даних: Щоразу, коли інформація в довідковій таблиці змінюється (наприклад, постачальники), дані, які ви вводите за допомогою функції VLOOKUP, автоматично оновлюватимуться.
  • Швидко та автоматично шукайте інформацію про студентів, книги, клієнтів, продукти тощо.

VLOOKUP — ваш найкращий союзник, щоб зупинити копіювання та вставку й автоматизувати повторювані процеси в Excel, що значно підвищить вашу продуктивність.

Крок за кроком, щоб створити формулу VLOOKUP

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

  • Замовлення інгредієнтів: Список того, що купити.
  • Список постачальників: Вкажіть ім'я постачальника, номер телефону, дату доставки та іншу інформацію, що стосується кожного інгредієнта.

Ми хочемо додати три стовпці до списку замовлень: ім'я постачальника, номер телефону та дату доставки. Щоб це зробити:

  1. На аркуші «Замовлення інгредієнтів» перейдіть до клітинки, де потрібно вказати назву постачальника.
  2. Натисніть «=», щоб почати вводити формулу.
  3. запис VLOOKUP( o VLOOKUP ( якщо ваш Excel англійською мовою.
  4. Виберіть клітинку з назвою інгредієнта, який потрібно знайти (наприклад, B5).
  5. Введіть кому та виберіть діапазон, де знаходяться дані постачальника (наприклад, таблицю на аркуші Список постачальників, від A3 до G13).
  6. Натисніть F4, щоб зробити діапазон абсолютним посиланням (з'являться знаки $).
  7. Поставте кому, вкажіть номер стовпця, який містить дані, які ви хочете вивести (наприклад, назва постачальника знаходиться у стовпці 2, номер телефону у стовпці 7, дата доставки у стовпці 5...)
  8. Зрештою, напишіть ФЕЙК щоб шукати лише точні збіги та закрити дужку.

Ваша остаточна формула для назви постачальника може виглядати так: =BUSCARV(B5,'Lista de Proveedores'!$A$3:$G$13,2,FALSO)

Для телефону просто змініть номер стовпця (наприклад, 7), а для дня доставки введіть відповідний індекс.

Невелика хитрість: якщо ви копіюєте та вставляєте формулу нижче, переконайтеся, що посилання на таблицю пошуку заблоковано, щоб уникнути помилок (саме тому ми використовуємо F4 та символ $).

Ключові відомості про синтаксис та аргументи VLOOKUP

Давайте розберемо кожну частину аргументу, щоб не помилитися та повністю зрозуміти, що ми представляємо:

  • Значення для пошуку: Це може бути текст, число, посилання на іншу комірку… Важливо, щоб це було унікальний у першому стовпці діапазону. Приклад: «102» або B5.
  • Діапазон пошуку: Включіть стовпець із даними, які потрібно знайти, та всі стовпці, з яких потрібно витягти інформацію. Приклад: A2:D10.
  • Індекс стовпця: Це ціле число, і відлік завжди починається з крайнього лівого стовпця діапазону (1 = стовпець пошуку, 2 = наступний тощо). Не може бути менше за 1 або більше за кількість стовпців у діапазоні.
  • Точна або приблизна відповідність: ЗАВЖДИ рекомендується встановлювати значення FALSE, щоб уникнути несподіванок. Використовуйте значення TRUE, лише якщо ваш стовпець пошуку відсортовано, і ви шукаєте діапазони.
  Прості способи швидше оцінити вартість iPhone та iPad

А в Google Таблицях? Все вищезазначене застосовне на 100%, хоча в Таблицях аргументи іноді мають незначні варіації, такі як відсортовано.

Дуже корисні приклади VLOOKUP

Ось кілька прикладів для різних сценаріїв:

  • Текстовий пошук: =BUSCARV("Manzana";B4:D8;3;FALSO) → Повертає ціну яблука
  • Пошук за посиланням на клітинку:=BUSCARV(G9;B4:D8;3;FALSO) → Знайдіть значення G9 у списку
  • Пошук за приблизним збігом: =BUSCARV(102;A4:D8;2;VERDADERO) → Якщо значення 102 не існує, система повертає найближче значення, менше ніж 102
  • Зі змінним індексом стовпця: =BUSCARV(G3;B4:D8;2;FALSO) → Знайдіть величину відповідно до значення G3
  • Об'єднання критеріїв (у Google Таблицях): Якщо вам потрібно шукати за іменем та прізвищем, ви можете створити допоміжний стовпець, який пов’язує їх та використовувати його як унікальний ключ.

Якщо у вас є кілька рядків, які можуть відповідати вашому пошуку, VLOOKUP завжди повертатиме ПЕРШИЙ знайдений збіг.

Поширені помилки та способи їх усунення

Найпоширеніша помилка VLOOKUP — #N/A, що означає, що значення пошуку не існує в першому стовпці діапазону. Давайте розглянемо найпоширеніші причини та способи їх виправлення:

  • Дублікати даних: Якщо у вас є кілька записів з однаковим ключем, відображатиметься лише перший. Видаліть дублікати зі стовпця пошуку, щоб уникнути плутанини.
  • Пробіли на початку/в кінці: Якщо перед або після даних є невидимі пробіли, Excel не поверне збіг. Використовуйте функцію SPACES, щоб очистити дані.
  • Неправильне посилання на таблицю: Якщо копіювання формули зміщує діапазон, пошук не вдасться. Рішення: Використовуйте абсолютні посилання (з символом $).
  • Індекс стовпця поза діапазоном: Якщо ввести число, більше за кількість вибраних стовпців, з’явиться помилка #REF!.
  • Неправильний порядок: Якщо ви використовуєте приблизне зіставлення без сортування стовпця пошуку від найнижчого до найвищого, ви можете отримати помилкові результати. Завжди вказуйте значення FALSE, якщо ви не впевнені в тому, що робите.

Ви можете налаштувати помилку #N/A, поєднавши функцію VLOOKUP з IFNA:

  • =SI.ERROR(BUSCARV(...), "No encontrado") в Excel
  • =SI.ND(BUSCARV(...), "No encontrado") у Google Таблицях

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

Розширені поради та підказки для опанування VLOOKUP

1. Використовуйте абсолютні посилання в діапазоні
Щоразу, коли ви копіюєте формули, переконайтеся, що діапазон пошуку не змінюється. Тому, після вибору діапазону, натисніть F4, щоб ввести знак $. Це гарантує, що Excel/Sheets не змінять його під час копіювання формули.

2. Завжди сортуйте стовпець пошуку, якщо використовуєте приблизне збігання
Якщо вам дійсно потрібно шукати за діапазонами (наприклад, розрахувати комісію за діапазоном), відсортуйте стовпець, де VLOOKUP шукає, від найменшого до найбільшого.

3. Працюйте з чистими даними
Перед використанням функції видаліть усі пробіли та переконайтеся, що числа або дати не збережені як текст. Це запобіжить неочікуваним результатам.

  Виправлення: Помилка драйвера гаджета в головній версії Windows 10

4. VLOOKUP дивиться лише праворуч
Якщо вам потрібно знайти значення ліворуч, вам потрібно буде змінити порядок стовпців або скористатися такими функціями, як INDEX та MATCH, або перейти до XLOOKUP, яка доступна в новіших версіях Excel.

5. Використовуйте підстановочні символи для часткових збігів
Якщо ви хочете знайти імена, які починаються з однакового імені, але не знаєте, як вони закінчуються, ви можете використовувати зірочки (*) або знаки питання (?) у своєму значенні пошуку за допомогою функції VLOOKUP, завжди використовуючи точну відповідність (FALSE). Приклад: BUSCARV("La*";...; FALSO) поверне перші дані, що починаються на "La".

6. VLOOKUP на різних аркушах або книгах
Ви можете шукати в таблицях, розташованих на іншій вкладці (аркуші) або навіть в іншому файлі Excel. Просто вкажіть назву аркуша та діапазон ось так: 'Hoja2'!A1:F20Для інших книг спочатку відкрийте їх і виберіть діапазон; Excel автоматично додасть шлях.

VLOOKUP у Google Таблицях: подібності та відмінності

Якщо ви користувач Google Таблиць, функція VLOOKUP працює майже так само, як і в Excel, хоча є незначні зміни в назвах аргументів.:

  • значення_пошуку: що ви хочете шукати.
  • інтервал: діапазон для пошуку та виведення результату.
  • індекс: стовпець, у який потрібно внести дані, в межах інтервалу (1 – це перший стовпець вибраного діапазону).
  • відсортовано: чи ви шукаєте точний збіг (ХИБНІСТЬ), чи приблизний збіг (ІСТИНА).

Крім того, Google Таблиці містять функцію SI.ND() персоналізувати повідомлення, коли шукане значення не знайдено, та SI.ERROR() для інших загальних помилок.

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

Обмеження та альтернативи функції VLOOKUP

Хоча VLOOKUP дуже потужний, він має деякі обмеження:

  • Ви не можете шукати ліворуч від стовпця пошуку.
  • Повертає лише перше відповідне значення.
  • Це може стати повільним з великими обсягами даних.
  • Це не дозволяє шукати значення у порядку спадання, якщо ваш діапазон відсортовано інакше.

У поточних версіях Excel ви можете використовувати ПОШУК X (XLOOKUP), який вирішує багато з цих проблем: дозволяє шукати як ліворуч, так і праворуч, знаходить результати в будь-якому стовпці та є більш гнучким.

Найкращі практики роботи з VLOOKUP

  • Використовуйте унікальні ключі: Якщо ваш стовпець підстановки містить дублікати, очистіть дані перед застосуванням функції VLOOKUP.
  • Зберігати діапазон відсортованим лише за умови приблизного збігуЦе не обов'язково для точного збігу, але ніколи не завадить мати достовірні дані.
  • Заблокуйте посилання на діапазон за допомогою $ перед копіюванням формули в інші комірки, це запобіжить помилкам та небажаним зсувам.
  • Переконайтеся, що дати та числа відформатовані правильно (не як текст).
  • Використовуйте IF.ERROR, IF.ND або IFNA щоб налаштувати повідомлення про помилки, особливо якщо ви надаєте доступ до аркуша більшій кількості користувачів.
пошук в excel
Пов'язана стаття:
Як використовувати VLOOKUP та XLOOKUP в Excel: повний посібник та практичні приклади