Generieke formule
=FILTER(list1,COUNTIF(list2,list1))
Samenvatting
Om twee lijsten te vergelijken en gemeenschappelijke waarden te extraheren, kunt u een formule gebruiken die is gebaseerd op de functies FILTER en AANTAL.ALS. In het getoonde voorbeeld is de formule in F5:
=FILTER(list1,COUNTIF(list2,list1))
waarbij lijst1 (B5: B15) en lijst2 (D5: D13) benoemde bereiken zijn. Het resultaat, waarden die in beide lijsten voorkomen, loopt over in het bereik F5: F11.
Uitleg
De FILTER-functie accepteert een reeks waarden en een "include" -argument dat de array filtert op basis van een logische uitdrukking of waarde.
In dit geval wordt de array geleverd als het benoemde bereik "lijst1", dat alle waarden in B5: B15 bevat. Het include- argument wordt geleverd door de AANTAL.ALS-functie, die is genest in FILTER:
=FILTER(list1,COUNTIF(list2,list1))
AANTAL.ALS wordt ingesteld met lijst2 als bereik en lijst1 als criterium . Omdat we AANTAL.ALS elf criteriumwaarden geven, retourneert AANTAL.ALS elf resultaten in een matrix zoals deze:
(1;1;0;1;0;1;0;1;0;1;1)
Merk op dat de enen overeenkomen met items in lijst2 die in lijst1 verschijnen.
Deze array wordt rechtstreeks aan de FILTER-functie geleverd als het "include" -argument:
=FILTER(list1,(1;1;0;1;0;1;0;1;0;1;1))
De FILTER-functie filtert lijst1 met behulp van de waarden die worden verstrekt door AANTAL.ALS. Waarden geassocieerd met nul worden verwijderd; andere waarden blijven behouden.
Het uiteindelijke resultaat is een reeks waarden die in beide lijsten voorkomen, die overlopen in het bereik F5: F11.
Uitgebreide logica
In de bovenstaande formule gebruiken we de onbewerkte resultaten van AANTAL.ALS als filter. Dit werkt omdat Excel elke waarde anders dan nul evalueert als WAAR en nul als ONWAAR. Als AANTAL.ALS een aantal groter dan 1 retourneert, werkt het filter nog steeds correct.
Om expliciet WAAR en ONWAAR resultaten af te dwingen, kunt u "> 0" als volgt gebruiken:
=FILTER(list1,COUNTIF(list2,list1)>0)
Verwijder duplicaten of sorteer
Om duplicaten te verwijderen, nestt u de formule gewoon in de functie UNIEK:
=UNIQUE(FILTER(list1,COUNTIF(list2,list1)))
Om resultaten te sorteren, nest je in de SORT-functie:
=SORT(UNIQUE(FILTER(list1,COUNTIF(list2,list1))))
Lijstwaarden ontbreken in lijst2
Om waarden uit lijst1 die ontbreken in lijst2 uit te voeren, kunt u de logica als volgt omkeren:
=FILTER(list1,COUNTIF(list2,list1)=0)
