Hoe vindt u een waarde op basis van twee of meer criteria in Excel?

Stel dat u werkt met een fruitsalestabel zoals hieronder weergegeven. U wilt het verkoopbedrag opzoeken op basis van meerdere criteria, zoals het soort fruit, de verkoopdatum en het gewicht. Met matrixformules in Excel haalt u deze waarden efficiënt op — zelfs bij meerdere voorwaarden. Deze methode is flexibel en eenvoudig aan te passen aan datasets waarin u één celwaarde zoekt die aan al uw criteria voldoet.

Matrixformule 1: Waarde zoeken met twee of meerdere criteria in Excel

De algemene structuur van deze matrixformule is als volgt:

{=INDEX(matrix;VERGELIJKEN(1;(criterium1=opzoekmatrix1)*(criterium2=opzoekmatrix2)…*(criterium n=opzoekmatrix n);0))}

Als u bijvoorbeeld het verkoopbedrag van mangoverkocht op 9/3/2019wilt vinden, voert u de volgende formule in een lege cel in en drukt u op Ctrl+Shift+Enterom deze als een matrixformule te bevestigen:

=INDEX(F3:F22;VERGELIJKEN(1;(J3=B3:B22)*(J4=C3:C22);0))

Opmerking: In dit voorbeeld,

• F3:F22 is de kolom 'Bedrag' waaruit u de waarde wilt ophalen.
• B3:B22 is de kolom 'Datum'; C3:C22 is de kolom 'Fruit'.
• J3 is de datum die als eerste criterium is gekozen; J4 is de fruitnaam die als tweede criterium wordt gebruikt.

Het toevoegen van meer criteria is eenvoudig. Als u bijvoorbeeld het verkoopbedrag zoekt van mango op 9/3/2019 met een gewicht van 211, voegt u de derde voorwaarde toe aan zowel VERGELIJKEN als de opzoekmatrices, zoals hieronder weergegeven:

=INDEX(F3:F22;VERGELIJKEN(1;(J3=B3:B22)*(J4=C3:C22)*(J5=E3:E22);0))

Nadat u de formule hebt ingevoerd, drukt u opnieuw op Ctrl+Shift+Enter om te bevestigen. Het resultaat is het verkoopbedrag dat voldoet aan alle opgegeven criteria.

Matrixformule 2: Waarde zoeken met twee of meerdere criteria in Excel door samenvoeging

U kunt ook samenvoeging toepassen in uw formule voor een alternatieve aanpak, vooral wanneer u een compacte structuur wenst. De basisformule is:

=INDEX(matrix;VERGELIJKEN(criterium1&criterium2…&criteriumNopzoekmatrix1&opzoekmatrix2…&opzoekmatrixN0);0)

Om bijvoorbeeld het verkoopbedrag op te halen voor een fruit met een gewicht van 242op 9/1/2019:

=INDEX(F3:F22;VERGELIJKEN(J3&J4B3:B22&C3:C22;0);0)

Opmerking: Hier,

• F3:F22 is de kolom Bedrag; B3:B22 is de kolom Datum; E3:E22 is de kolom Gewicht.
• J3 is de datum; J5 is de gewichtswaarde voor uw criteria.

Voor meer dan twee criteria breidt u zowel de criteria als de opzoekmatrices uit in dezelfde volgorde:

=INDEX(F3:F22;VERGELIJKEN(J3&J4&J5B3:B22&C3:C22&E3:E22;0);0)

Druk net als eerder op Ctrl+Shift+Enter om het juiste resultaat te krijgen.

Beide matrixformulemethoden helpen u de eerste waarde te vinden die aan al uw criteria voldoet. Ze vereisen echter dat de celbereiken even groot zijn en geven niet meerdere overeenkomende waarden terug—alleen de eerste match wordt opgehaald. Als er geen overeenkomst is, resulteert de formule in een #N/B-fout. Wilt u liever een formule die alle overeenkomsten weergeeft? Overweeg dan de FILTER-functie (zie hieronder voor details).

Enkele praktische tips en opmerkingen:

• Als u werkt met nieuwere versies van Excel (Microsoft 365, Excel 2021), kunt u dynamische matrixformules en de FILTER-functie gebruiken om dit proces te vereenvoudigen.
• Om #N/B-fouten te voorkomen wanneer er geen overeenkomsten zijn, kunt u de formule insluiten in ALS.FOUT, bijvoorbeeld: =ALS.FOUT(INDEX(F3:F22;VERGELIJKEN(1;(J3=B3:B22)*(J4=C3:C22);0));„Niet gevonden").
• Controleer of uw cellen met zoekcriteria geen overbodige spaties of afwijkende gegevenstypen bevatten.
• Als u een foutmelding krijgt nadat u alleen op Enter hebt gedrukt, gebruik dan Ctrl+Shift+Enter om de formule als matrixformule te bevestigen (voor Excel 2019 en eerder).

