Функція VLOOKUP (ВПР)
- Назва в Excel
VLOOKUP- У російській версії
ВПР- Категорія
- Посилання та масиви
Функція VLOOKUP (у російській версії Excel — ВПР) є однією з найчастіше вживаних і універсальних при роботі з даними в Excel. Вона має недоліки, але її обов’язково слід опанувати для розуміння механізму роботи з даними в Excel. Увага!!! У серпні 2019 Microsoft представила заміну для VLOOKUP — функцію XLOOKUP, яка краща в усьому, але вимагає підтримки динамічних масивів
Назви функцій подано англійською, як в українському Excel. Скриншоти й книги-приклади — з російської версії.
Опис функції VLOOKUP
У російській версії Excel ця функція називається ВПР. VLOOKUP виконує пошук вказаного значення в певному діапазоні (або таблиці з кількох стовпців) і повертає значення, яке знаходиться в тому ж рядку, що й шукане, з цього ж діапазону. Наглядно алгоритм дій виглядає так:

- Функція посилається на ідентифікатор «40», тобто значення, яке потрібно знайти в першому стовпці діапазону A3:D9, і повертає значення з 4-го стовпця того самого рядка, де знаходиться ідентифікатор 40 — це значення зарплати 87000
=VLOOKUP(G2;A3:D9;4;0)
=VLOOKUP(G6;A3:D9;3;0)
В обох випадках як четвертий параметр стояв «0» — цей параметр називається «інтервальний_пошук». Його можна записувати як «0» або «1», а також як FALSE або TRUE. Детальніше про цей аргумент нижче в розділі «Синтаксис»
На перший погляд може здатися трохи заплутано, але насправді використовувати цю опцію доволі просто.
Буква «V» у назві функції VLOOKUP означає «vertical» (вертикально). VLOOKUP застосовують замість функції HLOOKUP, якщо порівнювані значення розташовані в стовпці ліворуч від шуканих даних.
Ось інший приклад, що наочно демонструє принцип роботи VLOOKUP:

Синтаксис
=VLOOKUP(шукане_значення; таблиця; номер_стовпця; [інтервальний_пошук])
Аргументи
шукане_значення
Обов'язковий. Значення, яке має бути знайдене в першому стовпці таблиці або діапазону. Аргумент шукане_значення може бути значенням або посиланням. Якщо шукане_значення менше, ніж найменше значення в першому стовпці аргументу таблиця, функція VLOOKUP повертає помилку #N/A.
таблиця
Обов'язковий. Діапазон клітинок, що містить дані. Можна використовувати посилання на діапазон (наприклад, A2:D8) або ім'я діапазону. Значення в першому стовпці аргументу таблиця — це значення, у яких виконується пошук аргументу шукане_значення. Ці значення можуть бути текстовими, числовими або логічними. Текстові значення в нижньому й верхньому регістрах вважаються еквівалентними.
номер_стовпця
Обов'язковий. Номер стовпця в аргументі таблиця, з якого повертається знайдене значення. Якщо номер_стовпця дорівнює 1, повертається значення з першого стовпця аргументу таблиця; якщо номер_стовпця дорівнює 2 — значення з другого стовпця аргументу таблиця і т. д.
Якщо значення аргументу номер_стовпця:
- менше 1, функція VLOOKUP повертає помилку #VALUE!;
- більше, ніж число стовпців в аргументі таблиця, функція VLOOKUP повертає помилку #REF!.
інтервальний_пошук
Необов'язковий. Логічне значення, що визначає, яке співпадіння має знайти VLOOKUP — точне чи приблизне.
Якщо аргумент інтервальний_пошук має значення TRUE або опущений, повертається точне або приблизне співпадіння. Якщо точне співпадіння не знайдено, повертається найбільше значення, яке менше за шукане_значення.
Увага! Якщо аргумент інтервальний_пошук має значення TRUE або опущений, значення в першому стовпці аргументу таблиця мають бути впорядковані за зростанням, інакше VLOOKUP може повернути неправильний результат.
Якщо аргумент інтервальний_пошук має значення FALSE, VLOOKUP шукає лише точне співпадіння. Якщо в першому стовпці аргументу таблиця є кілька значень, що відповідають шукане_значення, використовується перше знайдене значення. Якщо точне співпадіння не знайдено, повертається помилка #N/A.
Зауваження
- При пошуку в великому масиві, особливо якщо на аркуші Excel багато формул, у користувача можуть виникати проблеми з продуктивністю. Перше, що спадає на думку — це оновити комп'ютер. Так, VLOOKUP вважається досить повільною, проте мало хто знає, що її швидкість можна збільшити в десять разів, якщо встановити аргумент рівним TRUE (або 1). Тут є нюанс у вигляді обов'язкового сортування, тому таке прискорення працюватиме не завжди. До речі, швидкість зростає через сортування, у цьому випадку немає потреби порівнювати кожне значення з стовпця.
- Функція VLOOKUP завжди виконує пошук у самому першому (лівому) стовпці діапазону. Обмеження можна обійти, де це можливо, звузивши аргумент таблиця, щоб шуканий стовпець став першим

