Розглянемо як швидко об'єднати дві великі таблиці разом з колонками та рядками, тобто робити вибірку не по одному параметру (як функції VLOOKUP або HLOOKUP), а за двома відразу (використовуючи функції INDEX та MATCH).
Якщо ви знайомі з функцією VLOOKUP або її горизонтальним аналогом HLOOKUP, то повинні пам'ятати, що ці чудові функції шукають інформацію тільки по одному параметру, тобто в одновимірному масиві - по рядку або по стовпцю. А якщо нам необхідно вибирати дані з двовимірної таблиці за збігом відразу двох параметрів - і по рядку і по стовпцю одночасно? Давайте розглянемо кілька варіантів об'єднання таблиць.
Припустимо, що нам потрібно поєднати дві таблиці, які відображають кредитний портфель та портфель забезпечення:
Ми можемо почати використовувати функцію VLOOKUP, щоб поєднати кожний окремий стовпець, проте якщо у нас таблиці мають надзвичайно багато рядків та стовпців, то це занняття може перетворитися в справжню муку. Проте з цієї ситуації є досить простий вихід, оскільки Excel має такі дві чудові функції, як INDEX та MATCH з категорії Посилання та масиви
Гаразд, що нам необхідно зробити, щоб швидко поєднати дві таблиці? Давайте для початку скопіюємо шапку другої таблиці (ту, що ми будемо приєднувати) та вставимо її поруч з шапкою першої таблиці. По назві шапки функція MATCH буде видавати нам порядковий номер колонки, а по номеру угоди - необхідний нам порядковий номер рядка.
Фактично, ми хочемо знайти значення комірки з перетину певного рядка та стовпця в таблиці. Для наочності, розіб'ємо завдання на три етапи:
Отже, поєднуючи все вище перелічене в одну формулу, отримуємо для комірки F14 наступну формулу:
=INDEX(J2:M18; MATCH(A14; K2:K18; 0); MATCH(F1; J1:M1; 0))
Таким чином, розтягуючи нашу формулу на весь діапазон, ми правильно приєднаємо додаткові поля. Також потрібно звернути увагу, що при розтягуванні формули, нам потрібно зафіксувати діапазони знаком долара ($) (для фіксування можна використати клавішу F4), для пошуку по шапці фіксуємо тільки рядок (F$1), для пошуку по номерам угод - тільки колонку ($A14).
Злегка модифікуємо попередній приклад та, припустимо, що у нас є аналогічні таблиці, проте їх заголовки є об'єднаними, тому функція MATCH нам не допоможе визначити правильно порядковий номер колонки.
В даному випадку, ми можемо створити технічне поле над таблицею, та проставити номера колонок вручну. Тоді, ми зможемо скористатися вже звичною нам функцією VLOOKUP.
Отже, формула для нашого другого варіанту буде наступною:
=VLOOKUP($A3; $J$3:$M$19; E$1; 0)
Знову ж, не забуваємо фіксувати діапазон та відповідні пошукові поля знаком $, для того щоб формула корректно працювала та не видавала помилку при зміщенні діапазонів.