VERT.ZOEKEN in twee of meer criteriakolommen – Excel University

Als je ooit hebt geprobeerd een functie VERT.ZOEKEN te gebruiken met twee of meer criteriakolommen, heb je snel ontdekt dat het was gewoon niet voor dat doel gebouwd. Gelukkig is er een andere functie die kan werken als alternatief voor VERT.ZOEKEN, afhankelijk van wat u wilt retourneren.

Doelstelling voor zoeken in meerdere kolommen

Laten we eerst onze doelstelling bevestigen door te kijken naar een voorbeeldwerkboek. We hebben wat informatie uit ons boekhoudsysteem geëxporteerd, en het is in feite een samenvatting van de transactietotalen voor de maand per klasse en per rekening. Een voorbeeld van de export wordt hieronder getoond:

Uit deze geëxporteerde gegevens willen we geselecteerde bedragen ophalen op basis van de klassen en accountkolommen. We willen de bedragen ophalen en ze in ons kleine rapport plaatsen, hieronder afgebeeld:

Als u bekend bent met VERT.ZOEKEN functie, voelt het natuurlijk om te proberen het rapport met deze functie op te bouwen, want dit is tenslotte een opzoektaak. En opzoektaken kunnen het beste worden opgelost met traditionele opzoekfuncties … toch? Het hangt er vanaf. Het hangt af van wat u probeert op te halen.

Voorwaardelijke optelling voor zoekopdrachten

Als u een numerieke waarde probeert op te halen, zoals een bedrag, dan is een traditionele opzoekfunctie mogelijk niet wees je beste weddenschap. Dit is waarom. Vanaf Excel 2007 heeft Microsoft de voorwaardelijke optelfunctie SUMIFS toegevoegd. Deze functie voor het optellen van meerdere voorwaarden is ontworpen om een kolom met getallen bij elkaar op te tellen en alleen rijen op te nemen die aan een of meer voorwaarden voldoen. Beginnen de punten al met elkaar te verbinden?

Als we dit idee toepassen op onze taak, zouden we ons snel realiseren dat we deze voorwaardelijke optelfunctie zouden kunnen gebruiken om onze rapportwaarden op te halen.

Het eerste argument van de SUMIFS-functie is het sombereik, dat wil zeggen de kolom met getallen die moeten worden opgeteld. In ons geval de kolom met de waarde die we willen retourneren. De overige argumenten komen in paren: het criteriabereik en de criteriumwaarde.

Het is handig om de functie in deze termen te bekijken: tel deze kolom op (argument 1), neem alleen die rijen op waar deze kolom (argument 2) is gelijk aan deze waarde (argument 3), en waar deze kolom (argument 4) gelijk is aan deze waarde (argument 5), en waar… enzovoort, tot 127 paren.

Om ons rapport te vullen, halen we de bedragwaarden op uit de export en matchen we de klassen en de accountkolommen, zoals hieronder weergegeven.

Als er meerdere rijen zijn met dezelfde klasse en accounts, retourneert de functie SUMIFS de som van alle overeenkomende items.

Zoals je kunt zien, als de waarde je proberen te retourneren is een getal, dan maakt de SUMIFS-functie het eenvoudig om zoekopdrachten met meerdere kolommen uit te voeren. Maar wat als de waarde die u probeert terug te geven geen getal is? Welnu, dan moet u een traditionele opzoekfunctie gebruiken, zoals hieronder wordt besproken.

VERT.ZOEKEN gebruiken met de SUMIFS-methode

Een methode is om VERT.ZOEKEN en SUMIFS in één formule te gebruiken. In wezen gebruikt u SUMIFS als het eerste argument van VERT.ZOEKEN. Deze methode wordt volledig onderzocht in deze Excel University-post:

VERT.ZOEKEN gebruiken met CONCATENATE-methode

Als u probeert een tekstreeks te retourneren in plaats van een getal, of als u een versie van Excel dat geen SUMIFS heeft, dan zit u waarschijnlijk vast met het gebruik van een traditionele opzoekfunctie zoals VERT.ZOEKEN samen met de functie CONCATENATE om een enkele unieke opzoekkolom te genereren. Deze aanpak is redelijk goed gedocumenteerd, maar het basisidee is als volgt: maak eerst een enkele opzoekkolom en gebruik dan VERT.ZOEKEN.

Ons voorbeeld is een werknemerslijst, zoals hieronder geïllustreerd:

We moeten de staat van de werknemerslijst halen voor ons kleine rapport hieronder:

Aangezien de waarde die we proberen te retourneren, de state, een tekstreeks is en geen getal, is het ons onmogelijk de functie SUMIFS te gebruiken. We moeten dus ouderwets gaan met VERT.ZOEKEN en SAMENVOEGEN.

We beginnen met het bouwen van een hulpkolom die in feite de gecombineerde opzoekwaarden maakt. Dit kan eenvoudig worden bereikt met de functie CONCATENATE of de aaneenschakelingsoperator (&). Deze nieuwe opzoekkolom wordt geïllustreerd in kolom B hieronder:

Nu hebben we een enkele opzoekkolom die kan worden gebruikt met een traditionele opzoekfunctie zoals VERT.ZOEKEN. Het rapport kan worden ingevuld door de gecombineerde namen binnen het nieuwe opzoekbereik op te zoeken, zoals hieronder weergegeven:

Deze zelfde benadering kan worden gebruikt wanneer er rekening moet worden gehouden met twee, drie of meer opzoekkolommen.

Conclusie

Naast het kunnen uitvoeren van zoekopdrachten met meerdere kolommen wanneer de geretourneerde waarde numeriek is, heeft de SUMIFS-functie extra voordelen in vergelijking met traditionele opzoekfuncties. Het retourneert bijvoorbeeld nul als er geen overeenkomende waarde wordt gevonden, het retourneert de som van alle overeenkomsten, het ondersteunt vergelijkingsoperatoren en het wordt niet afgebroken wanneer een nieuwe kolom wordt ingevoegd tussen de opzoek- en retourkolommen.

Dus, wanneer u op het punt staat de functie VERT.ZOEKEN uit te schakelen om een opzoektaak uit te voeren, overweeg dan om in plaats daarvan SUMIFS te gebruiken. Geloof het of niet, de SUMIFS-functie is een geweldige opzoekfunctie.

Als je andere voorkeursbenaderingen hebt voor opzoeken met meerdere kolommen, horen we graag meer … plaats hieronder een opmerking.

Voorbeeldbestand

Als je wilt spelen met het werkboek dat is gebruikt om de bovenstaande schermafbeeldingen te genereren, aarzel dan niet om het voorbeeldbestand te downloaden:

MultColumnLookup