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

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

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

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

Рішення.

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

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

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

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

P.S.1

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

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