Generisk formel
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Resumé
For at sammenligne to lister og trække manglende værdier fra den ene liste til den anden kan du bruge en matrixformel baseret på INDEX og MATCH. I det viste eksempel er den sidste værdi i liste B i celle D11. Formlen i D12, kopieret ned, er:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
hvor "komplet" er det navngivne område B5: B15.
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter.
Forklaring
Arbejdet indefra og ud er kernen i denne formel det indre MATCH-udtryk:
ISNA(MATCH(complete,$D$5:D11,0)
Her bruges MATCH-funktionen til at sammenligne alle "komplette" værdier med den delvise liste. Det navngivne område "komplet" bruges til opslagsværdier, og delelisten bruges som opslagsarray. Bemærk dog, at den delvise liste er indtastet som et ekspanderende område, der ender "en celle over" formelcellen. Dette gør det muligt for den delvise liste at udvides til at omfatte nye værdier, da de vises under den oprindelige liste.
Resultatet af MATCH er en række tal og # N / A-fejl, hvor tal repræsenterer værdier i den komplette liste, der findes i den delvise liste; og fejl repræsenterer manglende værdier:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
ISNA-funktionen bruges til at konvertere disse resultater til en række SANDE og FALSKE værdier. I denne matrix svarer TRUE til manglende værdier og FALSE svarer til eksisterende værdier:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
ISNA-funktionen returnerer dette array til det ydre MATCH som opslagsarray. MATCH-funktionen returnerer altid det første match, der er fundet, så matchet returnerer positionen (rækken) for den første manglende værdi, der blev fundet. Dette resultat returneres til INDEX som række nummer, med det navngivne interval "komplet" angivet som array.
I celle D12 er den første manglende værdi fundet "kiwi" i række 2, så vi har:
=INDEX(complete,2) // returns "kiwi"
I D13 er "kiwi" nu inkluderet i den ekspanderende reference, så den første manglende værdi er "pære":
=INDEX(complete,5) // returns "pear"
Og så videre. Når alle manglende værdier er tilføjet, returnerer formlen fejlen # N / A.
