У цій статті описано найтиповіші причини виникнення помилки #N/A через використання функцій INDEX або MATCH .
Примітка.
Якщо потрібно, щоб функція INDEX або MATCH повертала замість #N/A змістовне значення, вкладіть її у функцію INDEX і MATCH. Заміна #N і A власним значенням лише визначає помилку, проте не вирішує її. Тому, перш ніж використовувати функцію IFERROR, дуже важливо переконатися, що формула працює правильно.
Проблема: немає точних збігів у даних
Якщо функція MATCH не знаходить шукане значення в масиві пошуку, вона повертає помилку #N/A.
Якщо ви впевнені, що в таблиці є потрібні дані, але функція MATCH їх не знаходить, імовірні наступні причини:
- клітинка містить неочікувані символи або приховані пробіли;
- клітинка містить дані в неприпустимому форматі. Наприклад, клітинка може містити числові значення, але до неї застосовано формат Текстовий.
РІШЕННЯ: Щоб видалити неочікувані символи або приховані пробіли, скористайтеся функцією CLEAN або TRIM відповідно. Крім того, перевірте формат даних у клітинках.
Ви ввели формулу масиву, не натиснувши клавіші Ctrl+Shift+Enter
Якщо ви використовуєте масив в INDEX,MATCH або поєднанні цих двох функцій, необхідно натиснути на клавіатурі клавіші Ctrl+Shift+Enter. Excel автоматично візьме формулу у фігурні дужки {}. Якщо ви спробуєте ввести квадратні дужки самостійно, Excel відобразить формулу як текст.
Примітка.
Якщо ви маєте поточну версію Microsoft 365, ви можете просто ввести формулу в клітинку виводу, а потім натиснути клавішу Enter , щоб підтвердити введення формули динамічного масиву. В іншому разі формулу потрібно ввести як застарілу формулу масиву. Для цього спочатку виберіть діапазон виводу, введіть формулу у клітинку виводу, а потім натисніть клавіші CTRL+SHIFT+ENTER , щоб підтвердити її. Excel автоматично вставляє фігурні дужки на початку та в кінці формул. Докладні відомості про формули масивів див. у статті Приклади формул масивів і рекомендації.
Проблема: неузгодженість типа збігу з порядком сортування даних
Значення аргументу match_type має бути узгоджене з порядком сортування значень у масиві пошуку. Якщо синтаксис відхиляється від наведених нижче правил, стається помилка #N/A.
- Якщо match_type дорівнює 1 або не задано, значення в lookup_array має бути відсортовано за зростанням. Наприклад: -2, -1, 0, 1, 2, … A, B, C, … FALSE, TRUE тощо.
- Якщо match_type –1, значення в lookup_array мають бути за спаданням.
У прикладі нижче функція MATCH має такий вигляд:
=MATCH(40;B2:B10;-1)
Аргумент match_type у синтаксисі має значення -1, тобто дані в діапазоні B2:B10 має бути відсортовано за спаданням. Проте значення розташовано за зростанням, тому виникає помилка #N/A.
РОЗВ'ЯЗАННЯ: Змініть значення аргументу match_type на 1 або відсортуйте таблицю за спаданням. Потім спробуйте знову.
Потрібна додаткова довідка?
Ви завжди можете поставити запитання експерту в спільноті Tech у Excel або отримати підтримку в спільнотах.
Додаткові відомості
Способи виправлення помилки #N/A
Використання в Excel функцій аркуша INDEX і MATCH із кількома умовами
Способи уникнення недійсних формул