Eu tentei fazer isso funcionar sob a suposição de que:
- "Componentes anexados" está sempre na mesma coluna.
- Você está realmente tentando pesquisar a descrição para cada "Material #".
E vou usar esta folha para trabalhar em:
Issopodenãoserexatamenteoquevocêprecisa,maspossotentarmelhorarminharespostacomsuasobservaçõessobreisso.
Aoreutilizarsuafórmulaparaidentificarondeestá"Componentes anexados" na coluna e, em seguida, adicionar 2, é apresentada a linha relativa em que a descrição do material começa:
=MATCH("Attached Components",B1:B32,0)+2
O resultado está no exemplo "7".
Depois de precisar 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 número de coluna em uma string com o nome da célula (ADDRESS (1,1)="$ A $ 1"), CONCATENATE colocará as strings juntas 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". Por isso, ele cobre a coluna Descrição e começa na linha em que você tem seus valores em 18 linhas abaixo. Para abranger mais ou menos linhas basta alterar o "+20" na 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 há colchetes ao redor (não digite os colchetes, eles aparecem quando você insere a fórmula e pressiona Ctrl + Shift + Enter)
INDIRECT transformar a string que construímos em uma referência de célula. ROW fornece o número da linha como resultado. MIN terá o menor valor no intervalo retornado. O "-1" no final é ter 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 a última descrição, de 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 #:
Nesteúltimoexemplo,ascélulascontê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 Material # na célula E7, ele exibe a descrição na célula F7.
EDITAR :
Após os comentários, a solução pode ser trabalhada dessa maneira:
Usando um exemplo mais complicado:
Acorrespondênciadelinhaséapenasumacascatadafunção2MATCH.UsandoaprimeirafunçãoMATCHparaencontraraparte#edepoisasegundaparaencontraraseçãodeinteresse:
F3: uma string da parte 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ê está procurando
F7: a fórmula para procurar a seção na parte identificada antes. 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 usa INDIRETO, CONCATENADO, ENDEREÇO. Em seguida, a linha relativa retornada pela MATCH é compensada por F4-1 para ter o número da linha absoluta.
=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:
F9:adicionando2aonúmerodalinhadacoluna"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 usando CTRL+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
Em seguida, para exibir a descrição, podemos usar INDIRECT e uma coluna de índice:
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 Material # e a descrição de uma linha identificada por um índice na coluna E. A instrução IF é para garantir que não exibimos as linhas que estão abaixo das últimas linhas. No exemplo, ele exibe apenas 5 linhas, mas você pode simplesmente copiar essa fórmula arrastando a primeira linha para baixo e adicionando novos índices para ter mais.