Naast het gebruik van formules biedt Excel de functie Geavanceerd filter, waarmee u alle rijen kunt filteren en extraheren die aan twee of meer criteria voldoen, en de overeenkomende resultaten op een andere locatie kunt weergeven. Deze aanpak is vooral handig als u alle records wilt zien die voldoen aan uw opgegeven voorwaarden, in plaats van slechts één waarde op te halen. Zo gebruikt u het:

1. Ga naar het tabblad Gegevens en kies Geavanceerd in de groep Sorteren en filteren om het dialoogvenster Geavanceerd filter te openen.

2. Voltooi in het dialoogvenster Geavanceerd filter de volgende instellingen:
(1) Selecteer Kopiëren naar een andere locatie in het gedeelte Actie.
(2) Bij Lijstbereikmarkeert u het bereik met de gegevens die u wilt filteren ()A1:E21 in dit voorbeeld).
(3) Bij Criteriabereikselecteert u het bereik met uw filtervoorwaarden ()H1:J2 hier). Zorg ervoor dat de kopteksten in dit criteriabereik exact overeenkomen met die in uw gegevenstabel.
(4) Bij Kopiëren naarselecteert u de eerste cel waar u de gefilterde resultaten wilt plakken ()H9 in dit geval).

3. Klik op OK om het filteren uit te voeren.

De rijen die voldoen aan alle voorwaarden in uw criteriabereik worden gekopieerd naar het door u opgegeven doelgebied — ideaal voor het controleren of rapporteren van records die tegelijkertijd aan meerdere filtervoorwaarden voldoen.

Enkele tips en aandachtspunten:

• Zorg ervoor dat de kopteksten in uw criteriabereik exact overeenkomen met die in uw hoofdgegevenstabel—anders werkt het filter mogelijk niet naar behoren.
• Geavanceerd filter ondersteunt zowel EN- als OF-voorwaarden: criteria op dezelfde rij volgen de EN-logica (alle voorwaarden moeten waar zijn), terwijl afzonderlijke rijen de OF-logica toepassen (minstens één voorwaarde hoeft waar te zijn).
• Het geavanceerde filter wordt niet automatisch bijgewerkt wanneer uw gegevens wijzigen; u moet het filter opnieuw toepassen nadat u uw gegevens of criteria hebt aangepast.
• Houd er rekening mee dat lege cellen in het criteriabereik mogelijk worden gezien als „accepteer elke waarde” voor dat veld.

In vergelijking met op formules gebaseerde oplossingen is Geavanceerd filteren vooral geschikt om complete datasets te extraheren die aan de criteria voldoen, in plaats van slechts één cel. Het is echter minder geschikt voor realtime- of regelmatig bijgewerkte zoekopdrachten, aangezien u het filter na elke wijziging in de gegevens opnieuw moet uitvoeren.

Als u een recente versie van Excel gebruikt (Microsoft 365 of Excel 2021 en nieuwer), biedt de functie FILTER een dynamische en intuïtieve manier om alle waarden te extraheren die aan meerdere criteria voldoen. Deze oplossing is ideaal voor wie automatisch bijgewerkte resultaten nodig heeft zodra gegevens of criteria veranderen — en vereist geen complexe matrixinvoer.

1. Voer in een lege cel een formule in zoals het volgende voorbeeld:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4))

In deze formule:

• F3:F22 is uw kolom Bedrag.
• B3:B22 is de kolom Datum, vergeleken met de datum in J3.
• C3:C22 is de kolom Fruit, vergeleken met het fruit in J4.

Als u een derde voorwaarde wilt toevoegen, zoals overeenkomst met de kolom Gewicht (E3:E22)met een waarde in J5, breidt u de formule uit:

=FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5))

Nadat u op Enter drukt, toont Excel alle bedragen die aan alle criteria voldoen. Als er geen overeenkomst wordt gevonden, retourneert de formule een foutmelding #CALC!, die u kunt afvangen met IFERROR:

=IFERROR(FILTER(F3:F22, (B3:B22=J3)*(C3:C22=J4)*(E3:E22=J5)), "No match")

Voordelen:

• Resultaten worden automatisch bijgewerkt zodra uw gegevens of criteria wijzigen.
• Formules zijn eenvoudiger te onderhouden en uit te breiden dan klassieke matrixformules.
• Geeft alle overeenkomsten terug, niet alleen de eerste gevonden waarde.

Beperking: Alleen beschikbaar in Microsoft 365, Excel 2021 of nieuwer. Niet ondersteund in oudere versies.