Generička formula
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Sažetak
Da biste usporedili dva popisa i povukli vrijednosti koje nedostaju s jednog popisa na drugi, možete upotrijebiti formulu niza koja se temelji na INDEX i MATCH. U prikazanom primjeru, zadnja vrijednost na popisu B je u ćeliji D11. Kopirana formula u D12 je:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
gdje je "kompletan" imenovani raspon B5: B15.
Napomena: ovo je formula niza i mora se unijeti sa control + shift + enter.
Obrazloženje
Radeći iznutra prema van, srž ove formule je unutarnji izraz MATCH:
ISNA(MATCH(complete,$D$5:D11,0)
Ovdje se funkcija MATCH koristi za usporedbu svih "cjelovitih" vrijednosti s djelomičnim popisom. Imenovani raspon "dovršen" koristi se za vrijednosti pretraživanja, a djelomični popis koristi se kao niz pretraživanja. Međutim, primijetite da se djelomični popis unosi kao opseg koji se širi i završava "jednu ćeliju iznad" ćelije formule. To omogućuje proširenje djelomičnog popisa tako da uključuje nove vrijednosti onako kako se pojavljuju ispod izvornog popisa.
Rezultat MATCH-a je niz brojeva i # N / A pogrešaka, gdje brojevi predstavljaju vrijednosti na cjelovitom popisu koje postoje na djelomičnom popisu; a pogreške predstavljaju vrijednosti koje nedostaju:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
Funkcija ISNA koristi se za pretvaranje ovih rezultata u niz TRUE i FALSE vrijednosti. U ovom polju TRUE odgovara vrijednostima koje nedostaju, a FALSE odgovara postojećim vrijednostima:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
Funkcija ISNA vraća ovaj niz u vanjski MATCH kao niz pretraživanja. Funkcija MATCH uvijek vraća prvo pronađeno podudaranje, tako da će match vratiti položaj (redak) prve pronađene vrijednosti koja nedostaje. Ovaj se rezultat vraća u INDEX kao broj retka, a imenovani raspon "dovršen" daje se kao niz.
U ćeliji D12 prva pronađena vrijednost koja nedostaje je "kivi" u retku 2, tako da imamo:
=INDEX(complete,2) // returns "kiwi"
U D13, "kivi" je sada uključen u referencu koja se širi, pa je prva vrijednost koja nedostaje "kruška":
=INDEX(complete,5) // returns "pear"
I tako dalje. Jednom kad se dodaju sve vrijednosti koje nedostaju, formula će vratiti pogrešku # N / A.
