У цій статті описано найпоширеніші причини використання функції VLOOKUP для неправильного результату та запропоновано натомість використовувати функції INDEX і MATCH.

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

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

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

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

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

Результат помилки #N/A, тому що у другому стовпці (Продукти) для аргументу A2:C10 шукане значення ''Капуста'' відображається table_array ''Творити''. У цьому випадку Excel в стовпці A, а не в стовпці B.

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

Натомість спробуйте використовувати функції INDEX і MATCH

З більшості випадків, коли VLOOKUP не відповідає вашим потребам, можна використовувати добре підходять параметри INDEX і MATCH. Основна перевага 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. З іншого боку, якщо потрібно додати стовпець до таблиці, тож вона створює статичне посилання на таблицю.

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

Синтаксис

Щоб створити синтаксис для INDEX і MATCH, потрібно використати аргумент масиву або посилання з функції INDEX і вбудувати в нього синтаксис MATCH. Ось як це зробити:

=INDEX(масив або посилання;MATCH(значення_підстановки;масив_який_переглядається;[тип_зіставлення])

Давайте використамо для заміни VLOOKUP із наведеного вище прикладу. Синтаксис матиме такий вигляд:

=INDEX(C2:C10;MATCH(B13;B2:B10;0))

Простою українською мовою це означає:

=INDEX(повертає значення із діапазону C2:C10; це буде MATCH(Капуста, що розташоване десь у масиві B2:B10, у якому повернуте значення – перше значення, яке відповідає "Капуста"))

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

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

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

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

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

Крім того, щоб очистити дані в клітинках, рекомендовано використовувати функцію CLEAN або TRIM.

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

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

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

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

Вирішення.

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

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

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

Якщо для аргументу range_lookup значення TRUE (і один зі стовпців підстановки) не відсортовано за зростанням (від А до Я), в #N або A в рядку формули з'являється помилка.

Вирішення.

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

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

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

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

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

Потрібна додаткова довідка?

Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільноті Answers.

Додаткові відомості

Потрібна додаткова довідка?

Отримуйте нові функції раніше за інших

Приєднатися до Microsoft оцінювачів >

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

Наскільки ви задоволені якістю мови?
Що вплинуло на ваші враження?

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

×