ПЕРЕГЛЯД З МАТЧОМ | Створіть гнучку формулу за допомогою VLOOKUP MATCH

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

Поєднуйте VLOOKUP із Match

Формула vlookup є найбільш часто використовуваною функцією, яка використовується для пошуку та повернення одного і того ж значення у вказаному індексі стовпця, або значення з іншого індексу стовпця з посиланням на відповідне значення з першого стовпця. Основна проблема, з якою стикається використання vlookup, полягає в тому, що вказаний індекс стовпця є статичним і не має динамічної функціональності. Особливо, коли ви працюєте над кількома критеріями, що вимагає вручну змінити індекс посилального стовпця. Тим самим ця потреба задовольняється за допомогою формули “MATCH”, щоб мати кращий зчеп або контроль часто змінюваного індексу стовпця у формулі VLOOKUP.

VLookup та формула збігу

№1 - Формула VLOOKUP

Формула функції VLOOKUP в Excel

Тут усі аргументи, які потрібно ввести, є обов’язковими.

  • Lookup_value - Тут слід ввести посилальну клітинку або текст із подвійними лапками, щоб визначити їх у діапазоні стовпців.
  •   Табличний масив - Цей аргумент вимагає введення діапазону таблиці, де слід шукати значення Lookup_value, а дані, які потрібно отримати, знаходяться в певному діапазоні стовпців.
  • Col_index_num - У цьому аргументі потрібно ввести номер індексу стовпця або підрахунок стовпця з першого стовпця посилання, з якого відповідне значення потрібно витягнути з тієї ж позиції, що і значення, яке шукається в першому стовпці.
  • [Range_lookup] - Цей аргумент надасть два варіанти.
  • TRUE - Приблизний збіг: - Аргумент можна ввести як TRUE або числовий “1”, що повертає приблизний збіг, що відповідає посилальному стовпцю або першому стовпцю. Більше того, значення в першому стовпці масиву таблиці повинні бути відсортовані за зростанням.
  • FALSE - Точна відповідність: - Тут аргумент, який потрібно ввести, може бути FALSE або числовим “0”. Цей параметр повертає лише точну відповідність значення, яке відповідає ідентифікації, з позиції в діапазоні першого стовпця. Неможливість пошуку значення з першого стовпця призведе до повідомлення про помилку "# N / A".

No2 - Формула матчу

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

Усі аргументи в синтаксисі є обов’язковими.

  • Lookup_value - Тут аргументом може бути посилання на клітинку значення або текстовий рядок із подвійними лапками, положення комірки яких потрібно витягнути.
  • Lookup_array - потрібно ввести діапазон масиву для таблиці, значення чи вміст комірки якого потрібно ідентифікувати.
  • [тип відповідності] - Цей аргумент надає три варіанти, як пояснено нижче.
  • «1-менше ніж» - тут аргументом, який потрібно ввести, є числовий «1», який поверне значення, яке менше або дорівнює значенню підстановки. А також масив підстановки повинен бути відсортований за зростанням.
  • “0-Точне збіг” - тут аргумент, який потрібно ввести, повинен бути цифровим “0”. Цей параметр поверне точну позицію відповідного значення пошуку. Однак масив підстановки може бути в будь-якому порядку.
  • “-1-Більше, ніж” -  аргумент, який потрібно ввести, повинен бути цифровим “-1”. Третій варіант знаходить найменше значення, яке більше або дорівнює значенню підстановки. Тут порядок для масиву підстановки повинен бути розміщений у порядку зменшення.

# 3 - ПЕРЕГЛЯД із формулою МАТЧ

= VLOOKUP (lookup_value, table_array, MATCH (lookup_value, lookup_array, [match_type]), [діапазон пошуку])

Як використовувати VLOOKUP із формулою відповідності в Excel?

Наведений нижче приклад допоможе зрозуміти функціонування формули vlookup та match при складанні.

Завантажити цей VLookup з шаблоном Excel Match можна тут - VLookup із шаблоном Excel Match

