Excel формула VLOOKUP

Як використовувати формулу Vlookup? Чому не працює Vlookup? Помилки REF, #Value, #N/A.

Для того, щоб правильно використовувати Vlookup потрібно йти найлегшим шляхом: покроково вибрати 4 пункти формули Vlookup і натиснути ОК. Ця формула складна, і з першого разу освоїти її нікому не вдається. У кожному завданні свої умови та формула може не спрацювати з тієї чи іншої причини. Як користуватися формулою Vlookup і як справлятися з помилками та написано у цій статті.

Формула Vlookup – це формула для пошуку потрібного числа (або тексту) з іншої таблиці для основної таблиці.

Щоб використовувати формулу Vlookup, потрібно обов’язково мати:

1) 2 таблиці (мінімум) з 1ією однаковою колонкою (тобто з колонкою з одними і тими ж даними). Якщо в 1ій таблиці (Прайс-лист) ціни та назви продуктів, то в 2ій таблиці (Асортимент) ідентифікатори продуктів повинні мати колонку з тими ж назвами, що і в першій таблиці.

2) Таблиці повинні постійно бути розташовані в тому самому місці (бажано в загальній Excel книзі, тобто в єдиному файлі). Якщо у вас 1 таблиця на робочому столі, а друга на флешці, то, вилучивши флешку з комп’ютера, формула перестане працювати.

3) Знання деяких нюансів (описані у випадках з помилками нижче, наприклад, різний формат)

Як Використовувати Формулу Vlookup

Для початку необхідно для прикладу знайти 2 таблиці. Ось вони:

У Таблиці № 1 ми бачимо, що колонка «Назва Товару» порожня. Найлегший спосіб, щоб її заповнити, це скопіювати-вставити імена товарів з Таблиці № 2 в цю колонку. Але що якби ця таблиця була довшою, ніж 4 рядки? Та й до того ж номери та назви товарів у правій таблиці в іншому порядку. Для цього потрібна формула Vlookup.

ВАЖЛИВО: Якщо б ви захотіли додати колонку «Назва Товару» в Таблицю № 1 Зліва від колонки «# товару», то формула VLookup би не спрацювала. Чому? п. 1 «Помилок VLookup та їх причин» (нижче).

Додаємо формулу Vlookup у Таблицю

Стаємо в осередок B3, вводимо =Vlookup( і натискаємо значок функції f_x (див. картинку нижче). Після натискання бачимо вікно, що з’явилося з чотирма полями (для введення аргументів).У кожне поле вводимо відповідне йому значення.

• Lookup_value (Значення_підстановки) = осередок даних, які є як в Таблиці №1 так і в Таблиці №2.
• Table_array (Таблиця) = Таблиця 2, з якої нам потрібно взяти інформацію. А саме, колонку «Назва Товару».
• Col_index_num (Номер_стовпця) = № стовпця з Таблиці № 2, рахується по черговості зліва направо (лише з обраних вами колонок), в даному випадку 2.
• Range_lookup (Точність_пошуку) = Якщо тут ставите 0 (або FALSE), то отримаєте точні дані. Якщо тут ставите 1 (або TRUE), отримаєте приблизні дані.

Отже, ми додаємо такі дані до наступних полів:

• Lookup_value (Значення_підстановки) = A3 (тобто число 110 з 1-ої колонки «# товару»)
• Table_array (Таблиця) = $F$2:$G$6 (просто вибрали Таблицю 2 і зафіксували її значками долара)
• Col_index_num (Номер_стовпця) = 2 (№ стовпця «Назва товару» з Таблиці № 2)
• Range_lookup (Точність_пошуку) = 0 (щоб отримати точні дані).

Помилки Vlookup та їх причини

Види помилок формули Vlookup

1. Колонка шуканого значення (1го аргументу Lookup_value у формулі) знаходиться праворуч від формули, що вводиться – невірне значення знадено.
2. Формула не знаходить значення, оскільки з’їхала рамка обраної таблиці (обраний діапазон не зафіксований доларовими знаками) – помилка #N/A.
3. Формула вибирає дані не з тієї колонки (параметр Col_index_num), хоча вибрано все правильно.
4. Порівнюючи числа [01], [ 1] (нуль одиниця текстовим форматом або пробіл та одиниця текстовим форматом) і [1] (одиниця цифровим форматом) формула вважає їх різними і не може знайти зіставні їм значення – помилка #VALUE.
5. Після переміщення файлу з Таблицею № 2, формула більше не бачить Таблицю № 2 і не може нічого знайти помилка #REF.
6. Для чисел 110 і 111 формула знаходять одні й самі дані, оскільки Excel вважає їх схожими – невірний параметр Range_lookup – пусто, 1 або TRUE – замість 0 або FALSE
7. Все зроблено правильно, але формула не працює. Треба зберегти файли з таблицями № 1 та №2 та закрити їх. При відкритті заново – формули мають запрацювати. Але може знадобитися закриття також і всіх інших відкритих Excel файлів.

Поради та способи вирішення проблем з формулою Vlookup (відповідно до номерів помилок)

1. Вибір таблиці (table_array) повинен починатися з колонки, в якій дані з обох Таблиць (в даному випадку це # товару).
2. Завжди ставте доларові значки на діапазон Таблиці, роблячи посилання абсолютним.
3. Номер колонки повинен рахуватися навіть із урахуванням прихованих колонок (hidden columns).
4. Формат значень має бути однаковим в обох таблицях:
   • формат чисел має бути скрізь числовий (якщо бачите зелений трикутник у лівому верхньому кутку, значить формат числа виставлений текстовим — натисніть на трикутник — і Excel змінить формат на числовий).
   • дотримання нулів (наприклад у випадку з числами 01, 02 це не те ж саме що 1, 2).
   • кількість пробілів має бути однаковою.
5. Таблиці повинні завжди залишатися в тому самому місці.
6. В останньому аргументі завжди слід ставити 0 (або FALSE), щоб шукати точні дані.
7. Якщо Ваші таблиці знаходяться в різних файлах, необхідно переконатися, що ви зберегли ці файли хоча б 1 раз і хоча б 1 раз закривали ці файли. В іншому випадку формула не працюватиме.

Скачати приклад

Якщо вас цікавлять інші корисні формули Excel, то перегляньте наступну стаття: Формула COUNTIF в Excel