✅ Як користуватися функцією VLOOKUP

Зміст

Функція VLOOKUP є однією з найпопулярніших функцій у Excel. Ця сторінка містить багато простих для наслідування прикладів VLOOKUP.

Точний збіг

Більшість часу ви шукаєте точну відповідність, коли використовуєте функцію VLOOKUP в Excel. Давайте поглянемо на аргументи функції VLOOKUP.

1. Нижче наведена функція VLOOKUP шукає значення 53 (перший аргумент) у крайньому лівому стовпці червоної таблиці (другий аргумент).

2. Значення 4 (третій аргумент) повідомляє функції VLOOKUP повернути значення в тому ж рядку з четвертого стовпця червоної таблиці.

Примітка: Логічне значення FALSE (четвертий аргумент) повідомляє функції VLOOKUP повернути точну відповідність. Якщо функція VLOOKUP не може знайти значення 53 у першому стовпці, вона поверне помилку #N/A.

3. Ось ще один приклад. Замість того, щоб повертати зарплату, функція VLOOKUP нижче повертає прізвище (третій аргумент має значення 3) ідентифікатора 79.

Приблизний збіг

Давайте розглянемо приклад функції VLOOKUP у режимі наближеної відповідності (четвертий аргумент встановлено як TRUE).

1. Нижче наведена функція VLOOKUP шукає значення 85 (перший аргумент) у крайньому лівому стовпці червоної таблиці (другий аргумент). Є лише одна проблема. У першому стовпці немає значення 85.

2. На щастя, булеве ІСТИНА (четвертий аргумент) повідомляє функції VLOOKUP повернути приблизне збіг. Якщо функція VLOOKUP не може знайти значення 85 у першому стовпці, вона поверне найбільше значення менше 85. У цьому прикладі це буде значення 80.

3. Значення 2 (третій аргумент) повідомляє функції VLOOKUP повернути значення в тому ж рядку з другого стовпця червоної таблиці.

Примітка: завжди сортуйте крайній лівий стовпець червоної таблиці у порядку зростання, якщо ви використовуєте функцію VLOOKUP у режимі наближеної відповідності (четвертий аргумент встановлено як TRUE).

Vlookup виглядає правильно

Функція VLOOKUP завжди шукає значення в крайньому лівому стовпці таблиці і повертає відповідне значення зі стовпця праворуч.

1. Наприклад, функція VLOOKUP нижче шукає ім’я та повертає прізвище.

2. Якщо змінити номер індексу стовпця (третій аргумент) на 3, функція VLOOKUP шукає ім’я та повертає заробітну плату.

Примітка: у цьому прикладі функція VLOOKUP не може шукати ім’я та повертати ідентифікатор. Функція VLOOKUP дивиться лише праворуч. Не хвилюйтесь, ви можете скористатися INDEX та MATCH у Excel, щоб виконати пошук ліворуч.

Перший матч

Якщо крайній лівий стовпець таблиці містить дублікати, функція VLOOKUP відповідає першому екземпляру. Наприклад, погляньте на функцію VLOOKUP нижче.

Пояснення: функція VLOOKUP повертає зарплату Мії Кларк, а не Мії Рід.

Vlookup не чутливий до регістру

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

Пояснення: функція VLOOKUP не чутлива до регістру, тому вона шукає MIA або Mia або mia або miA тощо. В результаті функція VLOOKUP повертає заробітну плату Міа Кларк (перша інстанція). Використовуйте INDEX, MATCH та EXACT в Excel для виконання пошуку з урахуванням регістру.

Кілька критеріїв

Бажаєте шукати значення на основі кількох критеріїв? Використовуйте INDEX та MATCH в Excel, щоб виконати пошук у двох стовпцях.

Примітка. Формула масиву, наведена вище, шукає заробітну плату Джеймса Кларка, а не Джеймса Сміта, не Джеймса Андерсона.

#Немає помилки

Якщо функція VLOOKUP не може знайти відповідність, вона повертає помилку #N/A.

1. Наприклад, функція VLOOKUP нижче не може знайти значення 28 у крайньому лівому стовпці.

2. Якщо хочете, ви можете скористатися функцією IFNA, щоб замінити помилку #N/A на дружнє повідомлення.

Примітка: функція IFNA була представлена в Excel 2013. Якщо ви використовуєте Excel 2010 або Excel 2007, просто замініть IFNA на IFERROR. Пам’ятайте, що функція IFERROR фіксує й інші помилки. Наприклад, #NAME? помилка, якщо ви випадково написали слово VLOOKUP.

Кілька таблиць пошуку

Під час використання функції VLOOKUP в Excel ви можете мати кілька таблиць пошуку. Ви можете використовувати функцію IF, щоб перевірити, чи виконується умова, і повернути одну таблицю пошуку, якщо TRUE, а іншу таблицю пошуку, якщо FALSE.

1. Створіть два іменовані діапазони: Таблиця1 та Таблиця2.

2. Виберіть клітинку E4 і введіть функцію VLOOKUP, показану нижче.

Пояснення: бонус залежить від ринку (Великобританія чи США) та суми продажів. Другий аргумент функції VLOOKUP робить свою справу. Якщо у Великобританії, функція VLOOKUP використовує Таблицю1, якщо США, функція VLOOKUP використовує Таблицю2. Встановіть четвертий аргумент функції VLOOKUP на TRUE, щоб повернути приблизне збіг.

3. Натисніть Enter.

4. Виберіть комірку E4, клацніть праворуч у нижньому кутку комірки E4 і перетягніть її вниз до комірки E10.

Примітка: наприклад, Уокер отримує бонус у розмірі 1500 доларів. Оскільки ми використовуємо іменовані діапазони, ми можемо легко скопіювати цю функцію VLOOKUP в інші комірки, не турбуючись про посилання на клітинки.

Індекс та відповідність

Замість використання VLOOKUP використовуйте INDEX та MATCH. Для виконання розширеного пошуку вам знадобляться INDEX та MATCH. Можливо, на цьому етапі це ще один крок для вас, але він показує вам одну з багатьох інших потужних формул, які може запропонувати Excel.