Generisk formel
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Sammendrag
For å sammenligne to lister og trekke manglende verdier fra en liste til en annen, kan du bruke en matriseformel basert på INDEX og MATCH. I eksemplet som vises er den siste verdien i liste B i celle D11. Formelen i D12, kopiert ned, er:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
der "komplett" er det navngitte området B5: B15.
Merk: dette er en matriseformel og må angis med kontroll + skift + enter.
Forklaring
Arbeidet fra innsiden og ut, kjernen i denne formelen er det indre MATCH-uttrykket:
ISNA(MATCH(complete,$D$5:D11,0)
Her brukes MATCH-funksjonen til å sammenligne alle "komplette" verdier mot delvis listen. Det navngitte området "komplett" brukes til oppslagsverdier, og delvis listen brukes som oppslagsmatrise. Legg imidlertid merke til at den delvise listen er angitt som et utvidende område som ender "en celle over" formelcellen. Dette gjør at den delvise listen kan utvides til å omfatte nye verdier når de vises under den opprinnelige listen.
Resultatet av MATCH er en rekke tall og # N / A-feil, der tall representerer verdier i den komplette listen som finnes i dellisten; og feil representerer manglende verdier:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
ISNA-funksjonen brukes til å konvertere disse resultatene til en rekke SANNE og FALSE verdier. I denne matrisen tilsvarer TRUE manglende verdier og FALSE tilsvarer eksisterende verdier:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
ISNA-funksjonen returnerer denne matrisen til den ytre MATCH som oppslagsmatrisen. MATCH-funksjonen returnerer alltid den første matchen som er funnet, så matchen returnerer posisjonen (raden) for den første manglende verdien som ble funnet. Dette resultatet returneres til INDEX som radnummer, med det navngitte området "komplett" oppgitt som matrisen.
I celle D12 er den første manglende verdien "kiwi" på rad 2, så vi har:
=INDEX(complete,2) // returns "kiwi"
I D13 er nå "kiwi" inkludert i den utvidende referansen, så den første manglende verdien er "pære":
=INDEX(complete,5) // returns "pear"
Og så videre. Når alle manglende verdier er lagt til, vil formelen returnere feilen # N / A.