Пошук VLOOKUP завжди в самому лівому стовпці - При пошуку текстових значень у першому стовпці аргументу таблиця переконайтеся, що дані в цьому стовпці не містять початкових або кінцевих пробілів, неправильних прямих ( ‘ або » ) чи парних ( ‘ або “ ) лапок або непечатних символів. У таких випадках VLOOKUP може повернути неправильне або неочікуване значення

Повернення помилки функцією VLOOKUP - При пошуку числових значень або дат переконайтеся, що дані в першому стовпці аргументу таблиця не є текстовими. В іншому випадку VLOOKUP може повернути неправильне або неочікуване значення
- Якщо значення аргументу «інтервальний_пошук» — FALSE, а аргумент шукане_значення є текстом, то в аргументі шукане_значення дозволяється використання підстановочних знаків: знак питання (?) і зірочка (*). Знак питання відповідає будь-якому одиночному символу; зірочка — будь-якій послідовності символів. Якщо потрібно знайти сам знак питання або зірочку, поставте перед ними тильду (~)

Використання підстановок при пошуку текстових значень
Нюанси використання VLOOKUP
Коли користувач працює з VLOOKUP, можуть виникати різні ситуації, коли щось іде не так або потрібно обійти певні обмеження. Тут розглядаються такі нюанси використання й рішення потенційних проблем.
Пошук у першому стовпці
Як уже зазначалося в зауваженнях, одним із мінусів є те, що пошук виконується тільки в найлівішому стовпці.
Може статися, що стовпець із потрібними значеннями розташований лівіше від стовпця, у якому має відбуватися пошук.
Якщо потрібно шукати лівіше від шуканого стовпця, слід використовувати зв'язку INDEX + MATCH. Розуміння роботи цієї зв'язки дещо складніше, ніж VLOOKUP, але, опанувавши її, використовувати INDEX + MATCH буде нескладно.
Пошук до першого співпадіння
Особливістю роботи VLOOKUP є пошук лише до першого співпадіння. Якщо перший стовпець діапазону не є унікальним, буде знайдено лише перше входження. Іноді це саме те, що потрібно, іноді — ні, тому варто мати це на увазі.

Це обмеження можна обійти — рішення непросте, але детально описане в курсі Excel.
Незалежність від регістру
Для Excel і VLOOKUP не важливо, у якому регістрі (великі чи малі літери) записане шукане значення і як воно записане в діапазоні.

Зайві пробіли
У примітках ми показали, як непомітний пробіл може спричинити помилку в роботі цієї функції. Якщо є ризик, що клітинки засмічуються надмірною кількістю пробілів, слід очистити клітинки за допомогою функції TRIM.
Різниця в форматі даних
Якщо один аргумент VLOOKUP посилається на клітинку, що містить число у текстовому форматі, а початковий стовпець масиву має числовий формат (або навпаки), пошук не дасть результату.
Усе можна виправити приведенням аргументів до однакового формату. Наприклад:
=VLOOKUP(--G2; A3:D9; 4; 0)
— якщо в D5 букви, а в таблиці — цифри;
=VLOOKUP(G2 & ""); A3:D9; 4; 0)
— або навпаки.

