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

У цій статті описано найпоширеніші причини появи помилки "#N/A" в результаті функцій INDEXабо MATCH .

Примітка.: Якщо потрібно, щоб функція INDEX або MATCH повертав змістовне значення, а не #N/A, використовуйте функцію IFERROR , а потім вкладіть функції 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 має бути узгодженість значення в аргументі 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)

Excel match function

Для аргументу match_type в синтаксисі встановлено значення -1, тобто порядок значень у клітинках B2:B10 має бути за спаданням, щоб формула працювала. Але значення розташовані за зростанням, і це призводить до помилки #N/A.

РІШЕННЯ: Змініть аргумент match_type на 1 або відсортуйте таблицю за спаданням. Потім спробуйте ще раз.