- Функція VLOOKUP в Excel використовується для пошуку значення в електронній таблиці.
- Синтаксис та аргументи такі = ПЕРЕГЛЯД(пошук_значення, таблиця пошуку, номер_столбця, [approximate_match] )
У цій статті пояснюється, як використовувати функцію VLOOKUP у всіх версіях Excel, включаючи Excel 2019 та Microsoft 365.
Змістовий покажчик
Що таке функція VLOOKUP?
Функція VLOOKUP в Excel використовується для пошуку чогось у таблиці. Якщо у вас є рядки даних, організовані за заголовками стовпців, VLOOKUP можна використовувати для пошуку значення за допомогою стовпця. Коли ви виконуєте VLOOKUP, ви говорите Excel спочатку знайти рядок, що містить дані, які ви хочете отримати, а потім повернути значення, яке знаходиться в певному стовпці в цьому рядку.
Синтаксис та аргументи функції VLOOKUP
Існує чотири можливі частини цієї функції:
= ПЕРЕГЛЯД(пошук_значення, таблиця пошуку, номер_столбця, [approximate_match] )
- пошук_значення це значення, яке ви шукаєте. Це має бути в першій колонці таблиця пошуку.
- таблиця пошуку – це діапазон, який ви шукаєте. Це включає пошук_значення.
- номер_столбця – число, яке представляє, скільки стовпців у таблиця пошуку, зліва, повинен бути стовпець, з якого VLOOKUP повертає значення.
- приблизний_збіг є необов’язковим і може бути будь-яким ІСТИНА або ПОМИЛКОВИЙ. Він визначає, чи потрібно знайти точну відповідність або приблизну відповідність. Якщо його не вказано, типовим значенням є ІСТИНА, тобто він знайде приблизний збіг.
Приклади функцій VLOOKUP
Ось кілька прикладів, що показують функцію VLOOKUP в дії:
Знайдіть значення поряд із словом із таблиці
= VLOOKUP («Лимони», A2: B5,2)
Це простий приклад функції VLOOKUP, де нам потрібно знайти, скільки лимонів у нас є на складі зі списку декількох предметів. Діапазон, який ми переглядаємо, такий A2: B5 а число, яке нам потрібно витягнути, знаходиться у стовпці 2 оскільки «На складі» – це друга колонка з нашого асортименту. Результат тут є 22.
Знайдіть номер працівника, використовуючи їх ім’я
= ПЕРЕГЛЯД (A8, B2: D7,3)
= ПЕРЕГЛЯД (A9, A2: D7,2)
Ось два приклади, коли ми пишемо функцію VLOOKUP дещо інакше. Вони обидва використовують подібні набори даних, але оскільки ми збираємо інформацію з двох окремих стовпців, 3 і 2, ми робимо це розрізнення в кінці формули – перший захоплює позицію людини в A8 (Finley), тоді як друга формула повертає ім’я, яке відповідає номеру працівника в A9 (819868). Оскільки формули посилаються на клітинки, а не на певний текстовий рядок, ми не можемо залишати лапки.
Використовуйте заяву IF, якщо з VLOOKUP
= IF (VLOOKUP (A2, Sheet4! A2: B5,2)> 10, «Ні», «Так»)
VLOOKUP також можна поєднувати з іншими функціями Excel і використовувати дані з інших аркушів. Ми робимо обидва в цьому прикладі, щоб визначити, чи потрібно нам замовляти більше елемента в стовпці А. Ми використовуємо функцію IF, щоб якщо значення в позиції 2 в Аркуш4! A2: B5 більше ніж 10, ми пишемо Немає щоб вказати, що нам не потрібно замовляти більше.
Знайдіть найближче число в таблиці
= ПЕРЕГЛЯД (D2, $ A $ 2: $ B $ 6,2)
У цьому останньому прикладі ми використовуємо VLOOKUP, щоб знайти відсоток знижки, який слід використовувати для різних оптових замовлень взуття. Знижка, яку ми шукаємо, знаходиться в стовпці D, а асортимент, який включає інформацію про знижку, є A2: B6, і в межах цього діапазону знаходиться стовпець 2 що містить знижку. Оскільки VLOOKUP не потрібно знаходити точну відповідність, приблизний_збіг залишається порожнім для позначення ІСТИНА. Якщо точного збігу не знайдено, функція використовує наступну меншу суму. Ви бачите, що в першому прикладі з 60 замовлень знижка не знаходиться в таблиці зліва, тому використовується наступна менша сума в 50, що становить 75% знижки. Стовпець F – остаточна ціна, коли враховано знижку.
Помилки та правила VLOOKUP
Ось деякі речі, про які слід пам’ятати, використовуючи функцію VLOOKUP в Excel:
- Якщо пошук_значення – це текстовий рядок, він повинен бути оточений лапками.
- Excel повернеться # NO MATCH якщо VLOOKUP не може знайти результат.
- Excel повернеться # NO MATCH якщо в номері немає номера таблиця пошуку це більше або дорівнює пошук_значення.
- Excel повернеться #REF! якщо номер_столбця більше, ніж кількість стовпців у таблиця пошуку.
- пошук_значення завжди в крайньому лівому положенні таблиця пошуку і є позицією 1 при визначенні номер_столбця.
- Якщо ви вкажете ПОМИЛКОВИЙ для приблизний_збіг і точного збігу не знайдено, VLOOKUP повернеться # Не застосовується.
- Якщо ви вкажете ІСТИНА для приблизний_збіг і не знайдено точного збігу, повертається наступне менше значення.
- Слід використовувати несортовані таблиці ПОМИЛКОВИЙ для приблизний_збіг щоб було повернуто першу точну відповідність.
- Якщо приблизний_збіг є ІСТИНА або опущений, перший стовпець потрібно відсортувати за алфавітом або числом. Якщо його не відсортовано, Excel може повернути несподіване значення.
- Використання абсолютних посилань на клітинки дозволяє автоматично заповнювати формули без змін таблиця пошуку.
Інші функції, такі як VLOOKUP
VLOOKUP виконує вертикальний пошук, тобто він отримує інформацію шляхом підрахунку стовпців. Якщо дані організовані горизонтально, і ви хочете відрахувати рядки для отримання значення, ви можете скористатися функцією HLOOKUP. Функція XLOOKUP схожа, але працює в будь-якому напрямку.