У цій статті описано найпоширеніші причини VLOOKUP для помилкового результату функції та наведено пропозиції щодо використання функцій INDEX і MATCH .
Порада.: Крім того, див. поради з виправлення неполадок із функцією VLOOKUP , які містять загальні причини #NA проблем у зручному файлі PDF. Ви можете надати спільний доступ до файлу PDF іншим користувачам або надрукувати його для власного посилання.
Проблема: значення підстановки міститься не в першому стовпці аргументу масив_таблиці
Одне з обмежень функції VLOOKUP полягає в тому, що вона може шукати значення лише в лівому стовпці масиву таблиці. Якщо значення підстановки міститься не в першому стовпці масиву, відобразиться помилка #N/A.
У таблиці нижче ми хочемо отримати кількість проданих одиниць капусти.
Помилка #N/A призводить до того, що в другому стовпці (Овочі) аргументу table_array A2:C10 відображається значення підстановки "Капуста". У цьому випадку Excel шукає його в стовпці A, а не в стовпці B.
Вирішення. Спробуйте виправити це, налаштувавши функцію VLOOKUP, щоб створити посилання на правильний стовпець. Якщо це неможливо, спробуйте перемістити стовпці. Це також може бути дуже недоцільно, якщо у вас є великі або складні електронні таблиці, де значення клітинок є результатами інших обчислень, або, можливо, є й інші логічні причини, чому стовпці просто не можна переміщати. Рішення полягає у використанні комбінації функцій INDEX і MATCH, які можуть шукати значення в стовпці незалежно від його розташування в таблиці підстановки. Див. наступний розділ.
Радимо натомість використовувати функції INDEX/MATCH
Функції INDEX і MATCH – це чудовий варіант для багатьох випадків, коли функція VLOOKUP не відповідає вашим потребам. Основна перевага ФУНКЦІЇ INDEX або MATCH полягає в тому, що ви можете знайти значення в стовпці в будь-якому розташуванні в таблиці підстановки. Функція INDEX повертає значення з указаної таблиці або діапазону відповідно до його позиції. Функція MATCH повертає відносне положення значення в таблиці або діапазоні. Використовуйте функції INDEX і MATCH разом у формулі, щоб знайти значення в таблиці або масиві, указавши відносне положення значення в таблиці або масиві.
Функція INDEX або MATCH замість функції VLOOKUP має кілька переваг:
-
У програмах INDEX і MATCH повернуте значення не має бути в тому самому стовпці, що й стовпець підстановки. Це відрізняється від функції VLOOKUP, у якій повернуте значення має бути в указаному діапазоні. Чому це важливо? Для функції VLOOKUP потрібно знати номер стовпця, який містить повернуте значення. Хоча це може здатися непростою справою, вона може бути громіздкою, якщо у вас велика таблиця, і вам доведеться підрахувати кількість стовпців. Крім того, якщо додати або видалити стовпець у таблиці, потрібно перерахувати й оновити аргумент col_index_num . З функціями INDEX і MATCH перерахунок непотрібний, тому що стовпець підстановки відрізняється від стовпця, який містить повернуте значення.
-
За допомогою функцій INDEX і MATCH можна вказати рядок або стовпець у масиві або вказати обидва. Це означає, що ви можете шукати значення як по вертикалі, так і по горизонталі.
-
За допомогою функцій INDEX і MATCH можна шукати значення в будь-якому стовпці. На відміну від функції VLOOKUP, у якій можна шукати лише значення в першому стовпці таблиці, функції INDEX і MATCH працюватимуть, якщо шукане значення міститься в першому, останньому або будь-якому іншому стовпці.
-
Функції INDEX і MATCH дають змогу створити динамічне посилання на стовпець, який містить повернуте значення. Це означає, що ви можете додавати стовпці до таблиці, не порушуючи функції INDEX і MATCH. З іншого боку, функція VLOOKUP розривається, якщо потрібно додати стовпець до таблиці, оскільки він створює статичне посилання на таблицю.
-
Функції INDEX і MATCH забезпечують більшу гнучкість завдяки матчах. Функції INDEX і MATCH можуть знайти точний збіг або значення, більше або менше за шукане значення. Функція VLOOKUP шукатиме лише найближчий збіг зі значенням (за замовчуванням) або точним значенням. Крім того, функція VLOOKUP за замовчуванням припускає, що перший стовпець у масиві таблиці відсортовано за алфавітом, і припустімо, що таблицю не настроєно таким чином, функція VLOOKUP поверне перший найближчий збіг у таблиці, який може бути не тими даними, які ви шукаєте.
Синтаксис
Щоб побудувати синтаксис функції INDEX або MATCH, потрібно використати аргумент масив/посилання з функції INDEX і вкласти в нього синтаксис MATCH. Це має форму:
=INDEX(масив або посилання;MATCH(значення_підстановки;масив_який_переглядається;[тип_зіставлення])
Давайте використамо функції INDEX і MATCH, щоб замінити функцію VLOOKUP у наведеному вище прикладі. Синтаксис матиме такий вигляд:
=INDEX(C2:C10;MATCH(B13;B2:B10;0))
Простою українською мовою це означає:
=INDEX(повертає значення з C2:C10, яке буде MATCH(Капуста, яка знаходиться десь у масиві B2:B10, у якому повернуте значення – перше значення, яке відповідає капусті))
Формула шукає перше значення в діапазоні C2:C10, яке відповідає значенню Капуста (у B7) і повертає значення в клітинці C7 (100), тобто перше значення, яке збігається з "Капуста".
Проблема: не вдається знайти точний збіг
Якщо аргумент range_lookup має значення FALSE (хибність) і функція VLOOKUP не може знайти точний збіг у даних, вона повертає помилку #N/A.
Вирішення. Якщо ви впевнені, що в електронній таблиці є відповідні дані, а функція VLOOKUP не ловить їх, знайдіть час, щоб переконатися, що клітинки, на які посилаються, не містять прихованих пробілів або недрукованих символів. Крім того, переконайтеся, що клітинки відслідкує правильний тип даних. Наприклад, клітинки з числами слід форматувати як число, а не текст.
Крім того, радимо використовувати функцію CLEAN або TRIM для очищення даних у клітинках.
Проблема: значення підстановки менше найменшого значення в масиві
Якщо для аргументу range_lookup встановлено значення TRUE (істина), а значення підстановки менше за найменше значення в масиві, відобразиться помилка #N/A. У разі значення ІСТИНА здійснюється пошук приблизної відповідності в масиві та повертається найближче значення, менше за значення підстановки.
У наведеному нижче прикладі значення підстановки – 100, але в діапазоні B2:C10 немає значень, менших за 100, тому відображається повідомлення про помилку.
Вирішення.
-
За потреби виправте значення підстановки.
-
Якщо ви не можете змінити значення підстановки та потребуєте більшої гнучкості зі значеннями, що збігаються, радимо використовувати функції INDEX або MATCH замість функції VLOOKUP, див. розділ вище в цій статті. За допомогою функції INDEX або MATCH можна шукати значення, більші, менші або рівні значенню підстановки. Докладні відомості про використання функції INDEX або MATCH замість функції VLOOKUP див. в попередньому розділі цієї статті.
Проблема: стовпець підстановки не відсортовано за алфавітом
Якщо для аргументу range_lookup встановлено значення TRUE (істина), а один зі стовпців підстановки не відсортовано за зростанням (A–Z), відобразиться помилка #N/A.
Вирішення.
-
Змініть функцію VLOOKUP таким чином, щоб вона шукала точний збіг. Для цього установіть для аргументу точність_пошуку значення ХИБНІСТЬ. Для значення FALSE сортування не потрібне.
-
Використовуйте функцію INDEX або MATCH, щоб шукати значення в невідсортованій таблиці.
Проблема: значення є великим числом із рухомою комою
Якщо в клітинках є значення часу або великі десяткові числа, excel повертає помилку #N/A через точність рухомої коми. Числа з рухомою комою – це числа після десяткової коми. (Excel зберігає значення часу як числа з рухомою комою.) Excel не може зберігати числа з дуже великими рухомими точками, тому щоб функція працювала правильно, числа з рухомою комою потрібно округлити до 5 десяткових розрядів.
Вирішення. Скоротіть числа, округливши їх до п’яти десяткових розрядів за допомогою функції ROUND.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.