Існує кілька варіантів, як перетворити текст у число:
- Піднести до степеня: G2^1;
- Подвійне заперечення: --G2;
- Додати нуль: G2+0;
- Помножити на одиницю: G2*1.
Припинення появи помилки #N/A
Якщо є ймовірність виникнення помилки під час обчислення формули, можна скористатися функцією IFERROR, яка поверне задане значення у випадку помилки.
Не зафіксовано масив
У наведених вище прикладах ми не використовували фіксацію масиву, бо завжди шукали одне значення, тобто не користувалися автозаповненням.
Однак у більшості випадків VLOOKUP ви будете використовувати саме з автозаповненням. У такому випадку найкраще застосувати абсолютне посилання для фіксації діапазону пошуку.
Масив можна оформити як розумну таблицю. Команда «Форматувати як таблицю» на вкладці «Основне», або гаряче поєднання Ctrl+T. У такому випадку посилання на такий діапазон автоматично стануть абсолютними. Але при роботі з такими таблицями в Excel є свої нюанси, і не завжди це підходить.

Відносний пошук
При використанні VLOOKUP користувачі, як правило, завжди ставлять аргумент інтервальний_пошук рівним «0» (FALSE), що означає виконати точний пошук і що значення в першому стовпці не відсортовані. Проте якщо пошук здійснюється за числовими значеннями, в деяких випадках гарним рішенням буде відсортувати значення та виконати неточний пошук. А в деяких випадках неточний пошук взагалі буде єдиним рішенням. Розглянемо детальніше.
Розглянемо докладно приклади з неточним співпадінням, і тут не зайвим буде нагадати, що якщо точне співпадіння не знайдено, повертається найбільше значення, що менше за шукане_значення.
Пошук оцінки
Сьогодні оцінку часто можна зустріти в двох форматах: у балах і у буквено-символьному позначенні, наприклад:
- 0 — 60: F
- 60 — 70: D
- 70 — 80: C
- 80 — 90: B
- 90 — 100: A
Ось як виглядає формула, якщо для її побудови використовувати логічну функцію IF (так-так, з використанням SWITCH або IFS рішення теж буде кращим, але припустимо, у вас не найновіший Excel, і тут VLOOKUP виграє навіть у нових логічних функцій):

подивіться на рисунку на формулу з IF і порівняйте з VLOOKUP з неточним співпадінням — остання виглядає значно краще:
=VLOOKUP(E1;A2:B6;2;1)Максимальне значення
Для пошуку максимального значення є більш підходяща функція MAX, однак вона просто знайде максимальне число, а от за допомогою VLOOKUP ми можемо знайти інше значення в таблиці, що стоїть в одному рядку з максимальним. Наприклад, у нас є стовпець із зарплатою співробітників і потрібно знайти прізвище співробітника з максимальною зарплатою.
Оскільки, якщо VLOOKUP не знаходить точного співпадіння, функція поверне максимальне значення, що менше за шукане_значення, то нам достатньо ввести дуже велике число, яке гарантовано більше зарплати будь-якого співробітника. Головне не забути відсортувати дані за зарплатою.

В'язкість рідини
У прикладах ви знайдете класичний приклад використання VLOOKUP для визначення в'язкості рідини або температури за щільністю. Чому класичний? Тому що в таких випадках, як правило, точні значення щільності не вказують — через округлення до тисячних отримане значення може не збігатися з табличним, доводиться брати максимальне значення, що менше за шукане_значення.
Приклад
Великий приклад з відео
Приклад про в'язкість рідини
Відео роботи функції
Повна інструкція VLOOKUP
Ще про VLOOKUP
Порівняння двох файлів
Розв'язання задач #ExcelWorldChamp
Додаткові матеріали
Як порівняти два файли MS Excel
#Excelwordlchamp 1-1 Розв'язуємо задачі першого туру