Como indexar correspondência de uma célula especificada até não ficar em branco

Question

Eu tenho dados em uma espécie de layout de árvore de diretórios (veja a foto)

Basicamente eu quero usar um código de pesquisa como um ponto de referência e ter o Excel para cima nas respectivas colunas para exibir os "diretórios pai".

Atualmente eu tenho um índice que me diz exatamente qual é o código, mas eu quero configurar uma ferramenta de pesquisa (veja a tabela abaixo na imagem abaixo) que me mostra todos os níveis de uma só vez. Na foto eu coloquei os valores que quero retornar usando fórmulas.

Portanto, se eu inserir um código de nível 4, quero usar fórmulas que retornarão os programas de nível 4, 3, 2 e 1. Para um código de nível 3, quero que ele pesquise os programas de nível 3, 2 e 1.

Existem mais de 10.000 linhas de dados.

Parece-me tão simples ... Comece por esta linha e olhe para cima nesta coluna até chegar a um valor ..... Mas parece que não é tão simples.

Qualquer ajuda / dicas será muito apreciada!

-Alex

    

Answer 1

Como você percebeu, o Excel não suporta nativamente "olhando para cima". Funções como index() ou vlookup() são ótimas para encontrar a correspondência primeiro , mas não a última .

Supondo que seus dados estejam nas células A1: F10, essa fórmula localizará a última célula não vazia na coluna A, começando do código (coluna D) inserido na célula H2. Ele pode ser preenchido automaticamente (para encontrar suas outras colunas) e para baixo (para procurar mais códigos). Esta é uma fórmula de matriz e deve ser confirmada com ctrl + shift + enter.

=INDEX(A$2:A$10,MAX(IF(NOT(ISBLANK(OFFSET(A$2,,,MATCH($H2,$D$2:$D$10,0)))),ROW(OFFSET(A$2,,,MATCH($H2,$D$2:$D$10,0))),""))-1)

OFFSET(A$2,,,MATCH($H2,$D$2:$D$10,0)) é usado para dimensionar dinamicamente a matriz de pesquisa para que ela inicie em A2 e termine na linha na qual o código desejado é encontrado. Então, se seu código está localizado em D5, esta peça retorna A2:A5

MAX(IF(NOT(ISBLANK(...)),ROW(...),"")) Isso retorna o número da linha da maior célula não vazia no intervalo encontrado anteriormente.

INDEX(A$2:A$10,...-1) e finalmente, index() encontra o valor real da célula que encontramos. Subtraímos um porque o intervalo de pesquisa começa na linha 2, enquanto a contagem de linhas que estamos usando, obviamente, começa em 1.