XLOOKUP с гибким столбцом для return_array и не уникальными значениями в search_array

Я просто сделаю это с помощью вспомогательных столбцов и буду использовать формулы шаг за шагом:

Сначала в ячейку N5 введите число 0.

В ячейку N6 напишите формулу ниже: =XMATCH(K6,DROP($H$3:$H$17,O6,0),0,1).

В ячейку O6 напишите формулу ниже: =SUM(--($K$5:K5=K6)*($N$5:N5))

Перетащите формулы в столбце N и столбце O, вы получите следующее:

Вот что делает столбец O:

например, ячейка O7 = 2, что означает, что строка 2 в H3:H17 имеет значение = 400, поэтому, чтобы найти «следующие» 400 в H3:H17, нам нужно удалить первые две строки в H3:H17, прежде чем мы выполним XMATCH. .

Ячейка N7 = 8; это означает, что 8-я строка в H5: H17 имеет значение = 400. (мы удаляем первые две строки, поэтому массив поиска имеет вид H5:H17 вместо H3:H17).

Наконец, мы получаем окончательные результаты в столбце P.

Формула в ячейке P6:

=INDEX(DROP($I$3:$I$17,O6,0),N6,1)

Вы можете перетащить его вниз.

Если вы хотите отображать значения на основе заголовка в M5, просто измените формулу ниже: (замените I3:I17 на XLOOKUP($M$5,$A$1:$I$1,$A$3:$I$17))

=INDEX(DROP(XLOOKUP($M$5,$A$1:$I$1,$A$3:$I$17),O6,0),N6,1)

Вот что вы можете сделать для достижения желаемого результата, используя одну формулу динамического массива:

=LET(
     _A, K6:K14,
     _B, H3:H17,
     _RollingA, MAP(_A,LAMBDA(α,COUNTIF(α:K6,α))),
     _RollingB, MAP(_B,LAMBDA(α,COUNTIF(α:H3,α))),
     XLOOKUP(_A&"|"&_RollingA,_B&"|"&_RollingB,XLOOKUP(M5,A1:I1,A3:I17)))

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

Чтобы немного объяснить приведенное выше решение, мы используем функцию MAP() для создания накопительного/скользящего/пробегающего счетчика для соответствующих массивов, которые имеют дубликаты, а именно. Выбранный один и массив поиска, чтобы создать уникальную последовательность или искомое значение для получения желаемого результата.

Или используйте, создав собственный LAMBDA():

=LET(
     α, K6:K14,
     δ, H3:H17,
     ƒx, LAMBDA(ε,φ, MAP(ε, LAMBDA(Σ, COUNTIF(φ:Σ,Σ)))),
     XLOOKUP(α&"|"&ƒx(α,K6),δ&"|"&ƒx(δ,H3),
     XLOOKUP(M5,A1:I1,A3:I17,""),""))

Немного похожий метод, примененный мной здесь Отображать результаты ФИЛЬТРА в том же порядке, что и значения в диапазоне критериев

Формула в M6:

=MAP(K6:K14,LAMBDA(x,@DROP(TOCOL(FILTER(IFS(E3:H17=x,F3:I17),F1:I1=M5),3),COUNTIF(K6:x,x)-1)))

Аналогично другим методам, то есть с использованием бегущих частот (с SUM):

=LAMBDA(h_, k_, data_range, col_headers, header_for_match,
    LET(
        i_index, XMATCH(header_for_match, col_headers),
        i_, INDEX(data_range, , i_index),
        freq, LAMBDA(arr,
            LET(
                indices, SEQUENCE(ROWS(arr)),
                MAP(
                    indices,
                    LAMBDA(i,
                        SUM(IF(TAKE(arr, i) = INDEX(arr, i, ), 1, 0))
                    )
                )
            )
        ),
        h_freq, h_ & " " & freq(h_),
        k_freq, k_ & " " & freq(k_),
        xlook, XLOOKUP(k_freq, h_freq, i_),
        formatted, IF(
            RIGHT(header_for_match, 2) = "-M",
            TEXT(xlook, "0%"),
            xlook
        ),
        formatted
    )
)(H3:H17, K6:K14, A3:I17, A1:I1, M5)