При використанні формули VLOOKUP в Excel іноді може виникнути потворна помилка #Н/Д. Це відбувається, коли ваша формула не може знайти значення пошуку.
У цьому підручнику я покажу вам різні способи використання IFERROR з VLOOKUP для обробки цих #N/A помилок, що з’являються у вашому аркуші.
Використання комбінації IFERROR з VLOOKUP дозволяє показати щось значуще замість помилки #N/A (або будь -якої іншої з цього приводу).
Перш ніж приступати до деталей щодо використання цієї комбінації, давайте спочатку швидко переглянемо функцію IFERROR і подивимося, як вона працює.
Пояснюється функція IFERROR
За допомогою функції IFERROR ви можете вказати, що має статися у випадку, якщо формула або посилання на комірку повертає помилку.
Ось синтаксис функції IFERROR.
= IFERROR (значення, значення_якщо_помилка)
- значення - це аргумент, який перевіряється на наявність помилки. У більшості випадків це або формула, або посилання на клітинку. При використанні VLOOKUP з IFERROR таким аргументом буде формула VLOOKUP.
- value_if_error - це значення, яке повертається, якщо є помилка. Оцінюються такі типи помилок: #N/A, #REF !, #DIV/0 !, #VALUE !, #NUM !, #NAME ?, і #NULL !.
Можливі причини VLOOKUP повернення помилки #N/A
Функція VLOOKUP може повернути помилку #N/A через будь -яку з наступних причин:
- Значення пошуку не знайдено в масиві пошуку.
- У значенні пошуку (або в масиві таблиць) є провідний, кінцевий або подвійний пробіл.
- Існує орфографічна помилка у значенні пошуку або значеннях у масиві пошуку.
Ви можете впоратися з усіма цими причинами помилок за допомогою комбінації IFERROR та VLOOKUP. Однак слід слідкувати за причинами №2 та №3 та виправити їх у вихідних даних, замість того, щоб дозволити IFERROR обробляти їх.
Примітка: IFERROR оброблятиме помилку незалежно від того, що її спричинило. Якщо ви хочете лише лікувати помилки, спричинені тим, що VLOOKUP не може знайти значення пошуку, замість цього використовуйте IFNA. Це переконається, що помилки, окрім #Н/Д, не обробляються, і ви можете дослідити ці інші помилки.
За допомогою функції TRIM можна обробляти початкові, кінцеві та подвійні пробіли.
Заміна помилки VLOOKUP #N/A значущим текстом
Припустимо, у вас є набір даних, як показано нижче:
Як бачите, формула VLOOKUP повертає помилку, оскільки значення пошуку немає у списку. Ми прагнемо отримати рахунок за Глен, якого немає в таблиці оцінок.
Хоча це дуже маленький набір даних, ви можете отримати величезні набори даних, де вам доведеться перевіряти наявність багатьох елементів. У кожному випадку, коли значення не знайдено, ви отримаєте помилку #N/A.
Ось формула, за допомогою якої можна отримати щось значуще замість помилки #N/A.
= ПОМИЛКА (VLOOKUP (D2, $ A $ 2: $ B $ 10,2,0), "Не знайдено")
Наведена вище формула повертає текст “Not Found” замість помилки #N/A. Ви також можете використовувати ту саму формулу, щоб повернути порожній, нульовий або будь -який інший значущий текст.
Вкладений VLOOKUP з функцією IFERROR
Якщо ви використовуєте VLOOKUP і ваша таблиця пошуку фрагментована на одному робочому аркуші або різних аркушах, вам потрібно перевірити значення VLOOKUP через усі ці таблиці.
Наприклад, у наборі даних, показаному нижче, є дві окремі таблиці з іменами студентів та оцінками.
Якщо мені потрібно знайти оцінку Grace у цьому наборі даних, мені потрібно скористатися функцією VLOOKUP для перевірки першої таблиці, а якщо значення в ній не знайдено, перевірте другу таблицю.
Ось вкладена формула IFERROR, яку я можу використовувати для пошуку значення:
= IFERROR (VLOOKUP (G3, $ A $ 2: $ B $ 5,2,0), IFERROR (VLOOKUP (G3, $ D $ 2: $ E $ 5,2,0), "Not Found"))
Використання VLOOKUP з IF та ISERROR (версії до Excel 2007)
Функція IFERROR була представлена в Excel 2007 для Windows та Excel 2016 на Mac.
Якщо ви використовуєте попередні версії, функція IFERROR не працюватиме у вашій системі.
Ви можете відтворити функціональність функції IFERROR за допомогою комбінації функції IF та функції ISERROR.
Дозвольте мені швидко показати вам, як використовувати комбінацію IF та ISERROR замість IFERROR.
У наведеному вище прикладі замість використання IFERROR можна також скористатися формулою, зазначеною у комірці В3:
= IF (ISERROR (A3), ”Not Found”, A3)
Частина формули ISERROR перевіряє наявність помилок (включаючи помилку #N/A) і повертає TRUE, якщо помилка виявлена, а FALSE - якщо ні.
- Якщо це ІСТИНА (це означає, що є помилка), функція IF повертає вказане значення (у цьому випадку "Не знайдено").
- Якщо це значення FALSE (це означає, що помилки немає), функція IF повертає це значення (A3 у наведеному вище прикладі).
IFERROR проти IFNA
IFERROR обробляє всі види помилок, а IFNA - лише помилку № N/A.
При обробці помилок, викликаних VLOOKUP, вам потрібно переконатися, що ви використовуєте правильну формулу.
Використовуйте IFERROR коли ви хочете лікувати всілякі помилки. Тепер помилка може бути викликана різними факторами (такими як неправильна формула, неправильно написаний іменований діапазон, не знаходження значення пошуку та повернення значення помилки з таблиці пошуку). Для IFERROR це не має значення, і воно замінить усі ці помилки на вказане значення.
Використовуйте IFNA коли ви хочете лікувати лише #Н/Д помилок, які, швидше за все, будуть спричинені тим, що формула VLOOKUP не може знайти значення пошуку.
Ви також можете знайти корисними такі підручники Excel:
- Як зробити VLOOKUP чутливим до регістру
- VLOOKUP Vs. ІНДЕКС/МАТЧ - Дебати закінчуються!
- Використовуйте VLookup, щоб отримати останній номер у списку в Excel.
- Як використовувати VLOOKUP з кількома критеріями
- Помилка #NAME у Excel - що її викликає та як її виправити!
