Виправлення помилки #N/A у функції VLOOKUP

У цій статті описано найпоширеніші причини можливих проблем із функцією VLOOKUP.

Порада : Див. Короткий довідник: поради з виправлення неполадок із функцією VLOOKUP, у якому описано поширені причини виникнення помилок #N/A з функцією VLOOKUP у зручному файлі PDF. Ви можете поділитися файлом PDF з іншими людьми або надрукувати його для подальшого використання.

Проблема: значення підстановки міститься не в першому стовпці аргументу масив_таблиці

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

У таблиці нижче ми хочемо отримати кількість проданих одиниць капусти.

Помилка #NA у функції VLOOKUP: значення підстановки міститься не в першому стовпці масиву таблиці.

Повідомлення про помилку виникає, тому що значення підстановки "Капуста" міститься в другому стовпці ("Продукти") в аргументі масив_таблиці A2:C10, тож Excel шукає його в стовпці A, а не в стовпці B.

Вирішення. Ви можете спробувати виправити це, налаштувавши для функції VLOOKUP посилання на правильний стовпець. Якщо це неможливо, спробуйте перемістити стовпці. Це може бути надзвичайно незручно, якщо у вас великі або складні таблиці, де значеннями клітинок є результати інших обчислень. Також може бути неможливо перемістити стовпці з інших причин. Рішенням цієї проблеми є поєднання функцій INDEX і MATCH, за допомогою яких можна знайти значення в стовпці незалежно від його розташування в таблиці підстановки.

Використання функцій INDEX і MATCH замість VLOOKUP

Для задач, які не може вирішити за допомогою функції VLOOKUP, можна використовувати функції INDEX і MATCH. Найбільша перевага цих функцій полягає в тому, що вони дають змогу шукати значення в стовпці в будь-якому місці таблиці підстановки. Функція INDEX повертає значення з указаної таблиці або діапазону на основі його позиції, а MATCH повертає відносне положення значення в таблиці або діапазоні. Використовуючи функції INDEX і MATCH разом у формулі, можна шукати значення в таблиці або масиві за його відносною позицією.

Порівняно з VLOOKUP використання функцій INDEX і MATCH має кілька переваг:

  • Коли використовуються функції INDEX і MATCH, повернуте значення не обов’язково має бути в тому самому стовпці, що й стовпець підстановки, на відміну від функції VLOOKUP, де повернуте значення має бути у вказаному діапазоні. Чому це важливо? Для функції VLOOKUP потрібно знати номер стовпця, який містить повернуте значення. Вам може здатися, що це не так важливо, але у великій таблиці рахувати стовпці може бути непросто. Крім того, якщо ви збираєтеся додати або видалити аргумент у таблиці, необхідно перерахувати й оновити аргумент номер_стовпця. З функціями 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;яке ЗБІГАЄТЬСЯ_ЗІ_ЗНАЧЕННЯМ("Капуста"; що розташоване десь у масиві B2:B10; де повернуте значення – перше значення, яке збігається з "Капуста"))

Функції INDEX і MATCH можна використовувати замість функції VLOOKUP.

Формула шукає перше значення в діапазоні C2:C10, яке відповідає значенню Капуста (у B7) і повертає значення в клітинці C7 (100), тобто перше значення, яке збігається з "Капуста".

Проблема: не вдається знайти точний збіг

Коли аргумент точність_пошуку має значення ХИБНІСТЬ і функції VLOOKUP не вдається знайти точний збіг у ваших даних, вона повертає помилку #N/A.

Вирішення. Якщо ви впевнені, що відповідні дані наявні у вашій електронній таблиці, але функція VLOOKUP їх не знаходить, переконайтеся, що у відповідних клітинках немає прихованих пробілів або недруковних символів. Також перевірте, чи для клітинок указано правильний тип даних. Наприклад, для клітинок із числами має бути вказано формат "Числовий", а не "Текстовий".

Видаліть дані в клітинках за допомогою функції CLEAN або TRIM.

Проблема: значення підстановки менше найменшого значення в масиві

Якщо аргумент точність_пошуку має значення ІСТИНА та значення підстановки менше за найменше значення в масиві, відобразиться помилка #N/A. У разі значення ІСТИНА здійснюється пошук приблизної відповідності в масиві та повертається найближче значення, менше за значення підстановки.

У наведеному нижче прикладі значення підстановки – 100, але в діапазоні B2:C10 немає значень, менших за 100, тому відображається повідомлення про помилку.

Помилка N/A у функції VLOOKUP, яка виникає, якщо значення підстановки менше найменшого значення в масиві

Вирішення.

  • За потреби виправте значення підстановки.

  • Якщо ви не можете змінити значення підстановки та потребуєте більшої гнучкості із зіставленням значень, замість VLOOKUP ви можете використати функції INDEX і MATCH. INDEX і MATCH дають змогу шукати значення, більші чи менші за значення підстановки або рівні йому. Докладні відомості про використання функцій INDEX і MATCH замість VLOOKUP див. в попередньому розділі цієї статті.

Проблема: стовпець підстановки не відсортовано за алфавітом

Якщо аргумент точність_пошуку має значення ІСТИНА та один зі стовпців підстановки не відсортовано за зростанням (від А до Я), відобразиться помилка #N/A.

Вирішення.

  • Змініть функцію VLOOKUP таким чином, щоб вона шукала точний збіг. Для цього установіть для аргументу точність_пошуку значення ХИБНІСТЬ. Для значення ХИБНІСТЬ сортування не потрібне.

  • Використовуйте функцію INDEX або MATCH, щоб шукати значення в невідсортованій таблиці.

Проблема: значення є великим числом із рухомою комою

Якщо в клітинках є значення часу або великі десяткові числа, Excel повертає помилку #N/A через точність рухомої коми. Числа з рухомою комою – це числа, які йдуть після десяткової коми (зверніть увагу, що Excel зберігає значення часу як числа з рухомою комою). Excel не може зберігати числа з великою кількістю цифр, тому для правильної роботи функції необхідно округляти числа до 5 десяткових розрядів.

Вирішення. Скоротіть числа, округливши їх до п’яти десяткових розрядів за допомогою функції ROUND.

Відповіді на запитання щодо конкретних функцій

Опублікуйте запитання на форумі спільноти Microsoft Excel

Допомога в удосконаленні програми Excel

Маєте пропозиції щодо того, як покращити наступну версію програми Excel? Тоді перегляньте розділи на веб-сайті Excel User Voice.

Див. також

Виправлення помилки #N/A

VLOOKUP: більше жодних помилок #N/A

Функції HLOOKUP, VLOOKUP, LOOKUP повертають неправильні значення в Excel

Обчислення чисел із рухомою комою може давати неправильні результати в Excel

Короткий довідник: пам’ятка щодо функції VLOOKUP

Функція VLOOKUP

Огляд формул у програмі Excel

Способи уникнення недійсних формул

Виявлення помилок у формулах за допомогою функції перевірки помилок

Функції Excel (за алфавітом)

Функції Excel (за категоріями)

Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×