Загальна формула
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Резюме
Для пошуку значень за допомогою INDEX та MATCH, використовуючи кілька критеріїв, ви можете використовувати формулу масиву. У наведеному прикладі формула в H8 має вигляд:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Примітка: це формула масиву, і її потрібно вводити за допомогою control + shift + enter, за винятком Excel 365.
Пояснення
Це більш досконала формула. Основи див. У розділі Як використовувати INDEX та MATCH.
Зазвичай формула INDEX MATCH конфігурується з набором MATCH для перегляду діапазону в один стовпець та забезпечення відповідності на основі заданих критеріїв. Без об'єднання значень у допоміжному стовпці або в самій формулі неможливо подати більше одного критерію.
Ця формула працює навколо цього обмеження, використовуючи логічну логіку для створення масиву одиниць та нулів для представлення рядків, що відповідають усім 3 критеріям, а потім за допомогою MATCH відповідає першому знайденому 1. Тимчасовий масив одиниць та нулів генерується за допомогою цього фрагмента:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Тут ми порівнюємо елемент у H5 з усіма елементами, розмір у H6 з усіма розмірами та колір у H7 з усіма кольорами. Початковий результат - три масиви результатів TRUE / FALSE, таких як:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Порада: використовуйте F9, щоб переглянути ці результати. Просто виберіть вираз у рядку формул і натисніть F9.
Математична операція (множення) перетворює значення TRUE FALSE на 1s і 0s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Після множення ми маємо єдиний масив, такий:
(0;0;1;0;0;0;0)
який подається у функцію MATCH як масив підстановки зі значенням підстановки 1:
MATCH(1,(0;0;1;0;0;0;0))
На даний момент формула є стандартною формулою INDEX MATCH. Функція MATCH повертає значення 3 до INDEX:
=INDEX(E5:E11,3)
і INDEX повертає кінцевий результат $ 17,00.
Візуалізація масиву
Описані вище масиви може бути важко візуалізувати. На зображенні нижче показано основну ідею. Стовпці B, C та D відповідають даним у прикладі. Стовпець F створюється шляхом множення трьох стовпців разом. Це масив, переданий MATCH.
Немасивна версія
До цієї формули можна додати інший ІНДЕКС, уникаючи необхідності вводити як формулу масиву з керуванням + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
Функція INDEX може обробляти масиви спочатку, тому другий INDEX додається лише для того, щоб "зловити" масив, створений за допомогою логічної операції, і повернути той самий масив знову в MATCH. Для цього в INDEX налаштовано нуль рядків та один стовпець. Хитрість із нульовим рядком призводить до повернення INDEX стовпця 1 із масиву (який і так уже є одним стовпцем).
Чому ви хочете використовувати не-масивну версію? Іноді люди забувають ввести формулу масиву з контролем + shift + enter, і формула повертає неправильний результат. Отже, формула, що не є масивом, є більш «куленепробивною». Однак компроміс є більш складною формулою.
Примітка: У програмі Excel 365 немає необхідності вводити формули масиву спеціальним способом.