Розгляньте таблицю даних нижче, яка описує технічні характеристики даного автомобіля, який потрібно придбати.

Щоб отримати ясність комбінованої функції для функції vlookup та match, давайте зрозуміємо, як працює окрема формула, а потім прийдемо до результатів матчів vlookup, коли їх складемо.

Крок No1 - Давайте застосуємо формулу vlookup на індивідуальному рівні, щоб отримати результат.

Результат показаний нижче:

Тут значення пошуку позначається $ B9, що є моделлю “E”, а масив пошуку задається як діапазон таблиці даних з абсолютним значенням “$”, індекс стовпця - стовпець “4”, що є підрахунком для стовпець "Тип" і пошук діапазону отримує точну відповідність.

Таким чином, застосовується наступна формула для повернення значення для стовпця "Паливо".

Результат показаний нижче:

Тут значення підстановки із абсолютним рядком “$”, застосованим до значення підстановки та запиту_масиву, допомагає виправити посилальну комірку, навіть якщо формула копіюється в іншу комірку. У стовпці “Паливо” нам потрібно змінити індекс стовпця на “5”, оскільки змінюється значення, з якого потрібні дані для отримання.

Крок No2 -  Тепер застосуємо формулу Match, щоб отримати позицію для заданого значення пошуку.

Результат показаний нижче:

Як видно з наведеного вище знімка екрана, тут ми намагаємося отримати позицію стовпця з масиву таблиці. У цьому випадку номер стовпця, який потрібно витягнути, називається коміркою C8, тобто стовпцем “Тип”, а діапазон пошуку, який потрібно здійснити, задається як діапазон заголовків стовпців, а тип відповідності отримує точну відповідність як 0 ”.

Таким чином, таблиця нижче дасть бажаний результат для позицій стовпця «Паливо».

Тепер тут стовпець, який потрібно шукати, отримує клітинку D8, а бажаний індекс стовпця повертається як “5”.

Крок №3 - Тепер формула Match буде використана у функції vlookup для отримання значення з ідентифікованої позиції стовпця.

Результат показаний нижче:

У наведеній вище формулі функція збігу розміщується замість параметра індексу стовпця функції vlookup. Тут функція збігу ідентифікує еталонну комірку значення пошуку “C8” і повертає номер стовпця через заданий масив таблиці. Ця позиція стовпця служитиме ціллю як вхід для аргументу індексу стовпця у функції vlookup. Що, у свою чергу, допоможе vlookup визначити значення, яке потрібно повернути з результуючого номера індексу стовпця?

Подібним чином ми застосували vlookup із формулою відповідності і для стовпця "Паливо".

Результат показаний нижче:

Таким чином, ми можемо застосувати цю комбіновану функцію до інших стовпців "Тип" та "Паливо".

Що слід пам’ятати

  • VLOOKUP можна застосовувати до значень пошуку лише в його передній лівій частині. Будь-які значення, які потрібно шукати в правій частині таблиці даних, повернуть значення помилки "# N / A".
  • Діапазон table_array, введений у другому аргументі, повинен бути абсолютним посиланням на клітинку “$”, це збереже фіксований діапазон табличного масиву при застосуванні формули пошуку до інших комірок, інакше комірки посилання для діапазону масиву таблиці змістяться до наступної комірки посилання.
  • Значення, введене у значення підстановки, не повинно бути меншим за найменше значення в першому стовпці масиву таблиці, інакше функція поверне значення помилки "# N / A".
  • Перш ніж застосовувати приблизний збіг “TRUE” або “1” в останньому аргументі, завжди пам’ятайте про сортування масиву таблиці за зростанням.
  • Функція збігу повертає лише значення значення в масиві таблиці vlookup і не повертає значення.
  • Якщо функція збігу не може визначити позицію значення пошуку в масиві таблиці, тоді формула повертає значення "# N / A" у значенні помилки.
  • Функції перегляду та збігу не враховують регістр, коли збігається значення підстановки з відповідним текстовим значенням у масиві таблиці.