Для тих з вас, хто добре розбирається в Excel, ви, швидше за все, добре знайомі з функцією VLOOKUP . Функція VLOOKUP використовується для пошуку значення в іншій комірці на основі деякого відповідного тексту в одному рядку.
Якщо ви все ще новачок у функції VLOOKUP, ви можете перевірити мій попередній пост про те, як використовувати VLOOKUP в Excel.
Настільки ж потужним, як VLOOKUP, є обмеження щодо того, яким чином має бути структурована відповідна таблиця, щоб формула працювала.
Ця стаття покаже вам обмеження, де VLOOKUP не може бути використаний, і ввести іншу функцію в Excel з назвою INDEX-MATCH, що може вирішити проблему.
INDEX MATCH Приклад Excel
Використовуючи наступний приклад електронної таблиці Excel, ми маємо список імен власників автомобілів та назви автомобіля. У цьому прикладі ми намагатимемося отримати ідентифікатор автомобіля на основі моделі автомобіля, зазначеної під кількома власниками, як показано нижче:
На окремому аркуші називається CarType, ми маємо просту базу даних автомобілів з ідентифікатором, моделлю автомобіля та кольором .
За допомогою цієї установки таблиці функція VLOOKUP може працювати, лише якщо дані, які ми хочемо отримати, розташовані на стовпчику праворуч від того, що ми намагаємося відповідати (поле моделі автомобіля ).
Іншими словами, з цією структурою таблиці, оскільки ми намагаємося зрівняти її з моделлю автомобіля, єдиною інформацією, яку ми можемо отримати, є Color (Не ідентифікатор, оскільки стовпець ID розташований ліворуч від стовпця моделі автомобіля ).
Це пов'язано з тим, що з VLOOKUP значення пошуку має з'являтися в першому стовпці, а стовпці пошуку - праворуч. У нашому прикладі жодна з цих умов не зустрічається.
Доброю новиною є те, що INDEX-MATCH зможе допомогти нам у цьому. На практиці це фактично поєднує дві функції Excel, які можуть працювати індивідуально: функція INDEX і функція MATCH .
Однак для цілей цієї статті ми будемо говорити лише про комбінацію цих двох з метою тиражування функції VLOOKUP .
Формула може здатися трохи довгою і спочатку страшною. Однак, як тільки ви використовували його кілька разів, ви дізнаєтеся синтаксис напам'ять.
Це повна формула в нашому прикладі:
= INDEX (CarType! $ A $ 2: $ A $ 5, MATCH (B4, CarType! $ B $ 2: $ B $ 5, 0))
Ось розбивка для кожного розділу
= INDEX ( - "=" позначає початок формули в комірці, а INDEX - перша частина функції Excel, яку ми використовуємо.
$ A $ 2: $ A $ 5 - стовпці на аркуші CarType, де містяться дані, які ми хотіли б отримати. У цьому прикладі ідентифікатор кожної моделі автомобіля.
MATCH ( - друга частина функції Excel, яку ми використовуємо.
B4 - клітина, яка містить текст пошуку, який ми використовуємо ( модель автомобіля ) .
$ B $ 2: $ B $ 5 - стовпці на аркуші CarType з даними, які ми будемо використовувати для відповідності тексту пошуку.
0)) - Вказувати, що текст пошуку повинен точно збігатися з текстом у відповідній колонці (тобто CarType! $ B $ 2: $ B $ 5 ). Якщо точно не знайдено, формула повертає # N / A.
Примітка : запам'ятайте подвійні дужки закриття в кінці цієї функції “))” і кому між аргументами.
Особисто я відійшов від VLOOKUP і тепер використовую INDEX-MATCH, оскільки він здатний робити більше, ніж VLOOKUP.
Функції INDEX-MATCH також мають інші переваги порівняно з VLOOKUP :
- Швидше розрахунки
Коли ми працюємо з великими наборами даних, де сам розрахунок може зайняти багато часу через багато функцій VLOOKUP, ви побачите, що як тільки ви заміните всі ці формули на INDEX-MATCH, загальний розрахунок буде обчислено швидше.
- Немає необхідності підраховувати відносні стовпці
Якщо наша довідкова таблиця має ключовий текст, який ми хочемо шукати в стовпці C, і дані, які нам потрібно отримати, знаходяться в стовпці AQ, нам потрібно знати / підраховувати кількість стовпців між стовпцем C і стовпцем AQ при використанні VLOOKUP .
За допомогою функцій INDEX-MATCH ми можемо безпосередньо вибрати індексний стовпець (тобто стовпець AQ), де нам потрібно отримати дані і вибрати стовпець, який буде співпадати (тобто стовпець C).
- Це виглядає більш складним
VLOOKUP є досить поширеним в даний час, але мало хто знає про використання функцій INDEX-MATCH разом.
Більш довгий рядок у функції INDEX-MATCH допомагає вам виглядати експертом у роботі з складними та просунутими функціями Excel. Насолоджуйтесь!
