Excel: procurando dados da linha 1, mas retornando dados 3 linhas abaixo da fórmula

Question

Estou tendo problemas para encontrar uma fórmula que eu precise, estou procurando ajuda para ver se um de vocês me ajudará. Anexei uma captura de tela da tabela e a fórmula "redigida".

em outra planilha eu quero pegar os dados da tabela abaixo com o saldo final do Tom da Coluna B em uma célula e a Coluna C em outro saldo Final da Coluna B em uma célula e a Coluna C em outra

"Eu usei o = INDEX (B1: B10, MATCH ($ D $ 1, $ A $ 1: $ A $ 10,0) +3)" em E1 e funcionou para o TOM, mas não funciona para JEFF Jeff é o que eu preciso de ajuda, mas também terá que trabalhar para a TOM.

a linha de saldo final não será sempre 3 linhas abaixo do nome da pessoa e a linha entre cada pessoa nem sempre será 4 linhas abaixo. Eu tentei a função de deslocamento, mas sempre preciso de um ponto de referência que não possa ser usado nessa situação.

Tabela

       A             B      C      D      E
1     TOM                          TOM    13
2     beg bal         11     200 
3     act             2      20 
4     end bal         13     220
5     JEFF
6     beg bal         25     154
7     ---------------------------------------
8     act             100     45
9     ---------------------------------------
10    end bal         125     200

    

Answer 1

Atualizar para diferentes espaços e locais para End Balance e outros

=INDEX($A$1:$D$10, SMALL(IF("end bal"=$A$1:$A$10, ROW($A$1:$A$10),""),ROW(1:1)), COLUMN(B1))
Basta pressionar Ctrl + Shift + Enter ao mesmo tempo depois de escrever a fórmula (fórmula de matriz)

A1: F10 é o conjunto de dados onde quer que comece | Uma coluna para empresas e balanço
Coluna B para o custo
Coluna C para o dividendo

Coluna D para Tom ... onde você quer o resultado | Comece com o primeiro (Tom, JEFF ...) que você quer o saldo final para como nos dados iniciais (você pode copiar a coluna A e remover duplicatas e os dados não desejados)
Por exemplo, em D1 escreva Tom, D2 escreva JEFF ...
Em E1 escrever a fórmula e você pode arrastá-lo na mesma linha e para baixo na mesma coluna
Encontra a fileira mais pequena (end bal) Tom (pequeno, 1) JEFF (pequeno, 2) ...

Pedido diferente para JEFF, TOM ...

No caso do pedido não ser o mesmo que os dados iniciais Crie uma coluna auxiliar em D por exemplo e escreva nela o seguinte:
=1+COUNTIF($A$1:A2,"end bal") em D2 e arraste-a para baixo, ela gravará um número na mesma linha do nome (esse número em vez de linha (1: 1) em pequeno
A nova fórmula se tornará:
=INDEX($A$1:$D$10,SMALL(IF("end bal"=$A$1:$A$10, ROW($A$1:$A$10),""),VLOOKUP(E2,$A$1:$F$23,4,FALSE)), COLUMN(B1))
Fórmula de matriz pressione Ctrl + Shift + Enter mesmo tempo
Vlookup encontrará o número criado pelo contador que corresponde ao nome na E2
4 é a coluna D onde o número é escrito