Функція VLOOKUP належить до трійки найпопулярніших в Excel — після SUM і AVERAGE. Її завдання — знайти потрібне значення в таблиці даних і вивести його в задану клітинку.
VLOOKUP шукає значення по вертикалі, тобто серед рядків (на це вказує перша літера V — Vertical). Крім того, в Excel є функція HLOOKUP, яка робить горизонтальний пошук по стовпцях. Але нашому оку зрозуміліше, коли дані представлені кількома великими стовпцями, а основна інформація записана в рядках, тому VLOOKUP — більш популярна. Розглянемо цю функцію детальніше.
*У статті використовуються формули з англійської версії MS Excel.
Навіщо потрібна функція VLOOKUP
Синтаксис функції VLOOKUP однаковий як у Excel, так і в Google Spreadsheets. Він виглядає так:
= VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])
= VLOOKUP (шукане значення, масив таблиці, номер стовпця, [інтервальний перегляд]), де
- шукане значення (що шукаємо?) — шукане значення або посилання на клітинку з шуканим значенням
- масив таблиці (де шукаємо?) — посилання на діапазон клітинок, в першому стовпці якого буде пошук шуканого значення
- номер стовпця — номер стовпця в діапазоні, з якого буде повернено значення
- [інтервальний перегляд] — не обов'язковий, але важливий параметр, який відповідає на запитання «чи відсортовано за зростанням перший стовпчик діапазону пошуку?». Якщо відсортовано — ми вказуємо значення TRUE чи 1, якщо ні — FALSE чи 0. Якщо цей параметр опущено — він за замовчуванням дорівнює 1.
Розглянемо приклад. Ми маємо таблицю з трьома стовпчиками — артикул, найменування товару та його вартість.
Припустимо, нам потрібно знайти вартість товару з артикулом 1101. На скрині інтуїтивно зрозуміло, що вона дорівнює 58, але коли в таблиці тисячі рядків — пошук ускладнюється. З функцією VLOOKUP розібратися буде легше.
Щоби знайти вартість артикула 1101 — у клітинці, куди необхідно вивести значення, прописуємо формулу
= VLOOKUP
- <що шукаємо?> — шукане значення може бути прописане як посилання на клітинку (у нашому випадку клітинка F2) або як числове значення 1101 (якщо потрібно знайти текстове значення — його виділяємо лапками “”)
- <де шукаємо?> — прописуємо діапазон таблиці: може бути записаний як A2:C7 або як іменований діапазон
- <номер стовпця> — у нашому випадку вартість товару прописана в третьому за рахунком стовпці
*Результат формули, записаної трьома різними способами, буде однаковим.
У цьому прикладі 4-й параметр — [інтервальний перегляд] — пропущений, і функція за замовчуванням вирішила, що наша таблиця відсортована за зростанням номера артикула. У прикладі вище це виявилося правдою — тому ми отримали значення, на яке очікували. Але далі розглянемо приклад, де подібне не спрацювало б.
Як улаштована логіка VLOOKUP: функція почне пошук шуканого значення з першого рядка і продовжуватиме його порядково. Вона завершить пошук, одразу як знайде значення більше, ніж ми запитували, — і поверне попереднє.
Наприклад, нам потрібно знайти вартість артикула 1104 (на скрині нижче він розташований на 7-му рядку). Функція почне шукати порядково, знайде артикул 1105 (тобто більший, ніж шуканий) і завершить пошук, повернувши значення попереднього артикула — 1103.
Тобто ми вказали, що стовпець відсортований — отже, артикул 1104 не може бути розташований нижче, ніж 1105.
Ще один важливий момент: якщо потрібно знайти вартість, наприклад, артикула 1108, то функція поверне останній рядок діапазону — не вказавши, що артикула 1108 у цій таблиці взагалі не існує.
Якщо ви відзначите, що 4-й параметр не обов'язковий, це прискорить роботу. Коли таблиця містить тисячі рядків, відсортованих за зростанням, для функції немає сенсу проходити по всьому набору даних. Їй достатньо зупинитися на першому знайденому значенні. Тому, якщо ви впевнені в коректному сортуванні своїх даних, то 4-й параметр можна пропустити, якщо ні — вкажіть FALSE або 0.
Сортування за зростанням стосується не тільки числових, але і текстових виразів — функція може здійснювати пошук в алфавітному порядку.
Крім того, VLOOKUP допускає неточний пошук значення — ви можете опустити якийсь із символів, якщо він невідомий. Для цього використовуйте символи підстановки:
- ? — замінює один символ у рядку
- * — замінює будь-яку кількість символів
Наприклад, потрібно знайти продукт, в останній літері якого ми не впевнені. Для цього підставимо знак «?» — «BA?» — і функція поверне нам вартість продукту BAC:
Якщо ж потрібно знайти саме знак питання або зірочку — введіть значок тильди (~) перед шуканим символом.
Крім того, функцію VLOOKUP можна використовувати для об'єднання двох таблиць, які мають один спільний ключ — у нашому випадку це артикул.
Синтаксис функції залишається таким: шукаємо артикул з першої таблиці в заданому діапазоні іншої таблиці. При цьому вказуємо, у якому стовпці розташоване значення, яке потрібно відобразити у першій таблиці.
На скриншоті вище артикул 1106 повернувся з помилкою #N/A — це означає, що пошук за номером не дав результату, оскільки артикула з таким номером немає у другій таблиці. Щоб не виводити в клітинки значення N/A, можна доопрацювати формулу функцією IFERROR:
Недоліки функції VLOOKUP
Головний мінус у тому, що пошук шуканого значення може проходити лише по першому стовпцю заданого діапазону, а повернути потрібне значення функція може лише зі стовпців праворуч.
У прикладах вище цей недолік не проявлявся, оскільки шукане значення (артикул) було в першому стовпці діапазону, а інші значення розташовані праворуч від нього. Але якщо стовпець з потрібними значеннями розташований у середині таблиці, то вивести значення ми можемо тільки зі стовпців правіше. Наприклад:
У такому разі ми зможемо вивести лише вартість найменування, оскільки цей стовпець (С) розташований праворуч від стовпця з назвою (B). Код (A) розташований ліворуч, тому функція VLOOKUP у такому разі безсила.
Другий недолік VLOOKUP полягає в тому, що функція перестає працювати, якщо прибрати або додати стовпець до таблиці пошуку. Вставлений або видалений елемент змінить результат формули, оскільки синтаксис функції вимагає вказувати весь діапазон та конкретний номер стовпця, з якого потрібно вилучити дані.
Крім того, функція VLOOKUP має обмеження за довжиною шуканого значення у 255 символів, інакше буде повернена помилка #VALUE!
Які є аналоги VLOOKUP
Щоб обійти обмеження пошуку в першому стовпці діапазону, в Excel з'явилася функція XLOOKUP. Вона доступна лише для користувачів Microsoft 365 та Excel 2021.
Синтаксис функції виглядає так:
=XLOOKUP (lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
=XLOOKUP (шукане значення; масив, який потрібно переглянути; масив, який потрібно повернути, [якщо не знайдено]; [тип зіставлення]; [режим пошуку]), де
- шукане значення (що шукаємо?) — шукане значення або посилання на клітинку, що містить шукане значення
- масив, який потрібно переглянути (де шукаємо?) — масив або діапазон, в якому ви шукаєте потрібне значення
- масив, який потрібно повернути— масив або діапазон, з якого ви хочете отримати значення
- [якщо не знайдено] (не обов'язково) — значення, що повертається, якщо збіг не знайдено
- [тип зіставлення] (не обов'язково) — як зіставляти шукане значення зі значеннями в шуканому масиві:
- 0 (за замовчуванням) = точний збіг. Якщо збігів не знайдено, повернути #N/A
- -1 = точний збіг. Якщо збігів не знайдено, повернути наступне менше значення
- 1 = точний збіг. Якщо збігів не знайдено, повернути наступне більше значення
- 2 = частковий збіг
- [режим пошуку] (не обов'язково) — порядок пошуку:
- 1 (за замовчуванням) = шукати значення від першого до останнього елемента в шуканому масиві
- -1 = шукати значення від останнього до першого елемента
Таким чином, щоб отримати значення (артикул), яке знаходиться ліворуч від стовпця з потрібними значеннями, можна використовувати таку функцію:
В Excel попередніх версій цю задачу можна розв'язати, використовуючи комбінацію із функцій INDEX+MATCH. Синтаксис виглядає так:
= INDEX (column return a value from, MATCH (lookup value, column to look up against, [match_mode]) [column number])
= INDEX (масив, який потрібно повернути, MATCH (шукане значення, масив, який потрібно переглянути [тип зіставлення]), [номер стовпця]), де
- масив, який потрібно повернути — стовпець, з якого потрібно витягти дані
- шукане значення (що шукаємо?)
- масив, який потрібно переглянути (де шукаємо?)
- [тип зіставлення] (не обов'язково) — для точного збігу слід вказати 0
- [номер стовпця] (не обов'язково)
Функція поверне такі самі значення, як і XLOOKUP:
Таким чином, зв'язка функцій INDEX та MATCH фактично повністю замінює функцію VLOOKUP. Але водночас така комбінація не має недоліків VLOOKUP, які випливають із синтаксису самої функції, і цей варіант доступний для користувачів ранніх версій Excel.
Бажаєте отримувати дайджест статей?
