Fórmula genérica
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Resumen
Para comparar dos listas y extraer valores faltantes de una lista a otra, puede usar una fórmula de matriz basada en INDICE y COINCIDIR. En el ejemplo que se muestra, el último valor de la lista B está en la celda D11. La fórmula en D12, copiada, es:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
donde "completo" es el rango con nombre B5: B15.
Nota: esta es una fórmula de matriz y debe ingresarse con control + shift + enter.
Explicación
Trabajando desde adentro hacia afuera, el núcleo de esta fórmula es la expresión MATCH interna:
ISNA(MATCH(complete,$D$5:D11,0)
Aquí, la función COINCIDIR se utiliza para comparar todos los valores "completos" con la lista parcial. El rango con nombre "completo" se usa para los valores de búsqueda, y la lista parcial se usa como la matriz de búsqueda. Sin embargo, observe que la lista parcial se ingresa como un rango en expansión que termina "una celda por encima" de la celda de fórmula. Esto permite que la lista parcial se expanda para incluir nuevos valores a medida que aparecen debajo de la lista original.
El resultado de MATCH es una matriz de números y errores # N / A, donde los números representan valores en la lista completa que existen en la lista parcial; y los errores representan valores perdidos:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
La función ISNA se utiliza para convertir estos resultados en una matriz de valores VERDADERO y FALSO. En esta matriz, TRUE corresponde a los valores perdidos y FALSE corresponde a los valores existentes:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
La función ISNA devuelve esta matriz al MATCH externo como matriz de búsqueda. La función COINCIDIR siempre devuelve la primera coincidencia encontrada, por lo que coincidencia devolverá la posición (fila) del primer valor faltante encontrado. Este resultado se devuelve a INDICE como el número de fila, con el rango denominado "completo" proporcionado como la matriz.
En la celda D12, el primer valor faltante encontrado es "kiwi" en la fila 2, por lo que tenemos:
=INDEX(complete,2) // returns "kiwi"
En D13, "kiwi" ahora se incluye en la referencia desplegable, por lo que el primer valor que falta es "pera":
=INDEX(complete,5) // returns "pear"
Y así. Una vez que se hayan agregado todos los valores faltantes, la fórmula devolverá el error # N / A.
