É possível referenciar duas células no Excel para encontrar uma terceira célula?

Question

Tentei fazer com que funcionasse partindo do pressuposto de que:

"Componentes anexados" está sempre na mesma coluna.
Na verdade, você está tentando pesquisar a descrição de cada "Material #".

E usarei esta planilha para trabalhar em:

Exemplo

Talvez isso não seja exatamente o que você precisa, mas posso tentar melhorar minha resposta com seus comentários.

Ao reutilizar sua fórmula para identificar onde está "Componentes anexados" na coluna e depois adicionar 2, obtém-se a linha relativa onde começa a descrição do material:

=MATCH("Attached Components",B1:B32,0)+2

O resultado está no exemplo "7".

Depois você precisa identificar a última linha onde estão as descrições. Para pesquisar no intervalo correto, a fórmula precisa ser alterada dependendo de qual linha "Componentes anexados" está localizada. A combinação de MATCH, ADDRESS, CONCATENATE recriará o intervalo.

MATCH fornece a linha relativa, ADDRESS transforma um número de linha e um número de coluna em uma string com o nome da célula ( ADDRESS(1,1)="$A$1" ), CONCATENATE reunirá as strings para criar um intervalo.

CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))

Isso retorna uma string como "$C$7:$C$25". Portanto, ele cobre a coluna Descrição e começa na linha onde você tem seus valores 18 linhas abaixo. Para cobrir mais ou menos linhas, basta alterar o “+20” da fórmula para o valor apropriado.

Encontrar a última linha é apenas uma questão de encontrar a primeira célula vazia com IF e MIN.

{=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
     )
)-1}

Esta fórmula é uma fórmula de matriz. É por isso que tem colchetes (não digite os colchetes, eles aparecem quando você insere a fórmula e pressiona Ctrl+Shift+Enter)

INDIRETO transforma a string que construímos em uma referência de célula. ROW fornece o número da linha como resultado. MIN assumirá o menor valor no intervalo retornado. O "-1" no final deve conter o número da linha da última descrição e não a primeira linha em branco.

No exemplo esta fórmula retorna “9”.

Agora temos o número da linha da primeira descrição e da última descrição, 7 a 9. Podemos combinar esses números da maneira que quisermos usando ADDRESS, CONCATENATE e INDIRECT para fazer qualquer operação que você precisar. Mas desta vez você tem uma referência de célula específica para trabalhar.

Por exemplo, uma pesquisa de Material #:

Exemplo de PROCV

Neste último exemplo as células contêm

E2:

=MATCH("Attached Components",B1:B32,0)+2

F2 (Para entrar usando Ctrl+Shift+Enter):

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS(MATCH("Attached Components",B1:B32,0)+2,3),":",ADDRESS(MATCH("Attached Components",B1:B32,0)+20,3))))
    )
)-1

F7:

=VLOOKUP(E7,INDIRECT(CONCATENATE(ADDRESS(E2,1),":",ADDRESS(F2,3))),3,FALSE)

Dessa forma, quando você digita um número de material na célula E7, ele exibe a descrição na célula F7.

EDITAR:

Seguindo os comentários, a solução pode ser elaborada dessa maneira:

Usando um exemplo mais complicado:

Exemplo complicado

A correspondência de linhas é apenas uma cascata de 2 funções MATCH. Usando a primeira função MATCH para encontrar o número da peça e depois a segunda para encontrar a seção de interesse:

Os 2 correspondentes

F3: uma string da peça que você está procurando

F4: a fórmula para procurar o “Part #” na primeira coluna.

=MATCH($F$3,A1:A32,0)

F6: o nome da seção que você procura

F7: a fórmula para procurar o trecho na parte identificada anteriormente. A correspondência é feita em um intervalo que começa na linha do "Part #" (armazenado na célula F4). O intervalo é construído usando o mesmo tipo de fórmula que utiliza INDIRETO, CONCATENAR, ENDEREÇO. Em seguida, a linha relativa retornada por MATCH é compensada por F4-1 para ter o número absoluto da linha.

=MATCH($F$6,INDIRECT(CONCATENATE(ADDRESS(F4,2,1),":",ADDRESS(F4+20,2,1))),0)+F4-1

Agora, para identificar a primeira e a última linha da descrição podemos reutilizar as mesmas fórmulas de antes:

primeira e última linha para descrições

F9: adicionando 2 ao número da linha "Componentes anexados" para obter a primeira linha de descrição.

=F7+2

F10: procurando a primeira linha em branco no intervalo de descrição (começando na linha armazenada em F9). Esta é uma fórmula de matriz que precisa ser inserida usandoCTRL+SHIFT+ENTER

=MIN(
    IF(
        INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3)))="",
        ROW(INDIRECT(CONCATENATE(ADDRESS($F$9,3),":",ADDRESS($F$9+20,3))))
     )
)-1

Então para exibir a descrição podemos usar INDIRETO e uma coluna de índice:

Matriz de exibição

F15:

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,1)),"")

G15:

=IF($F$9+$E15-1<=$F$10,INDIRECT(ADDRESS($F$9+$E15-1,3)),"")

Essas fórmulas exibirão o número do material e a descrição de uma linha identificada por um índice na coluna E. A instrução IF serve para garantir que não exibiremos as linhas que estão abaixo das últimas linhas. No exemplo, ele exibe apenas 5 linhas, mas você pode simplesmente copiar esta fórmula arrastando a primeira linha para baixo e adicionando novos índices para ter mais.

Answer 1