Notice: This page requires JavaScript to function properly.
Please enable JavaScript in your browser settings or update your browser.Вивчайте Index, Match та Xmatch | Формули Excel
Формули Excel

Index, Match та Xmatch

Свайпніть щоб показати меню

XLOOKUP є рекомендованою функцією для пошуку в сучасних моделях Excel, але багато робочих книг у реальному світі все ще використовують INDEX та MATCH. У цьому розділі розглядається, як ці функції працюють разом, а також як XMATCH розширює цю логіку з більшою гнучкістю.

Структура MATCH

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value: значення для пошуку;
  • lookup_array: діапазон для пошуку;
  • [match_type]: спосіб співставлення;
  • 0: точний збіг;
  • 1: найбільше значення, менше або дорівнює шуканому значенню;
  • -1: найменше значення, більше або дорівнює шуканому значенню.

MATCH повертає позицію, а не саме значення.

Структура INDEX

=INDEX(array, row_num, [col_num])
  • array: масив, з якого повертається значення;
  • row_num: позиція рядка;
  • [col_num]: необов'язкова позиція стовпця.

INDEX отримує значення за позицією.

Комбінація INDEX та MATCH

=INDEX(Products[Price], MATCH("Keyboard", Products[Product], 0))
  • MATCH(...): знаходить позицію продукту;
  • INDEX(...): повертає значення на цій позиції.

Це відтворює поведінку пошуку без обмежень за напрямком.

Структура XMATCH

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
  • lookup_value: значення для пошуку;
  • lookup_array: діапазон для пошуку;
  • [match_mode]: логіка співставлення;
  • [search_mode]: напрямок пошуку.

XMATCH повертає позиції так само, як і MATCH, але додає сучасні засоби керування пошуком, подібні до XLOOKUP.

Крок 1 Перевірка MATCH окремо
expand arrow

На аркуші Summary введіть:

=MATCH("Laptop", Products[Product], 0)

Переконайтеся, що Excel повертає позицію Laptop у таблиці Products.

Крок 2 Перевірка INDEX окремо
expand arrow

Введіть:

=INDEX(Products[Price], 3)

Переконайтеся, що формула повертає значення на позиції 3.

Крок 3 Комбінація INDEX та MATCH
expand arrow

Введіть:

=INDEX(Products[Price], MATCH("Keyboard", Products[Product], 0))

Тепер формула динамічно отримує ціну відповідного продукту.

Крок 4 Використання структурованих посилань
expand arrow

Замініть фіксоване значення для пошуку на:

=INDEX(Products[Price], MATCH([@Product], Products[Product], 0))
  • [@Product]: значення продукту в поточному рядку;
  • Products[Product]: стовпець для пошуку;
  • Products[Price]: стовпець для повернення значення.

Тепер формула працює динамічно в структурі таблиці.

Крок 5 Перевірка XMATCH
expand arrow

Введіть:

=XMATCH("Gaming Chair", Products[Product])

Переконайтеся, що Excel повертає позицію відповідного рядка.

Крок 6 Двовимірний пошук
expand arrow

Введіть:

