Все, що потрібно знати про VLOOKUP — коли її використовувати, коли перейти на XLOOKUP і дюжина помилок, що призводять до помилок.
- VLOOKUP шукає значення в першому стовпці діапазону і повертає значення з іншого стовпця того ж рядка.
- Синтаксис:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])— і ви завжди повинні передавати FALSE як четвертий аргумент. - VLOOKUP шукає лише зліва направо і ламається при реорганізації стовпців. Для Excel 2021+ XLOOKUP виправляє обидві проблеми.
- Найпоширеніша помилка — забути FALSE: без нього VLOOKUP виконує приблизне співпадіння і тихо повертає неправильні відповіді.
VLOOKUP — найпоширеніша формула Excel, і мабуть найчастіше неправильно використовувана. Майже кожен Excel-файл у корпоративному середовищі містить хоча б один VLOOKUP, і хоча б один з них тихо повертає неправильну відповідь, бо хтось забув четвертий аргумент.
Що насправді робить VLOOKUP
VLOOKUP — Vertical Lookup (Вертикальний пошук) — шукає перший стовпець вказаного вами діапазону, знаходить рядок, що відповідає вашому значенню, і повертає значення з обраного вами стовпця цього рядка. Він з'явився в Excel у 1985 і з тих пір є стандартною функцією пошуку.
Синтаксис простою мовою
Аргументи VLOOKUP
- lookup_value — що шукаєте (посилання на клітинку, як A2, або текст, як "Іван")
- table_array — діапазон для пошуку. Стовпець пошуку ОБОВ'ЯЗКОВО має бути першим стовпцем цього діапазону.
- col_index_num — який стовпець діапазону повертати. 1 — перший стовпець, 2 — другий і так далі.
- range_lookup — FALSE для точного збігу, TRUE для приблизного. Завжди передавайте FALSE, якщо конкретно не потрібне приблизне збіг.
Базові приклади
Імена у стовпці A, зарплати у стовпці C — знайти зарплату Івана:
=VLOOKUP("Іван", A:C, 3, FALSE)
Пошук проти посилання на клітинку — набагато поширеніше в реальних таблицях:
=VLOOKUP(A2, Аркуш2!A:E, 5, FALSE)
Копіювання формули вниз стовпця з абсолютними посиланнями на таблицю пошуку:
=VLOOKUP(A2, $D$2:$F$100, 3, FALSE)
Критичні поради, що економлять години
Завжди робіть ці три речі
- Завжди передавайте FALSE як четвертий аргумент. Стандарт TRUE виконує приблизне збіг, що повертає неправильні відповіді без будь-якої помилки.
- Використовуйте абсолютні посилання ($) для table_array при копіюванні формул. Без знаків долара діапазон зміщується при копіюванні вниз.
- Загортайте в IFERROR для чистого виводу.
=IFERROR(VLOOKUP(...), "Не знайдено")перетворює некрасиві помилки #N/A на читабельний текст.
Поширені помилки та їх виправлення
| Помилка | Що означає | Як виправити |
|---|---|---|
| #N/A | Значення пошуку не знайдено у першому стовпці | Перевірте написання, кінцеві пробіли (використовуйте TRIM), типи даних |
| #REF! | col_index_num більший за кількість стовпців у table_array | Перерахуйте стовпці; пам'ятайте, col_index_num починається з 1 |
| #VALUE! | Неправильний тип аргументу | Перевірте аргументи — найчастіша причина — відсутній обов'язковий аргумент |
| Неправильний результат (без помилки) | Ви забули FALSE, тому VLOOKUP зробив приблизне збіг | Додайте FALSE як четвертий аргумент |
VLOOKUP vs XLOOKUP vs INDEX/MATCH
| Функція | Найкраще для | Версії Excel |
|---|---|---|
| XLOOKUP | Нові формули у сучасному Excel | 365, 2021+ |
| INDEX/MATCH | Старий Excel, максимальна гнучкість | Всі версії |
| VLOOKUP | Зворотна сумісність, прості випадки | Всі версії |
FAQ
VLOOKUP чи XLOOKUP?
Якщо у вас Excel 365 або 2021+ — XLOOKUP. Він швидший, гнучкіший і має безпечніші стандарти. Використовуйте VLOOKUP лише при обміні файлами з користувачами старих версій Excel.
Що робить четвертий аргумент?
Четвертий аргумент (range_lookup) вказує VLOOKUP, чи робити точне або приблизне збіг. FALSE означає точне збіг — знайдіть значення або поверніть #N/A. TRUE (стандарт) означає приблизне збіг. Завжди передавайте FALSE, якщо конкретно не потрібне приблизне збіг.
Чому мій VLOOKUP повертає #N/A?
Значення пошуку не знайдено в першому стовпці table_array. Перевірте три речі: написання (VLOOKUP нечутливий до регістру, але опечатки ламають), кінцеві пробіли (використовуйте TRIM для очищення обох сторін), тип даних ("100" як текст не відповідає числу 100).
Чи може VLOOKUP шукати вліво?
Ні. VLOOKUP шукає лише перший стовпець table_array і повертає зі стовпця праворуч. Для пошуку вліво використовуйте XLOOKUP (Excel 365+) або INDEX/MATCH (всі версії).
Підсумок
VLOOKUP — найпоширеніша формула Excel з причини — вирішує найтиповіше завдання в одному рядку. Підводний камінь у тому, що поведінка за замовчуванням небезпечна, а індекс стовпця крихкий. Завжди передавайте FALSE. Завжди використовуйте абсолютні посилання при копіюванні. Завжди загортайте в IFERROR для чистого виводу. І якщо ви в Excel 365 — переходьте на XLOOKUP для будь-якої нової формули.
- Завжди передавайте FALSE як четвертий аргумент — інакше тихі неправильні результати.
- Використовуйте абсолютні посилання ($) при копіюванні формул.
- Загортайте в IFERROR для обробки відсутніх збігів.
- VLOOKUP шукає лише зліва направо — реструктуруйте або використовуйте XLOOKUP для інших напрямків.
- Якщо ви в Excel 365, XLOOKUP швидший, безпечніший і гнучкіший.
Одне посилання для контенту та навчальних матеріалів Excel
Додайте URL UniLink до свого біо — показуйте навчальні матеріали, курси, блог. Безкоштовно.