Функція VLOOKUP в Excel шукає значення в діапазоні комірок, а потім повертає значення, яке знаходиться в тому ж рядку, що й значення, яке ви шукаєте.
VLOOKUP, що означає «Вертикальний пошук», — це функція пошуку, яка шукає значення в крайньому лівому стовпці (першому стовпці) діапазону та повертає паралельне значення зі стовпця праворуч. Функція VLOOKUP шукає лише значення вгорі (зверху вниз) у таблиці, розміщеній вертикально.
Наприклад, скажімо, у нас є інвентарний список на робочому аркуші з таблицею, що показує назви товарів, дату покупки, кількість та ціну. Потім ми можемо використовувати VLOOKUP на іншому робочому аркуші, щоб витягти кількість і ціну для певної назви товару з аркуша інвентарю.
Функція VLOOKUP спочатку може здатися складною, але насправді нею досить легко користуватися, коли ви зрозумієте, як вона працює. Тут, у цьому посібнику, ми покажемо вам, як використовувати функцію VLOOKUP в Excel.
Синтаксис і аргументи VLOOKUP
Якщо ви збираєтеся використовувати функцію VLOOKUP, вам потрібно знати її синтаксис та її аргументи.
Синтаксис функції VLOOKUP:
=VLOOKUP(значення_шукання,масив_таблиці,число_індексу_стовпця,[діапазон_пошуку])Ця функція складається з 4 параметрів або аргументів:
- пошукове_значення: Це визначає значення, яке ви шукаєте в першому стовпці даного масиву таблиці. Значення пошуку завжди має бути в крайньому лівому (стовпець таблиці пошуку.
- table_array: Це таблиця (діапазон комірок), у якій ви хочете знайти значення. Ця таблиця (пошукова таблиця) може бути на одному робочому аркуші, на іншому аркуші або навіть в іншій книзі.
- col_index_num: Це визначає номер стовпця масиву таблиці, який має значення, яке ви бажаєте витягти.
- [range_lookup]: Цей параметр визначає, чи потрібно витягти точну чи приблизну відповідність. Це або TRUE, або FALSE, введіть «FALSE», якщо вам потрібно точне значення, або введіть «TRUE», якщо у вас все в порядку з приблизним значенням.
Використання функції VLOOKUP в Excel
Давайте розглянемо, як використовувати VLOOKUP в Microsoft Excel.
Основний приклад
Щоб використовувати VLOOKUP, спочатку потрібно створити свою базу даних або таблицю (див. нижче).
Потім створіть таблицю або діапазон, де потрібно шукати, і витягніть значення з таблиці пошуку.
Далі виберіть осередок, куди потрібно витягнути значення, і введіть таку формулу VLOOKUP. Наприклад, ми хочемо знайти номер телефону "Ena", потім нам потрібно ввести значення пошуку як B13, A2:E10 як масив таблиці, 5 для номера стовпця номера телефону і FALSE, щоб повернути точне значення значення. Потім натисніть «Enter», щоб завершити формулу.
=VLOOKUP(B13,A2:E10,5,FALSE)Вам не потрібно вводити діапазон таблиці вручну, ви можете просто вибрати діапазон або таблицю за допомогою миші для аргументу table_array. І він буде доданий до аргументу автоматично.
Пам’ятайте, щоб це спрацювало, значення Lookup-value має бути в лівій частині нашої таблиці пошуку (A2:E10). Крім того, Lookup_value не обов’язково має бути в стовпці A робочого аркуша, це просто має бути крайній лівий стовпець діапазону, який потрібно шукати.
Vlookup виглядає правильно
Функція VLOOKUP може дивитися лише праворуч від таблиці. Він шукає значення в першому стовпці таблиці або діапазону та витягує відповідне значення зі стовпця праворуч.
Точна відповідність
Функція VLOOKUP Excel має два методи зіставлення: точний і приблизний. Параметр range_lookup у функції VLOOKUP визначає, який тип ви шукаєте, точний чи приблизний.
Якщо ви введете range_lookup як «FALSE» або «0», формула шукає значення, яке точно дорівнює lookup_value (це може бути число, текст або дата).
=VLOOKUP(A9,A2:D5,3,FALSE)
Якщо в таблиці не знайдено точної відповідності, вона поверне помилку #N/A. Коли ми спробували знайти «Японія» та повернути відповідне значення в стовпці 4, виникає помилка #N/A, оскільки в першому стовпці таблиці немає «Японія».
Ви можете ввести число «0» або «FALSE» в останній аргумент. Обидва вони означають те саме в Excel.
Приблизна відповідність
Іноді не обов’язково потрібен точний збіг, достатньо найкращого. У таких випадках можна використовувати приблизний режим відповідності. Встановіть кінцевий аргумент функції «TRUE», щоб знайти приблизний збіг. Значенням за замовчуванням є TRUE, що означає, що якщо ви не додасте останній аргумент, функція використовуватиме приблизне відповідність за замовчуванням.
=VLOOKUP(B10,A2:B7,2,TRUE)У цьому прикладі нам не потрібен точний бал, щоб знайти відповідну оцінку. Все, що нам потрібно, це оцінки, щоб бути в цьому діапазоні.
Якщо VLOOKUP знайде точну відповідність, він поверне це значення. У наведеному вище прикладі, якщо формула не може знайти значення look_up 89 у першому стовпці, вона поверне наступне найбільше значення (80).
Перший матч
Якщо крайній лівий стовпець таблиці містить дублікати, VLOOKUP знайде і поверне першу відповідність.
Наприклад, VLOOKUP налаштовано на пошук прізвища для імені «Мія». Оскільки є 2 записи з іменем «Міа», то функція повертає прізвище для першого запису «Бена».
Підстановка відповідності
Функція VLOOKUP дозволяє знайти часткову відповідність для заданого значення за допомогою символів підстановки. Якщо ви хочете знайти значення, яке містить значення пошуку в будь-якій позиції, додайте знак амперсанда (&), щоб об’єднати наше значення пошуку зі символом підстановки (*). Використовуйте знаки «$», щоб створити абсолютні посилання на клітинки та додайте підстановковий знак «*» до або після значення пошуку.
У прикладі ми маємо лише частину значення пошуку (Vin) у клітинці B13. Отже, щоб виконати часткову відповідність для заданих символів, об’єднайте символ «*» після посилання на клітинку.
=VLOOKUP($B$13&"*",$A$2:$E$10,3,FALSE)
Кілька пошуків
Функція VLOOKUP дозволяє створювати динамічний двосторонній пошук, збігаючи як рядки, так і стовпці. У наступному прикладі VLOOKUP налаштовано на виконання пошуку на основі імені (Майра) та міста. Синтаксис у B14 такий:
=VLOOKUP(B13;A2:E10, СПІВНИК(A14;A1:E1,0),0)
Як виконати ВПР з іншого аркуша в Excel
Зазвичай функція VLOOKUP використовується для повернення відповідних значень з окремого аркуша, і вона рідко використовується з даними в одному аркуші.
Щоб здійснити пошук з іншого аркуша Excel, але в тій самій книзі, введіть назву аркуша перед table_array зі знаком оклику (!).
Наприклад, щоб знайти значення клітинки A2 робочого аркуша «Продукти» в діапазоні A2:B8 на робочому аркуші «ItemPrices» і повернути відповідне значення зі стовпця B:
=VLOOKUP(A2,ItemPrices!$A$2:$C$8,2,FALSE)На малюнку нижче показано таблицю в аркуші «Ціни товарів».
Коли ми вводимо формулу VLOOKUP у стовпець C робочої таблиці «Продукти», вона витягує відповідні дані з робочого аркуша «ItemPrices».
Як виконати ВПР з іншої книги в Excel
Ви також можете шукати значення в зовсім іншій книзі. Якщо ви хочете здійснити ВПР з іншої книги, вам потрібно помістити назву книги в квадратні дужки, а потім назву аркуша перед table_array зі знаком оклику (!) (як показано нижче).
Наприклад, скористайтеся цією формулою, щоб знайти значення комірки A2 іншого робочого аркуша з аркуша під назвою «ItemPrices» у книзі «Item.xlsx»:
=VLOOKUP(A2,[Item.xls]ItemPrices!$A$2:$B$8,2,FALSE)Спочатку відкрийте обидві книги, потім почніть вводити формулу в клітинку C2 аркуша (Таблиця продукту), а коли ви дійдете до аргументу table_array, перейдіть до основної книги даних (Item.xlsx) і виберіть діапазон таблиці. Таким чином, вам не доведеться вводити назву книги та аркуша вручну. Введіть решту аргументів і натисніть клавішу «Enter», щоб завершити виконання функції.
Навіть якщо ви закриєте книгу, яка містить таблицю пошуку, формула VLOOKUP продовжуватиме працювати, але тепер ви можете побачити повний шлях до закритої книги, як показано на наступному знімку екрана.
Використовуйте функцію VLOOKUP зі стрічки Excel
Якщо ви не можете згадати формули, ви завжди можете отримати доступ до функції VLOOKUP зі стрічки Excel. Щоб отримати доступ до VLOOKUP, перейдіть на вкладку «Формули» на стрічці Excel і натисніть значок «Пошук та посилання». Потім виберіть опцію «VLOOKUP» у нижній частині спадного меню.
Потім введіть аргументи в діалоговому вікні «Аргументи функції». Потім натисніть кнопку «ОК».
У прикладі ми шукали ім’я «Шеріл» у таблиці, щоб повернути його відповідний стан у стовпці D.
Сподіваємося, ви дізналися, як використовувати функцію VLOOKUP в Excel з цієї статті. Якщо ви хочете дізнатися більше про те, як користуватися Excel, перегляньте наші інші статті, пов’язані з Excel.