=INDEX(Products, XMATCH("Gaming Chair", Products[Product]), XMATCH("Cost", Products[#Headers]))
  • Перший XMATCH(...): отримує позицію рядка;
  • Другий XMATCH(...): отримує позицію стовпця;
  • INDEX(...): повертає значення на перетині.

Це створює повністю динамічну систему пошуку за рядком і стовпцем.

Крок 7 Перевірка динамічної гнучкості
expand arrow

Змініть значення продукту та стовпця у формулі.

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

1. Яка основна роль функції MATCH у комбінації INDEX/MATCH?

2. Чому потрібна функція INDEX при використанні MATCH?

3. Яка основна перевага використання INDEX з XMATCH для двовимірного пошуку?

question mark

Яка основна роль функції MATCH у комбінації INDEX/MATCH?

Виберіть правильну відповідь

question mark

Чому потрібна функція INDEX при використанні MATCH?

Виберіть правильну відповідь

question mark

Яка основна перевага використання INDEX з XMATCH для двовимірного пошуку?

Виберіть правильну відповідь

Все було зрозуміло?

Як ми можемо покращити це?

Дякуємо за ваш відгук!

Секція 1. Розділ 16

Запитати АІ

expand

Запитати АІ

ChatGPT

Запитайте про що завгодно або спробуйте одне із запропонованих запитань, щоб почати наш чат

Index, Match та Xmatch

XLOOKUP є рекомендованою функцією для пошуку в сучасних моделях Excel, але багато робочих книг у реальному світі все ще використовують INDEX та MATCH. У цьому розділі розглядається, як ці функції працюють разом, а також як XMATCH розширює цю логіку з більшою гнучкістю.

Структура MATCH

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value: значення для пошуку;
  • lookup_array: діапазон для пошуку;
  • [match_type]: спосіб співставлення;
  • 0: точний збіг;
  • 1: найбільше значення, менше або дорівнює шуканому значенню;
  • -1: найменше значення, більше або дорівнює шуканому значенню.

MATCH повертає позицію, а не саме значення.

Структура INDEX

=INDEX(array, row_num, [col_num])
  • array: масив, з якого повертається значення;
  • row_num: позиція рядка;
  • [col_num]: необов'язкова позиція стовпця.

INDEX отримує значення за позицією.

Комбінація INDEX та MATCH

=INDEX(Products[Price], MATCH("Keyboard", Products[Product], 0))
  • MATCH(...): знаходить позицію продукту;
  • INDEX(...): повертає значення на цій позиції.

Це відтворює поведінку пошуку без обмежень за напрямком.

Структура XMATCH

=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])
  • lookup_value: значення для пошуку;
  • lookup_array: діапазон для пошуку;
  • [match_mode]: логіка співставлення;
  • [search_mode]: напрямок пошуку.

XMATCH повертає позиції так само, як і MATCH, але додає сучасні засоби керування пошуком, подібні до XLOOKUP.

Крок 1 Перевірка MATCH окремо
expand arrow

На аркуші Summary введіть:

=MATCH("Laptop", Products[Product], 0)

Переконайтеся, що Excel повертає позицію Laptop у таблиці Products.

Крок 2 Перевірка INDEX окремо
expand arrow

Введіть:

=INDEX(Products[Price], 3)

Переконайтеся, що формула повертає значення на позиції 3.

Крок 3 Комбінація INDEX та MATCH
expand arrow

Введіть:

=INDEX(Products[Price], MATCH("Keyboard", Products[Product], 0))

Тепер формула динамічно отримує ціну відповідного продукту.

Крок 4 Використання структурованих посилань
expand arrow

Замініть фіксоване значення для пошуку на:

=INDEX(Products[Price], MATCH([@Product], Products[Product], 0))
  • [@Product]: значення продукту в поточному рядку;
  • Products[Product]: стовпець для пошуку;
  • Products[Price]: стовпець для повернення значення.

Тепер формула працює динамічно в структурі таблиці.

Крок 5 Перевірка XMATCH
expand arrow

Введіть:

=XMATCH("Gaming Chair", Products[Product])

Переконайтеся, що Excel повертає позицію відповідного рядка.

Крок 6 Двовимірний пошук
expand arrow

Введіть:

=INDEX(Products, XMATCH("Gaming Chair", Products[Product]), XMATCH("Cost", Products[#Headers]))
  • Перший XMATCH(...): отримує позицію рядка;
  • Другий XMATCH(...): отримує позицію стовпця;
  • INDEX(...): повертає значення на перетині.

Це створює повністю динамічну систему пошуку за рядком і стовпцем.

Крок 7 Перевірка динамічної гнучкості
expand arrow

Змініть значення продукту та стовпця у формулі.

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

Все було зрозуміло?

Як ми можемо покращити це?

Дякуємо за ваш відгук!

Секція 1. Розділ 16
some-alt