Veja uma solução de exemplo. Será um pouco difícil manter se os arquivos de referência estiverem mudando com frequência, mas é melhor do que nada.
Suponha que temos três arquivos, 1.xslx , 2.xslx e 3.xslx , e cada um deles tem uma tabela de Index e Value nas colunas A e B , assim:
Index Value
1 a
2 b
3 c
Crie uma nova pasta de trabalho, index.xlsx , para se parecer com isso:
A B ... E F
1 index: 1.xlsx
2 value: 2.xlsx
3 3.xlsx
Lá, você precisará preparar uma lista de todos os seus nomes de arquivo (digamos, coluna E).
Adicione esta fórmula às células F1:F3 (selecione o intervalo, insira a fórmula e digite Ctrl + Deslocamento + Enter ):
=VLOOKUP($B$1, INDIRECT("["&$E$1:$E$3&"]Sheet1"&"!$A$2:$B$5"), 2, FALSE)
substituindo 3 pelo número de arquivos (ou seja, o número de entradas na coluna E ) e 5 com o número da linha mais alta em qualquer um dos seus arquivos. Se você optar por não ter títulos na linha 1 de seus arquivos de referência, altere $A$2 para $A$1 . O segundo 2 refere-se ao fato de que estamos extraindo dados da coluna B dos arquivos de referência.
Na célula B2 , insira a fórmula:
=INDEX(F1:F3, MATCH(TRUE,ISTEXT(F1:F3),0), 0)
(novamente usando Ctrl + Desloc + Enter ). Agora, inserindo algum índice em B1 , você terá seu valor em todos os arquivos exibidos em B2 .
Nota: isso pressupõe que os valores sejam texto (e não em branco), embora não suponha que os índices sejam numéricos. Se os índices não forem exclusivos, você obterá a primeira correspondência, pesquisando os arquivos na ordem em que eles estão listados na coluna E .
Se necessário, ele pode ser modificado para intervalos diferentes em cada arquivo ou planilhas diferentes, mas essa é a ideia.
(Aqui está um exemplo da solução apresentada acima (chamada index.xlsx acima), para referência.)