Como aplicar a função MATCH() apenas a um intervalo filtrado? Ou existe outra maneira de fazer isso?

Se você estiver usando MS365, poderá tornar a fórmula acima mais curta usando LET()eSEQUENCE()

Para versões mais antigas do Excel ->

• Fórmula usada na célulaG5

=MATCH(1,IF(SUBTOTAL(3,OFFSET(Sheet1!$A$2:$A$7,
  ROW(Sheet1!$A$2:$A$7)-ROW(Sheet1!$A$2),0,1))>0,
  IF(Sheet1!$A$2:$A$7=Sheet1!F5,1)),0)

Ou, emMS365

• Fórmula usada na célulaG6

=LET(α,Sheet1!A2:A7,MATCH(1,IF(SUBTOTAL(3,OFFSET(α,SEQUENCE(ROWS(α))-1,0,1))>0,IF(α=F5,1)),0))

• Então, basicamente, o que estou tentando conseguir usando a SUBTOTAL()função é obter a lista de valores que não estão ocultos ou filtrados, na função a seguir 3denota o COUNTA()while OFFSET()ajuda na criação de uma faixa dinâmica. A captura de tela a seguir e a fórmula lhe darão uma ideia clara. Além disso, desde que estou, MS365não preciso acertar CTRL+ SHIFT+ ENTER, mas versões mais antigas precisam acertar.

=SUBTOTAL(3,OFFSET(Sheet1!$A$2:$A$7,ROW(Sheet1!$A$2:$A$7)-ROW(Sheet1!$A$2),0,1))

O acima é o mesmo que

=SUBTOTAL(3,OFFSET(α,SEQUENCE(ROWS(α))-1,0,1))

Onde αestá o alcanceSheet1!A2:A7

• Na imagem acima, vê-se que aquele que é filtrado, ou seja, 2mostra como 0, porque os que são encontrados são os 1s, quando esta parte da fórmula é avaliada mostra como todos, FALSEpois não consegue encontrar o valor correspondente aqui2

• Conseqüentemente, ao agrupar MATCH()para obter todos os TRUEie 1s que estamos obtendo, #N/Auma vez que não conseguimos encontrá-los.

Mas se tentarmos encontrar alguns outros valores, isso nos dará a saída necessária, mostrando a posição da linha. A lógica aplicada é a mesma para outras versões e também para LibreOfficeoutras versões.

Agora a verificação do questionador do German Libre Office (funciona):

• Vá para G5.
• Colar VERGLEICH(1;WENN(TEILERGEBNIS(3;VERSCHIEBUNG($A$2:$A$7;ZEILE($A$2:$A$7)-ZEILE($A$2);0;1))>0;WENN($A$2:$A$7=F5;1));0).
• Pressione Ctrl+ Shift+ Enter.
• Veja a função matriz( {}) {=VERGLEICH(1;WENN(TEILERGEBNIS(3;VERSCHIEBUNG($A$2:$A$7;ZEILE($A$2:$A$7)-ZEILE($A$2);0;1))>0;WENN($A$2:$A$7=F5;1));0)}:

E para entender o truque principal, veja como funcionam os subtotais na matriz:

• Marque G7até G12(ou quaisquer outras 5 células vazias abaixo uma da outra).
• Pressione F2enquanto as 5 células estão marcadas.
• Agora você estará na primeira célula das 5 células.
• Colar TEILERGEBNIS(3;VERSCHIEBUNG($A$2:$A$7;ZEILE($A$2:$A$7)-ZEILE($A$2);0;1)).
• Pressione Ctrl+ Shift+ Enter.
• Veja a matriz das 5 células 0preenchidas G9para 2: