Пошук за кількома критеріями за допомогою INDEX та MATCH

При роботі з великою базою даних в таблиці Excel з кількома стовпцями та підписами рядків завжди важко знайти те, що відповідає кільком критеріям. У цьому випадку можна використовувати формулу масиву з the ІНДЕКС та MATCH fрозпорядження.

Як виконати пошук за кількома критеріями?

Щоб дізнатися, продукт тобто білий та середа-розмір з ціною $18 як показано на малюнку вище, ви можете скористатися перевагами логічної логіки для створення масиву з 1 і 0, щоб показати рядки, що відповідають критеріям. Після цього функція MATCH знайде позицію першого рядка, яка відповідає всім критеріям. Після цього INDEX знайде відповідний ідентифікатор товару в тому ж рядку.

Родовий синтаксис

=INDEX(return_range,MATCH(1,(criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))

√ Примітка: Це формула масиву, яка вимагає введення з Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у .

Щоб знайти продукт, який є білий та середа-розмір з ціною $18, скопіюйте або введіть формулу нижче в клітинку Н8 і натисніть Ctrl + Shift + Що натомість? Створіть віртуальну версію себе у щоб отримати результат:

= ІНДЕКС (B5: B10, MATCH (1, ("Білий"=C5: C10) * ("Середній"=D5: D10) * (18=E5: E10), 0))

Або використовуйте посилання на клітинку, щоб зробити формулу динамічною:

= ІНДЕКС (B5: B10, MATCH (1, (H4=C5: C10) * (H5=D5: D10) * (H6=E5: E10), 0))

Пояснення формули

=INDEX(B5:B10,MATCH(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))

• (H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10): Формула порівнює колір осередку H4 проти всіх кольорів в асортименті C5: C10; порівнює розмір в H5 проти всіх розмірів в D5: D10; порівнює ціну в H6 проти всіх цін в E5: E10. Початковий результат такий:
  {ІСТИНА;ХИБНІСТЬ;ІСТИНА;ХИБНІСТЬ;ІСТИНА;ХИБНІСТЬ}*{ХИБНІСТЬ;ХИБНІСТЬ;ІСТИНА;ІСТИНА;ІСТИНА;ХИБНІСТЬ}*{ХИБНІСТЬ;ХИБНІСТЬ;ХИБНІСТЬ;ІСТИНА;ІСТИНА;ХИБНІСТЬ}.
  Множення призведе до перетворення істинних та неправдивих значень на 1 і 0:
  {1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
  Після множення у нас буде єдиний такий масив:
  {0;0;0;0;1;0}.
• МАТЧ (1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0) = МАТЧ (1,{0;0;0;0;1;0}, 0): Тип збігу 0 просить функцію MATCH знайти точну відповідність. Після цього функція поверне положення 1 в масиві {0;0;0;0;1;0}, Яка є 5.
• ІНДЕКС (B5: B10,МАТЧ (1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0)) = ІНДЕКС (B5: B10,5): Функція INDEX повертає 5це значення в діапазоні ідентифікаторів товару B5: B10, Яка є 30005.

Супутні функції

Пов’язані формули