Generieke formule
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete, partial_expanding,0)),0))
Samenvatting
Om twee lijsten te vergelijken en ontbrekende waarden van de ene lijst naar de andere te halen, kunt u een matrixformule gebruiken op basis van INDEX en MATCH. In het weergegeven voorbeeld bevindt de laatste waarde in lijst B zich in cel D11. De formule in D12, gekopieerd, is:
=INDEX(complete,MATCH(TRUE,ISNA(MATCH(complete,$D$5:D11,0)),0))
waarbij "compleet" het benoemde bereik is B5: B15.
Opmerking: dit is een matrixformule en moet worden ingevoerd met control + shift + enter.
Uitleg
De kern van deze formule, die van binnen naar buiten werkt, is de innerlijke MATCH-uitdrukking:
ISNA(MATCH(complete,$D$5:D11,0)
Hier wordt de MATCH-functie gebruikt om alle "complete" waarden te vergelijken met de gedeeltelijke lijst. Het benoemde bereik "compleet" wordt gebruikt voor opzoekwaarden en de gedeeltelijke lijst wordt gebruikt als de opzoekmatrix. Merk echter op dat de gedeeltelijke lijst wordt ingevoerd als een uitbreidend bereik dat eindigt op "één cel boven" de formulecel. Hierdoor kan de gedeeltelijke lijst worden uitgebreid met nieuwe waarden die onder de oorspronkelijke lijst verschijnen.
Het resultaat van MATCH is een reeks getallen en # N / A-fouten, waarbij getallen waarden vertegenwoordigen in de volledige lijst die in de gedeeltelijke lijst voorkomt; en fouten vertegenwoordigen ontbrekende waarden:
(1;#N/A;2;3;#N/A;4;5;6;#N/A;7;#N/A)
De ISNA-functie wordt gebruikt om deze resultaten om te zetten in een array van TRUE en FALSE waarden. In deze array komt TRUE overeen met ontbrekende waarden en FALSE met bestaande waarden:
(FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE)
De ISNA-functie retourneert deze array naar de buitenste MATCH als de lookup-array. De functie MATCH retourneert altijd de eerste gevonden overeenkomst, dus match retourneert de positie (rij) van de eerste gevonden ontbrekende waarde. Dit resultaat wordt geretourneerd naar INDEX als het rijnummer, met het benoemde bereik "compleet" als de array.
In cel D12 is de eerste gevonden ontbrekende waarde "kiwi" in rij 2, dus we hebben:
=INDEX(complete,2) // returns "kiwi"
In D13 is "kiwi" nu opgenomen in de uitbreidende referentie, dus de eerste ontbrekende waarde is "peer":
=INDEX(complete,5) // returns "pear"
Enzovoort. Nadat alle ontbrekende waarden zijn toegevoegd, retourneert de formule de fout # N / A.
