Ошибка расчета индекса / субпродукта

Question

Я работал над проблемой Excel в течение 24 часов. У меня есть формула, которая почти делает то, что я хочу, но она не возвращает то, что, по-моему, она должна вернуть:

=INDEX(Data!D:D,SUMPRODUCT((Data!C:C=Total!A5)*(Data!A:A="Total")*(Data!B:B="Total")*(ROW(Data!D:D))))

Я представляю строку для индексации столбца D в данных! Рабочий лист должен быть фактической строкой, в которой происходит сопоставление, но ДЛЯ КАЖДОГО ЗНАЧЕНИЯ, НО ПЕРВОГО ЗНАЧЕНИЯ, возвращаемое значение получается из 3 строк ниже него.

Да, поэтому для первой ячейки, в которой я применяю эту формулу, я получаю правильное значение из столбца D данных !. Но для каждой последующей ячейки с формулой я возвращаю значение в столбце D данных! 3 ряда ниже спички.

Я пробовал каждую комбинацию вычитания 3 из формулы (чтобы вернуть правильный ряд), но безрезультатно. Однако это не объясняет, почему первая ячейка, в которой применяется формула, возвращает ожидаемое значение!

Любая помощь очень ценится, конечно ...

Answer 1

Мне не совсем понятно, хотите ли вы найти первое, второе и т.д. Совпадения с Total!A5 или если вы пытаетесь найти первое совпадение с A5, затем первое совпадение с A6 и т.д. Это предлагаемое решение для первого.

Я предпочитаю использовать метод математического исключения для отлова множественных возвратов по совпадениям. Если вы устраняете то, что не соответствует, то то, что остается, должно быть набором совпадающих результатов. Я также достаточно нетерпелив, что нахожу отставание в вычислениях полных ссылок на столбцы с SUMPRODUCT раздражающим, поэтому я вырубил ваши диапазоны ссылок на первые ~ 1К строк.

=IFERROR(INDEX(Data!$D$2:$D$999, SMALL(INDEX(ROW($1:$998)+((Data!$C$2:$C$999<>Total!$A$5)+(Data!$A$2:$A$999<>"Total")+(Data!$B$2:$B$999<>"Total"))*1E+99,,),ROW(1:1))),"")

Это стандартная (не массив) формула. Заполните по мере необходимости, чтобы поймать все соответствующие возвращения. Когда вы расшифруете это для своих собственных целей, помните, что ROW(1:998) - это позиция в Data!D2:D999 , а не фактический номер строки на листе. ROW(1:1) - просто счетчик, который увеличивается по мере заполнения. Иногда COUNTIF более уместен и может использоваться для получения нескольких совпадающих возвратов для нескольких значений поиска.