Retourneer meerdere overeenkomende waarden op basis van meerdere criteria in Excel (volledige handleiding)
Excel-gebruikers komen vaak scenario's tegen waarin het nodig is om meerdere waarden te extraheren die aan meerdere criteria tegelijk voldoen, en alle overeenkomende resultaten in een kolom, een rij of geconsolideerd in één cel te presenteren. Deze gids verkent methoden voor alle Excel-versies en de nieuwere FILTER-functie die beschikbaar is in Excel 365 en 2021.
Retourneer meerdere overeenkomende waarden op basis van meerdere criteria in één cel
In Excel is het extraheren van meerdere overeenkomende waarden op basis van meerdere criteria binnen één cel een veelvoorkomende uitdaging. Ontdek hier twee efficiënte methoden.
Methode 1: De Textjoin-functie gebruiken (Excel365 / 2021,2019)
Om alle overeenkomende waarden in één cel met scheidingstekens te krijgen, kan de functie TEXTJOIN u van dienst zijn.
Voer de volgende formule in of kopieer deze in een lege cel en druk vervolgens op de Enter-toets (Excel 2021 en Excel 365) of op de toetsen Ctr l+ Shift + Enter in Excel 2019 om het resultaat te krijgen:
=TEXTJOIN(", ", TRUE, IF(($A$2:$A$18=E2)*($B$2:$B$18=F2), $C$2:$C$18, ""))
- ($A$2:$A$21=E2)*($B$2:$B$21=F2) controleer of elke rij aan beide voorwaarden voldoet: "Verkoper is gelijk aan E2" en "Maand is gelijk aan F2." Als aan beide voorwaarden is voldaan, is het resultaat 1; anders is het 0. De asterisk * betekent dat beide voorwaarden waar moeten zijn.
- ALS(..., $C$2:$C$21, "") retourneert de productnaam als de rij overeenkomt; anders wordt een lege rij geretourneerd.
- TEXTJOIN(", ", WAAR, ...) combineert alle niet-lege productnamen in één cel, gescheiden door ",".
Methode 2: Kutools voor Excel gebruiken
Kutools voor Excel biedt een krachtige maar eenvoudige oplossing waarmee u snel meerdere overeenkomsten kunt ophalen en combineren in één cel op basis van meerdere criteria, zonder ingewikkelde formules.
Na het installeren van Kutools voor Excel, doe dit als volgt:
- Selecteer het gegevensbereik waarvan u alle bijbehorende waarden wilt ophalen op basis van criteria.
- Klik vervolgens op Kutools > Samenvoegen en splitsen > Geavanceerd rijen combineren, zie screenshot:
- Configureer de volgende opties in het dialoogvenster Geavanceerd rijen combineren:
- Selecteer de kolomkoppen die uw matchingcriteria bevatten (bijvoorbeeld Verkoper en Maand). Klik voor elke geselecteerde kolom op Primaire sleutel om ze te definiëren als uw opzoekvoorwaarden.
- Klik op de kolomkop waar u de gecombineerde resultaten wilt hebben (bijvoorbeeld Product). Selecteer in het gedeelte Combineren het gewenste scheidingsteken (bijvoorbeeld komma, spatie of aangepast scheidingsteken).
- Klik ten slotte op de knop OK.
Resultaat: Kutools voegt direct alle overeenkomende waarden samen in één cel per unieke criteriacombinatie.
Retourneer meerdere overeenkomende waarden op basis van meerdere criteria in een kolom
Excel biedt een aantal krachtige oplossingen als u meerdere overeenkomende records uit een dataset wilt extraheren en weergeven op basis van verschillende voorwaarden, waarbij de resultaten in een verticale kolomindeling worden weergegeven.
Methode 1: Een matrixformule gebruiken (voor alle versies)
U kunt de volgende matrixformule gebruiken om resultaten verticaal in een kolom te retourneren:
1. Kopieer of typ de volgende formule in een lege cel:
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), ROW(1:1))), "")2. Druk op de toetsen Ctrl + Shift + Enter om het eerste overeenkomende resultaat te krijgen, selecteer vervolgens de eerste formulecel en sleep de vulgreep naar beneden naar de cellen totdat een lege cel wordt weergegeven. Nu worden alle overeenkomende waarden geretourneerd zoals in onderstaande schermafbeelding:
- $A$2:$A$18=$E$2: Controleert of de Verkoper overeenkomt met de waarde in cel E2.
- $B$2:$B$18=$F$2: Controleert of de maand overeenkomt met de waarde in cel F2.
- * is een logische EN-operator (beide voorwaarden moeten waar zijn).
- RIJ($C$2:$C$18)-RIJ($C$2)+1: Genereert een relatief rijnummer voor elk product.
- KLEIN(..., RIJ(1:1)): Haalt de n-de kleinste overeenkomende rij op (terwijl de formule naar beneden wordt gesleept).
- INDEX(...): Retourneert het product uit de overeenkomende rij.
- ALSFOUT(..., ""): Retourneert een lege cel als er geen overeenkomsten meer zijn.
Methode 2: Filterfunctie gebruiken (Excel365 / 2021)
Als u Excel 365 of Excel 2021 gebruikt, is de FILTER-functie een uitstekende keuze voor het retourneren van meerdere resultaten op basis van meerdere criteria, dankzij de eenvoud, duidelijkheid en de mogelijkheid om resultaten dynamisch te verspreiden zonder complexe matrixformules.
Kopieer of typ de onderstaande formule in een lege cel en druk op Enter. Alle overeenkomende records worden geretourneerd op basis van de verschillende criteria.
=FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match")
- FILTER(...) retourneert alle waarden uit C2:C18 waarbij aan beide voorwaarden is voldaan.
- (A2:A18=E2)*(B2:B18=F2): Logische matrix die controleert op overeenkomende verkoper en maand.
- "Geen match": Optioneel bericht als er geen waarden worden gevonden.
Retourneer meerdere overeenkomende waarden op basis van meerdere criteria in een rij
Excel-gebruikers moeten vaak meerdere waarden uit een dataset halen die aan verschillende voorwaarden voldoen en deze horizontaal (op een rij) weergeven. Dit is handig voor het maken van dynamische rapporten, dashboards of samenvattingstabellen waar de verticale ruimte beperkt is. In deze sectie verkennen we twee krachtige methoden.
Methode 1: Een matrixformule gebruiken (voor alle versies)
Traditionele matrixformules maken het mogelijk om meerdere overeenkomende waarden te extraheren met behulp van de functies INDEX, SMALL, IF en COLUMN. In tegenstelling tot verticale extractie (op kolommen gebaseerd), passen we de formule aan om resultaten in een rij te retourneren.
1. Kopieer of typ de onderstaande formule in een lege cel:
=IFERROR(INDEX($C$2:$C$18, SMALL(IF(($A$2:$A$18=$E$2)*($B$2:$B$18=$F$2), ROW($C$2:$C$18)-ROW($C$2)+1), COLUMN(A1))), "")2. Druk op de toetsen Ctrl + Shift + Enter om het eerste overeenkomende resultaat op te halen. Selecteer vervolgens de eerste formulecel en sleep de formule naar rechts over de kolommen om alle resultaten op te halen.
- $A$2:$A$18=$E$2: Controleert of de verkoper overeenkomt.
- $B$2:$B$18=$F$2: Controleert of de maand overeenkomt.
- *: Logische EN: beide voorwaarden moeten waar zijn.
- RIJ($C$2:$C$18)-RIJ($C$2)+1: Maakt relatieve rijnummers.
- KOLOM(A1): Hiermee past u aan welke overeenkomst wordt geretourneerd, afhankelijk van hoe ver de formule naar rechts is gesleept.
- ALSFOUT(...): Voorkomt fouten zodra er geen matches meer zijn.
Methode 2: Filterfunctie gebruiken (Excel365 / 2021)
Kopieer of typ de onderstaande formule in een lege cel en druk op de Enter-toets. Alle overeenkomende waarden worden geëxtraheerd en in een rij geplaatst. Zie screenshot:
=TRANSPOSE(FILTER(C2:C18, (A2:A18=E2)*(B2:B18=F2), "No match"))
- FILTER(...): Haalt overeenkomende waarden op uit kolom C op basis van de twee voorwaarden.
- (A2:A18=E2)*(B2:B18=F2): Beide voorwaarden moeten waar zijn.
- TRANSPONEREN(...): Converteert de verticale array die door FILTER wordt geretourneerd naar een horizontale array.
🔚 Conclusie
U kunt op verschillende manieren meerdere overeenkomende waarden ophalen op basis van meerdere criteria in Excel, afhankelijk van hoe u de resultaten wilt weergeven: in een kolom, een rij of in één cel.
- Voor gebruikers met Excel 365 of Excel 2021 biedt de FILTER-functie een moderne, dynamische en elegante oplossing die de complexiteit minimaliseert.
- Voor gebruikers van oudere versies zijn matrixformules nog steeds krachtige hulpmiddelen, maar ze vereisen wel wat meer instellingen en onderhoud.
- Als u bovendien resultaten in één cel wilt consolideren of de voorkeur geeft aan een oplossing zonder code, kunnen de functie TEXTJOIN of hulpmiddelen van derden zoals Kutools voor Excel het proces aanzienlijk stroomlijnen.
Kies de methode die het beste past bij uw versie van Excel en uw voorkeurslay-out, en u bent goed uitgerust om multi-criteria lookups efficiënt en nauwkeurig te verwerken. Als u geïnteresseerd bent in het verkennen van meer Excel-tips en -trucs, onze website biedt duizenden tutorials om u te helpen Excel onder de knie te krijgen.
Meer relatieve artikelen:
- Retourneer meerdere opzoekwaarden in één door komma's gescheiden cel
- In Excel kunnen we de functie VERT.ZOEKEN toepassen om de eerste overeenkomende waarde uit een tabelcel te retourneren, maar soms moeten we alle overeenkomende waarden extraheren en vervolgens gescheiden door een specifiek scheidingsteken, zoals komma, streepje, enz ... in een enkele cel zoals in het volgende screenshot getoond. Hoe kunnen we meerdere opzoekwaarden ophalen en retourneren in één door komma's gescheiden cel in Excel?
- Bekijk en retourneer meerdere overeenkomende waarden tegelijk in Google Sheet
- De normale Vlookup-functie in het Google-blad kan u helpen de eerste overeenkomende waarde te vinden en te retourneren op basis van bepaalde gegevens. Maar soms moet u mogelijk alle overeenkomende waarden opvullen en retourneren zoals in het volgende screenshot. Heeft u goede en gemakkelijke manieren om deze taak in het Google-blad op te lossen?
- Bekijk en retourneer meerdere waarden uit de vervolgkeuzelijst
- Hoe kunt u in Excel meerdere overeenkomstige waarden uit een vervolgkeuzelijst opvouwen en retourneren, wat betekent dat wanneer u een item uit de vervolgkeuzelijst kiest, alle relatieve waarden tegelijk worden weergegeven zoals in het volgende screenshot. In dit artikel zal ik de oplossing stap voor stap introduceren.
- Bekijk en retourneer meerdere waarden verticaal in Excel
- Normaal gesproken kunt u de functie Vlookup gebruiken om de eerste overeenkomstige waarde te krijgen, maar soms wilt u alle overeenkomende records retourneren op basis van een specifiek criterium. In dit artikel zal ik het hebben over het opvullen en retourneren van alle overeenkomende waarden verticaal, horizontaal of in één enkele cel.
- Zoek en retourneer overeenkomende gegevens tussen twee waarden in Excel
- In Excel kunnen we de normale Vlookup-functie toepassen om de bijbehorende waarde te krijgen op basis van bepaalde gegevens. Maar soms willen we opvullen en de overeenkomende waarde tussen twee waarden retourneren, zoals de volgende schermafbeelding laat zien, hoe zou u deze taak in Excel kunnen aanpakken?
Beste Office-productiviteitstools
Geef uw Excel-vaardigheden een boost met Kutools voor Excel en ervaar efficiëntie als nooit tevoren. Kutools voor Excel biedt meer dan 300 geavanceerde functies om de productiviteit te verhogen en tijd te besparen. Klik hier om de functie te krijgen die u het meest nodig heeft...
Office-tabblad Brengt een interface met tabbladen naar Office en maakt uw werk veel gemakkelijker
- Schakel bewerken en lezen met tabbladen in Word, Excel, PowerPoint in, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster in plaats van in nieuwe vensters.
- Verhoogt uw productiviteit met 50% en vermindert honderden muisklikken voor u elke dag!