Используйте INDEX(MATCH), чтобы найти значение на основе нескольких критериев

Question

Я использую INDEX(MATCH) для поиска значения в одном столбце, используя критерии из двух разных ячеек на другом листе. Вот формула, которую я пытаюсь использовать:

=INDEX(Sheet1!P:P,MATCH(1,(Sheet1!A:A=Sheet2!C$1)*(Sheet1!B:B=Sheet2!$B2),0))

Sheet1 - это массив, а столбец P содержит значения, которые я хочу получить по формуле. Столбец A в Sheet1 содержит значения для первого критерия, а столбец B в Sheet1 содержит значения для второго критерия. Критерии представлены в С1 и В2 Листа 2. Это изменится, когда ячейка будет скопирована. Кто-нибудь может увидеть какие-либо ошибки в этой формуле? Возвращается "значение недоступно для формулы или функции".

Answer 1

Это должно быть введено как формула массива¹, поэтому полные ссылки на столбцы должны быть сокращены до минимального размера, иначе вы столкнетесь с ненужной задержкой вычислений при обработке сотен тысяч пустых ячеек.

С текстом в столбце А,

=index(Sheet1!$P$1:index(Sheet1!$P:$P, match("zzz", Sheet1!$A:$A)),
   match(1, (Sheet1!$A$1:index(Sheet1!$A:$A, match("zzz", Sheet1!$A:$A))=Sheet2!C$1)*
            (Sheet1!$B$1:index(Sheet1!$B:$B, match("zzz", Sheet1!$A:$A))=Sheet2!$B2), 0))

С номерами или датами в столбце А,

=index(Sheet1!$P$1:index(Sheet1!$P:$P, match(1e99, Sheet1!$A:$A)),
   match(1, (Sheet1!$A$1:index(Sheet1!$A:$A, match(1e99, Sheet1!$A:$A))=Sheet2!C$1)*
            (Sheet1!$B$1:index(Sheet1!$B:$B, match(1e99, Sheet1!$A:$A))=Sheet2!$B2), 0))

У вас не было абсолютных эталонных якорей (например, $) в исходных ссылках диапазона Sheet1, но то, как вы настроили их для критериев из Sheet2, заставило меня поверить, что они требуются как для строки, так и для столбца.

Если у вас есть метки заголовков столбцов в строке 1, измените A1, B1 и P1 на A2, B2 и P2.

---

_{Form Формула массива должна быть завершена с помощью Ctrl + Shift + Enter↵. Если введено правильно, Excel с формулой в скобках (например, { и }). Вы не вводите скобки в себя. После правильного ввода в первую ячейку, они могут быть заполнены или скопированы или вправо, как и любая другая формула. Попробуйте уменьшить количество ссылок на полные столбцы до диапазонов, более точно представляющих экстенты ваших фактических данных. Формулы массива вычисляют циклы вычислений логарифмически, поэтому хорошей практикой является сужение указанных диапазонов до минимума. См. Рекомендации и примеры формул массива для получения дополнительной информации.}