Функція VLOOKUP у Google Таблицях часто викликає непорозуміння, хоча є надзвичайно корисною. Вона дозволяє знаходити та поєднувати дані з двох різних наборів, використовуючи спільне значення для пошуку. Ось покрокова інструкція, як її ефективно застосовувати.
На відміну від Microsoft Excel, Google Таблиці не мають вбудованого майстра VLOOKUP. Це означає, що вам потрібно буде самостійно вводити формулу, але не варто цього боятися, це досить просто.
Принцип роботи VLOOKUP в Google Таблицях
На перший погляд VLOOKUP може здаватися складною функцією, але насправді її логіка досить зрозуміла. Формула VLOOKUP складається з чотирьох основних аргументів.
Першим аргументом є *ключове значення пошуку*, тобто значення, яке ви шукаєте. Другий аргумент – це *діапазон комірок*, де відбувається пошук (наприклад, від A1 до D10). Третій аргумент – це *номер стовпця* з діапазону, з якого ви хочете отримати результат. Перший стовпець у вибраному діапазоні має номер 1, другий – 2, і так далі.
Останній, четвертий аргумент, визначає, чи є *стовпець для пошуку відсортованим*. Це важливо тільки, якщо ви шукаєте наближене значення.
Четвертий аргумент має значення, коли вам потрібна найближча відповідність. Якщо ж ви шукаєте точну відповідність, тоді встановіть його значення як FALSE.
Для прикладу розглянемо ситуацію, коли в компанії є два аркуші. На першому міститься список продуктів з ідентифікаторами та цінами, а на другому — перелік замовлень.
Використовуючи ідентифікаційний номер як ключ пошуку, ви зможете швидко знайти ціну кожного товару у списку замовлень.
Важливо пам’ятати, що VLOOKUP не може шукати дані, розташовані ліворуч від стовпця з ключем пошуку. У більшості випадків вам потрібно або ігнорувати ці дані, або розмістити стовпець з ключем пошуку на початку діапазону.
VLOOKUP в межах одного аркуша
Розглянемо приклад, де у вас є дві таблиці на одному аркуші. У першій таблиці міститься список співробітників з їхніми ідентифікаційними номерами та датами народження.
У другій таблиці ви можете використовувати VLOOKUP для отримання даних із першої таблиці, використовуючи будь-який з критеріїв (ім’я, ідентифікаційний номер або день народження). У нашому прикладі, ми використаємо VLOOKUP для отримання дня народження за ідентифікаційним номером.
Відповідна формула VLOOKUP виглядає так: =VLOOKUP(F4, A3:D9, 4, FALSE).
Проаналізуємо цю формулу. VLOOKUP використовує значення з комірки F4 (123) як ключ пошуку, шукаючи його в діапазоні від A3 до D9. Функція повертає дані зі стовпця номер 4 (стовпець D, “День народження”) і, оскільки ми шукаємо точну відповідність, останній аргумент встановлено в FALSE.
Отже, для ідентифікаційного номера 123, VLOOKUP повертає дату народження 19/12/1971 (у форматі ДД/ММ/РР). Розширимо цей приклад, додавши стовпець із прізвищами до таблиці A, щоб пов’язати дати народження з конкретними людьми.
Формула трохи зміниться. Тепер, у комірці H4, =VLOOKUP(F4, A3:D9, 3, FALSE) шукатиме прізвище, яке відповідає ідентифікаційному номеру 123.
Замість дати народження, функція повертає дані зі стовпця номер 3 (“Прізвище”), яке відповідає значенню ідентифікатора зі стовпця номер 1 (“Ідентифікатор”).
VLOOKUP на різних аркушах
У попередньому прикладі ми працювали з даними на одному аркуші. Але VLOOKUP можна використовувати для пошуку даних на різних аркушах в одній таблиці. Для прикладу, інформація з таблиці А тепер знаходиться на аркуші “Співробітники”, а таблиця B — на аркуші “Дні народження”.
Замість звичного діапазону комірок A3:D9, ви можете ввести: =VLOOKUP(A4, Employees!A3:D9, 4, FALSE).
Додавши назву аркуша перед діапазоном комірок (Employees!A3:D9), формула VLOOKUP зможе використовувати дані з іншого аркуша.
Використання підстановочних символів з VLOOKUP
В попередніх прикладах ми використовували точні ключові значення для пошуку даних. Але VLOOKUP також підтримує підстановочні символи, такі як знак питання (?) або зірочка (*).
Знову скористаємося набором даних з попередніх прикладів, але перемістимо стовпець “Ім’я” у стовпець А. Таким чином, ми зможемо використовувати часткове ім’я та символ зірочки, для пошуку прізвища співробітника.
Формула VLOOKUP для пошуку прізвища за частковим ім’ям виглядає так: =VLOOKUP(B12, A3:D9, 2, FALSE), де значення вашого ключа пошуку міститься у комірці B12.
У наступному прикладі “Chr*” у комірці B12 відповідає прізвищу “Geek” у таблиці.
Пошук найближчої відповідності з VLOOKUP
Останній аргумент функції VLOOKUP дозволяє шукати як точні, так і наближені значення. Раніше ми встановлювали його в FALSE, щоб знайти точну відповідність.
Якщо потрібно знайти найближче значення, встановіть останній аргумент у TRUE. Важливо, щоб стовпець, у якому ви шукаєте, був відсортований за зростанням (від А до Я), інакше функція може працювати некоректно.
В таблиці нижче представлений список товарів для покупки (A3–B9) разом з їх назвами та цінами. Список відсортований за ціною від найнижчої до найвищої. Наш бюджет на один товар становить 17 доларів (комірка D4). Ми використовуємо VLOOKUP для пошуку найдешевшого товару, який можна придбати.
Формула VLOOKUP виглядає так: =VLOOKUP(D4, A4:B9, 2, TRUE). Оскільки функція налаштована на пошук найближчого значення, що не перевищує ключове, вона буде шукати товари, дешевші за 17 доларів.
У нашому випадку, найдешевшим товаром, який коштує менше 17 доларів, є сумка вартістю 15 доларів. Саме її формула VLOOKUP поверне як результат у комірці D5.
