Fórmula genérica
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Resumo
Para comparar duas listas e extrair os valores ausentes de uma lista para a outra, você pode usar uma fórmula de matriz baseada em INDEX e MATCH. No exemplo mostrado, o último valor da lista B está na célula D11. A fórmula em D12, copiada, é:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
onde "completo" é o intervalo nomeado B5: B15.
Observação: esta é uma fórmula de matriz e deve ser inserida com control + shift + enter.
Explicação
Trabalhando de dentro para fora, o núcleo desta fórmula é a expressão interna MATCH:
ISNA(MATCH(complete,$D$5:D11,0)
Aqui, a função MATCH é usada para comparar todos os valores "completos" com a lista parcial. O intervalo nomeado "completo" é usado para valores de pesquisa e a lista parcial é usada como a matriz de pesquisa. Observe, entretanto, que a lista parcial é inserida como um intervalo de expansão que termina "uma célula acima" da célula da fórmula. Isso permite que a lista parcial se expanda para incluir novos valores conforme aparecem abaixo da lista original.
O resultado de MATCH é uma matriz de números e erros # N / A, onde os números representam valores na lista completa que existem na lista parcial; e os erros representam valores ausentes:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
A função ISNA é usada para converter esses resultados em uma matriz de valores TRUE e FALSE. Nesta matriz, TRUE corresponde aos valores ausentes e FALSE corresponde aos valores existentes:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
A função ISNA retorna esta matriz para o MATCH externo como a matriz de pesquisa. A função MATCH sempre retorna a primeira correspondência encontrada, portanto, a correspondência retornará a posição (linha) do primeiro valor ausente encontrado. Este resultado é retornado a INDEX como o número da linha, com o intervalo nomeado "completo" fornecido como a matriz.
Na célula D12, o primeiro valor ausente encontrado é "kiwi" na linha 2, então temos:
=INDEX(complete,2) // returns "kiwi"
Em D13, "kiwi" agora está incluído na referência de expansão, então o primeiro valor ausente é "pera":
=INDEX(complete,5) // returns "pear"
E assim por diante. Depois que todos os valores ausentes forem adicionados, a fórmula retornará o erro # N / A.
