Formula generica
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Sommario
Per confrontare due elenchi e tirare i valori mancanti da un elenco all'altro, puoi utilizzare una formula di matrice basata su INDICE e CONFRONTA. Nell'esempio mostrato, l'ultimo valore nell'elenco B si trova nella cella D11. La formula in D12, copiata in basso, è:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
dove "completo" è l'intervallo denominato B5: B15.
Nota: questa è una formula di matrice e deve essere inserita con CTRL + MAIUSC + INVIO.
Spiegazione
Lavorando dall'interno verso l'esterno, il nucleo di questa formula è l'espressione MATCH interna:
ISNA(MATCH(complete,$D$5:D11,0)
Qui, la funzione CONFRONTA viene utilizzata per confrontare tutti i valori "completi" con l'elenco parziale. L'intervallo denominato "completo" viene utilizzato per i valori di ricerca e l'elenco parziale viene utilizzato come matrice di ricerca. Si noti, tuttavia, che l'elenco parziale viene immesso come un intervallo in espansione che termina "una cella sopra" la cella della formula. Ciò consente all'elenco parziale di espandersi per includere nuovi valori così come appaiono sotto l'elenco originale.
Il risultato di MATCH è un array di numeri e # N / A errori, dove i numeri rappresentano i valori nell'elenco completo che esistono nell'elenco parziale; e gli errori rappresentano i valori mancanti:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
La funzione ISNA viene utilizzata per convertire questi risultati in una matrice di valori TRUE e FALSE. In questo array, TRUE corrisponde ai valori mancanti e FALSE corrisponde ai valori esistenti:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
La funzione ISNA restituisce questo array al MATCH esterno come array di ricerca. La funzione MATCH restituisce sempre la prima corrispondenza trovata, quindi match restituirà la posizione (riga) del primo valore mancante trovato. Questo risultato viene restituito a INDICE come numero di riga, con l'intervallo denominato "completo" fornito come matrice.
Nella cella D12, il primo valore mancante trovato è "kiwi" alla riga 2, quindi abbiamo:
=INDEX(complete,2) // returns "kiwi"
In D13, "kiwi" è ora incluso nel riferimento in espansione, quindi il primo valore mancante è "pera":
=INDEX(complete,5) // returns "pear"
E così via. Una volta aggiunti tutti i valori mancanti, la formula restituirà l'errore # N / D.
