Завантажте зразок робочої книги
Цей підручник демонструє, як користуватися Функція Excel VLOOKUP в Excel, щоб знайти значення.
Огляд функції VLOOKUP
Функція VLOOKUP Vlookup означає вертикальний пошук. Він шукає значення в крайньому лівому стовпці таблиці. Потім повертає значення певну кількість стовпців праворуч від знайденого значення. Це те саме, що і hlookup, за винятком того, що він шукає значення вертикально, а не горизонтально.
(Зверніть увагу, як виглядають вхідні дані формул)
Синтаксис та аргументи функції VLOOKUP:
| 1 | = VLOOKUP (lookup_value, table_array, col_index_num, range_lookup) |
lookup_value - Значення, яке ви хочете шукати.
table_array - Діапазон даних, що містить як значення, яке потрібно шукати, так і значення, яке потрібно повернути vlookup. Стовпець, що містить значення пошуку, повинен бути крайнім лівим стовпцем.
col_index_num - Номер стовпця діапазону даних, з якого потрібно повернути значення.
range_lookup - Правда чи неправда. FALSE шукає точну відповідність. ІСТИНА шукає найближчу відповідність, меншу або рівну значенню lookup_value. Якщо вибрано значення ІСТИНА, крайній лівий стовпець (стовпець пошуку) слід сортувати за зростанням (від найнижчого до найбільшого).
Що таке функція VLOOKUP?
Як одна зі старих функцій у світі електронних таблиць, функція VLOOKUP використовується В.ертичний Пошук. Він має кілька обмежень, які часто долаються іншими функціями, такими як INDEX/MATCH. Тим не менш, він має перевагу, будучи єдиною функцією, яка сама може здійснювати пошук точного збігу. Це також, як правило, одна з перших функцій, про які люди дізнаються.
Основний приклад
Давайте подивимося на зразок даних з підручника. Ми розглянемо кілька прикладів вилучення інформації для конкретних студентів.
Якщо ми хочемо знайти, до якого класу належить Боб, ми написали б формулу:
| 1 | = VLOOKUP ("Боб", A2: C5, 2, FALSE) |
Важливо пам’ятати, що товар, який ми шукаємо (Боб), повинен знаходитися у першому стовпці нашого діапазону пошуку (A2: C5). Ми сказали функції, що хочемо повернути значення з 2nd стовпець, який у цьому випадку є стовпцем B. Нарешті, ми вказали, що хочемо зробити an точна відповідність розмістивши False як останній аргумент. Тут відповідь буде «Читати».
Бічна порада: Ви також можете використовувати число 0 замість False як останній аргумент, оскільки вони мають однакове значення. Деякі люди вважають за краще це, оскільки швидше писати. Просто знайте, що обидва прийнятні.
Зміщені дані
Щоб додати деякі пояснення до нашого першого прикладу, елемент пошуку не повинен знаходитися у стовпці А вашої електронної таблиці, а лише у першому стовпці вашого діапазону пошуку. Давайте використаємо той самий набір даних:
Тепер давайте знайдемо оцінку для класу природознавства. Наша формула буде такою
| 1 | = VLOOKUP ("Наука", B2: C5, 2, FALSE) |
Це все ще є дійсною формулою, оскільки першим стовпцем нашого діапазону пошуку є стовпець B, де буде знайдено наш пошуковий термін «Наука». Ми повертаємо значення з 2nd стовпця діапазону пошуку, який у цьому випадку є стовпцем C. Відповідь тоді-«A-».
Використання підстановки
Функція VLOOKUP підтримує використання символів підстановки “*” та “?” при проведенні обшуків. Наприклад, припустимо, що ми забули, як пишеться ім’я Франка, і просто хотіли шукати ім’я, яке починається на „F”. Ми могли б записати формулу
| 1 | = VLOOKUP ("F*", A2: C5, 2, FALSE) |
Це дозволить знайти ім'я Френк у рядку 5, а потім повернути значення з 2nd відносний стовпець. У цьому випадку відповідь буде «Наука».
Неточне збіг
Здебільшого ви хочете переконатися, що останній аргумент у VLOOKUP - невірний (або 0), щоб отримати точну відповідність. Однак є випадки, коли ви можете шукати неточне збіг. Якщо у вас є список відсортованих даних, ви також можете скористатися VLOOKUP, щоб повернути результат для того самого елемента або наступного найменшого. Це часто використовується при роботі зі збільшенням діапазонів цифр, наприклад у таблиці податків або преміях до комісійних.
Скажімо, ви хочете знайти ставку податку для доходу, введеного у клітинку D2. Формула в D4 може бути такою:
| 1 | = VLOOKUP (D2, A2: B6, 2, TRUE) |
Відмінність цієї формули в тому, що наш останній аргумент - «Істина». У нашому конкретному прикладі ми можемо побачити, що коли наші особи вносять дохід у розмірі 45 000 доларів США, вони матимуть ставку податку 15%.
Примітка: Хоча ми зазвичай хочемо точного збігу з False як аргумент, але ви забуваєте вказати 4го аргументом у VLOOKUP, за замовчуванням - True. Це може призвести до отримання несподіваних результатів, особливо при роботі з текстовими значеннями.
Динамічний стовпець
VLOOKUP вимагає від вас аргументу, в якому стовпці ви хочете повернути значення, але може виникнути випадок, коли ви не знаєте, де буде стовпець, або ви хочете дозволити своєму користувачеві змінити, з якого стовпця повертатися. У цих випадках може бути корисно скористатися функцією MATCH для визначення номера стовпця.
Давайте знову розглянемо наш приклад зошита з деякими введеннями в Е2 та Е4. Щоб отримати номер стовпця, ми могли б написати формулу
| 1 | = MATCH (E2, A1: C1, 0) |
Це спробує знайти точну позицію “Оцінка” в діапазоні A1: C1. Відповідь буде 3. Знаючи це, ми можемо підключити його до функції VLOOKUP і записати формулу на E6 так:
| 1 | = VLOOKUP (E4, A2: C5, MATCH (E2, A1: C1, 0), 0) |
Отже, функція MATCH оцінюватиметься в 3, і це повідомляє VLOOKUP повернути результат з 3rd стовпця в діапазоні A2: C5. Загалом, тоді ми отримуємо бажаний результат "С". Наша формула зараз є динамічною, оскільки ми можемо змінити або стовпець для перегляду, або ім’я для пошуку.
Обмеження VLOOKUP
Як згадувалося на початку статті, найбільшим падінням VLOOKUP є те, що він вимагає, щоб пошуковий термін знаходився у крайньому лівому стовпці діапазону пошуку. Хоча є деякі химерні хитрощі, які можна зробити, щоб подолати це, загальною альтернативою є використання INDEX та MATCH. Ця комбінація дає вам більше гнучкості, а іноді навіть може бути швидшим розрахунком.
VLOOKUP у Таблицях Google
Функція VLOOKUP працює точно так само в Google Таблицях, як і в Excel:
додаткові нотатки
Використовуйте функцію VLOOKUP для виконання ВЕРТИКАЛЬНОГО пошуку. VLOOKUP шукає точну відповідність (range_lookup = FALSE) або найближчий збіг, який дорівнює або менше значення lookup_value (range_lookup = ІСТИНА, лише числові значення) у першому рядку табличного_масиву. Потім він повертає відповідне значення, n кількість рядків нижче відповідності.
Використовуючи VLOOKUP для визначення точної відповідності, спочатку ви визначаєте ідентифікаційне значення, яке потрібно шукати як lookup_value. Цим ідентифікуючим значенням може бути SSN, ідентифікатор працівника, ім’я або інший унікальний ідентифікатор.
Далі ви визначаєте діапазон (який називається table_array), що містить ідентифікатори у верхньому рядку та будь -які значення, які ви зрештою хочете шукати у рядках під ним. ВАЖЛИВО: Унікальні ідентифікатори мають бути у верхньому рядку. Якщо їх немає, вам потрібно або перемістити рядок у верхню частину, або використовувати MATCH / INDEX замість VLOOKUP.
По -третє, визначте номер рядка (row_index) з table_array що ти хочеш повернутися. Майте на увазі, що перший рядок, що містить унікальні ідентифікатори, - це рядок 1. Другий рядок - це рядок 2 тощо.
Нарешті, ви повинні вказати, чи шукати точну відповідність (FALSE) або найближчу відповідність (TRUE) у файлі range_lookup. Якщо вибрано опцію точної відповідності, а точної відповідності не знайдено, повертається помилка (#Н/Д). Щоб формула повертала порожню або "не знайдено", або будь -яке інше значення замість значення помилки (#Н/Д), використовуйте функцію IFERROR з VLOOKUP.
Щоб скористатися функцією VLOOKUP, щоб повернути приблизний набір відповідностей: range_lookup = ІСТИНА. Ця опція доступна лише для числових значень. Значення потрібно сортувати в порядку зростання.
Щоб виконати горизонтальний пошук, скористайтеся функцією HLOOKUP.
Приклади VLOOKUP у VBA
Ви також можете використовувати функцію VLOOKUP у VBA. Тип:application.worksheetfunction.vlookup (lookup_value, table_array, col_index_num, range_lookup)
Для аргументів функції (lookup_value тощо) ви можете або ввести їх безпосередньо у функцію, або визначити змінні для використання замість них.
Повернення до списку всіх функцій Excel
