Поєднання таблиць (VLOOKUP, ВПР)

Приклад задачі

Припустимо ми маємо дві даблиці з даними - таблицю кредитів та таблицю забезпечення:

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

Рішення

В Excel в стандартоному наборі функцій в категорії Lookup and reference є функція VLOOKUP. Ця функія шукає вказане значення (в нашому прикладі номер угоди) в крайній лівій колонці зазначеної таблиці (таблиці забезпечення) рухаючись з гори до низу та, знаходячи їх, відображає значення сусідньої комірки (тип забезпечення по кредиту). Схематично робота функції виглядає так:

Отож, використаємо функцію VLOOKUP. Виберіть комірку де вона буде введена (E2) і відкрийте майстра формул (меню Insert - Function). В категорії (Lookup and Reference) знайдіть функцію VLOOKUP та нажміть ОК. З'явиться вікно для введення аргументів функції:

Заповніть їх один за одним:

Lookup Value - номер угоди, яку функція буде шукати в крайній лівій колонці таблиці забезпечення. В нашому випадку - номер угоди "KF568" з клітинки С2.

Table Array - таблиця, з якої ми отримуватимемо шукані значення, це наша таблиця забезпеченя. Для посилання ми використаємо діапазон $G$2:$H$11 (діапазон фіксуємо знаком долара ($), для того, щоб він нам не зміщався до низу при копіюванні формули).

Column index number - порядковий номер (не буква!) колонки в таблиці забезпечення, з якої ми братимемо назву забезпечення. Перша колонка таблиці забезпечення має порядковий номер 1, отже, нам потрібно взяти тип забезпечення з другої колонки (номер 2).

Range Lookup - в це поле ми можемо ввести лише два значення: TRUE або FALSE:

Якщо ми введемо значення 0 або FALSE, то це насправді означає, що дозволений пошук тільки з точним співпадінням, тобто, якщо функція не знайде в таблиці забезпечення номер угоди (якщо він відсутній), тоді відобразиться помилка #N/A (немає даних).
Якщо ми введемо значення 1 або TRUE, це означатиме, що дозволено пошук не тільки точного, але й приблизного співпадіння. В більшості випадків, така приблизна підстановка може спричинити хибний результат, коли VLOOKUP підставлятиме значення інших угод. Отож, приблизний пошук вам варто використовувати лише для цифрових значень (попередньо відсортованих по зростанню).

Ось і все! Залишилось натиснути ОК і скопіювати введену функцію по всій колонці.

P.S.1

Функція VLOOKUP повертає помилку (#N/A) якщо:

Дозволено точний пошук (аргумент Range Lookup=0) і шуканого значення немає у таблиці (Table).
Дозволено приблизний пошук (Range Lookup=1), але в таблиці (Table), в якій ми шукаємо значення, назви не посортовані по зростанню.
Формат комірки, з якої ми беремо номер угоди (наприклад, С2 в нашому випадку) і формат комірки першої колонки (G2:G11) таблиці є різними (наприклад, цифровий та текстовий). Ця ситуація є особливо типова, коли використовуються цифрові коди замість текстових назв (номера рахунків, ідентифікаційні коди, дати тощо). В цьому випадку ви можете використовувати функції VALUE та TEXT для перетворення форматів даних. Це виглядає так:
=VLOOKUP(TEXT(C2);$G$2:$H$11;0).
Функція не знаходить значення тому, що в коді є пробіли та невидимі символи, що не друкуються (переноси стрічки тощо). В цьому випадку ви можете використати текстову функцію (TRIM) та (CLEAN) для їх видалення:
=VLOOKUP(TRIM(CLEAN(C2));$G$2:$H$11;0).

P.S.2

Для придушення повідомлення про помилку (#N/A), коли функція не може знайти точного співпадіння, ви можете використовувати функцію IFERROR.

Функція IFERROR перевіряє, чи помилка (#N/A) є результатом роботи VLOOKUP, і якщо так, то виводить пусту стрічку ("") або нуль, якщо ні - результат роботи VLOOKUP.

Статті по темі:

Покращена функція VLOOKUP (VLOOKUP2, ВПР2)

VLOOKUP для поєднання по двом умовам (VLOOKUP3, ВПР3)

Швидке об'єднання великих таблиць (VLOOKUP2D, ВПР2D)