Що таке VLOOKUP (ВПР)

VLOOKUP (ВПР, вертикальний пошук, вертикальний перегляд результату) шукає значення у таблиці по вертикалі та виводить його в задану комірку.

Коли використовується

VLOOKUP використовується, коли потрібно знайти значення (посилання на клітинку, текстовий рядок, значення) у таблиці чи діапазоні за стовпчиком. Коли функція знаходить відповідність, вона повертає значення у тому ж рядку.  

Синтаксис

= VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

або

= ВПР (шукане значення, масив таблиці, номер стовпця, [інтервальний перегляд])

lookup_value — що ми шукаємо, значення пошуку, яке ми намагаємося знайти

table_array — де саме (в якій таблиці/посиланні на діапазон клітинок/іменованому діапазоні). Стовпчик, у якому буде виконуватись пошук, обов’язково має бути першим за порядком.

col_index_num — номер індексу стовпчика в діапазоні, з якого повертається значення

range_lookup — необов’язковий, але важливий аргумент. Він встановлює, чи потрібен приблизний (1/TRUE) або точний (0/FALSE) збіг. Якщо пропустити цей аргумент, за замовчуванням він матиме значення TRUE (тобто приблизний збіг).

*Важливий момент:

Перед початком роботи з ВПР переконайтеся, що дані відсортовані у порядку зростання. Інакше результат може виявитися неточним.

Приклади

У списку з артикулами товарів, найменуваннями та ціною нам потрібно знайти вартість товару з артикулом 103.

У такій маленькій таблиці, як  у нашому прикладі, зробити це нескладно. Але коли даних — десятки тисяч, пошук буде дещо складним.

Тому звернемося до функції ВПР.

Перший аргумент — шукане значення (lookup_value)

У якості нього може бути

• посилання на комірку (В5 або F3, якщо винести шукане значення в окрему клітинку)
• числове значення (103). Якщо шукаємо текст, його обов’язково потрібно взяти у лапки

Другий аргумент — table_array

Зазначаємо діапазон даних, в яких потрібно робити пошук (у нашому випадку — діапазон B3:D9)

Третій аргумент — col_index_num

У даному випадку — це 3 (вартість товару прописана у третьому за рахунком стовпчику)

Результат (34) буде однаковий незалежно від того, чи ми вказали в якості першого аргументу посилання на комірку (В5 або F3), чи зазначили числове значення (103)

Ми пропустили необов’язковий параметр range_lookup (інтервальний перегляд). ВПР за замовченням визначила, що дані таблиці відсортовані за зростанням (що так і є).

*Втім, це не завжди працює

Візьмемо той самий приклад, але дані будуть у нас невідсортовані.

Ми шукаємо вартість артикулу 103 (у прикладі нижче він розташований на 7-му рядку).

Логіка ВПР влаштована так, що функція шукає значення з першого рядочка і продовжує шукати по рядам далі. Як тільки вона знаходить значення більше, ніж ми шукаємо, ВПР повертає попереднє. Функція проходить по набору даних та зупиняється на першому знайденому значенні. Їй немає сенсу проходити далі по всьому набору даних.

У нашому прикладі функція ВПР повернула значення артикулу 102. Ми не вказали 4-й аргумент (інтервальний перегляд), тому ВПР вважає за замовчуванням, що дані в таблиці відсортовані за зростанням.

Функція починає шукати артикул 103 по рядкам, знаходить 104 (тобто більший, ніж шукане значення) і завершує пошук, а саме повертає значення попереднього артикулу — 102 зі значенням ціни 112.

Далі функція вже не шукає значення. За логікою VLOOKUP артикул 103 не може бути розташований нижче, ніж 104.

Ось тому перед роботою із ВПР краще усі дані відсортувати. *Сортувати за зростанням можна не тільки числові, але й текстові значення (за алфавітним порядком)

Якщо ви впевнені в тому, що дані правильно відсортовані, можна пропустити 4-й аргумент. Якщо ні — вкажіть FALSE (ЛОЖЬ)  або 0.

*Ще одна причина, чому корисно вказувати range_lookup, — ситуації, коли значення не існує.

Наприклад, нам потрібно знайти значення артикулу 108. У нашій таблиці такого немає. Якщо ми не вкажемо 4-й аргумент, функція поверне останній рядок діапазону (в даному випадку — 34, що відповідає значенню артикулу 103). При цьому ВПР не вкаже, що артикулу 108 у таблиці не існує

Якщо ж ми вкажемо в якості останнього необов’язкового аргументу FALSE (ЛОЖЬ) або 0, функція поверне помилку #Н/Д.

Недоліки VLOOKUP

1. Шукає значення лише по першому стовпчику діапазону і повертає значення із стовпчиків праворуч.
2. Обмеження довжини шуканих значень у 256 символів
3. Функція не буде працювати, якщо додати або прибрати стовпець у діапазон пошуку.

Усі ці недоліки виправляють інші функції — INDEX+MATCH та XLOOKUP

Хитрощам роботи з Excel навчаємо на наших курсах

Дізнайтеся більше про повну програму "EXCEL: бізнес-аналіз і прогнозування"