Video bekijken – Functie VERT.ZOEKEN gebruiken met meerdere criteria

De Excel VERT.ZOEKEN-functie kan in zijn basisvorm naar één opzoekwaarde zoeken en de overeenkomstige waarde uit de opgegeven rij retourneren.

Maar vaak is het nodig om de Excel VERT.ZOEKEN te gebruiken met meerdere criteria.

Hoe u VERT.ZOEKEN met meerdere criteria gebruikt

Stel dat u gegevens heeft met de naam van de leerling, het tentamentype en de wiskundige score (zoals hieronder weergegeven):

Het gebruik van de VERT.ZOEKEN-functie om de wiskundige score voor elke student voor de respectievelijke examenniveaus te krijgen, kan een uitdaging zijn.

Men kan discussiëren dat een betere optie zou zijn om de dataset te herstructureren of een draaitabel te gebruiken. Als dat voor jou werkt, zoiets niet. Maar in veel gevallen zit je vast aan de gegevens die je hebt en is een draaitabel misschien geen optie.

In dergelijke gevallen is deze tutorial iets voor jou.

Nu zijn er twee manieren waarop u de opzoekwaarde kunt krijgen met VERT.ZOEKEN met meerdere criteria.

• Een hulpkolom gebruiken.
• De functie KIEZEN gebruiken.

VERT.ZOEKEN met meerdere criteria – een hulpkolom gebruiken

Ik ben een fan van hulpkolommen in Excel.

Ik vind twee belangrijke voordelen van het gebruik van hulpkolommen in plaats van matrixformules:

• Het maakt het gemakkelijk om te begrijpen wat er in het werkblad gebeurt.
• Het maakt het sneller in vergelijking met de array-functies (merkbaar in grote datasets).

Begrijp me nu niet verkeerd. Ik ben niet tegen matrixformules. Ik hou van de geweldige dingen die kunnen worden gedaan met matrixformules. Ik bewaar ze alleen voor speciale gelegenheden wanneer alle andere opties niet helpen.

Terugkomend op de vraag in kwestie, de hulpkolom is nodig om een unieke kwalificatie te creëren. Deze unieke kwalificatie kan vervolgens worden gebruikt om de juiste waarde op te zoeken. Er zijn bijvoorbeeld drie Matt in de gegevens, maar er is maar één combinatie van Matt en Unit Test of Matt en Mid-Term.

Hier zijn de stappen:

Hoe werkt dit?

We creëren unieke kwalificaties voor elk exemplaar van een naam en het examen. In de functie VERT.ZOEKEN die hier wordt gebruikt, is de opzoekwaarde gewijzigd in $ F3 & “|” & G $ 2 zodat beide zoekcriteria worden gecombineerd en worden gebruikt als een enkele opzoekwaarde. De opzoekwaarde voor de functie VERT.ZOEKEN in G2 is bijvoorbeeld Matt | Unit Test. Nu wordt deze opzoekwaarde gebruikt om de score van C2: D19 te krijgen.

Verduidelijkingen:

Er zijn waarschijnlijk een paar vragen die bij je opkomen, dus ik dacht dat ik zal proberen het hier te beantwoorden:

• Waarom heb ik | symbool terwijl u zich aansluit bij de twee criteria? – In sommige uitzonderlijk zeldzame (maar mogelijke) omstandigheden kan het zijn dat u twee criteria heeft die verschillend zijn, maar uiteindelijk hetzelfde resultaat opleveren wanneer ze worden gecombineerd. Hier is een heel eenvoudig voorbeeld (vergeef me voor mijn gebrek aan creativiteit hier):

Merk op dat hoewel A2 en A3 zijn verschillend en B2 en B3 zijn verschillend, de combinaties zijn uiteindelijk hetzelfde. Maar als je een scheidingsteken gebruikt, dan is zelfs de combinatie anders (D2 en D3).

• Waarom heb ik de hulpkolom tussen kolom B en C ingevoegd en niet helemaal links? – Het kan geen kwaad om de hulpkolom helemaal links in te brengen. In feite, als u niet wilt temperen met de originele gegevens, zou dat de juiste keuze moeten zijn. Ik deed het omdat ik hierdoor minder cellen gebruik in de functie VERT.ZOEKEN. In plaats van 4 kolommen in de tabelmatrix te hebben, zou ik het kunnen redden met slechts 2 kolommen. Maar dat is mijn mening.

Nu is er niet één maat die iedereen past. Sommige mensen geven er misschien de voorkeur aan om geen hulpkolom te gebruiken als ze VERT.ZOEKEN gebruiken met meerdere criteria.

Dus hier is de niet-hulpkolommen-methode voor jou.

Download het voorbeeldbestand

VERT.ZOEKEN met meerdere criteria – met de CHOOSE-functie

Matrixformules gebruiken in plaats van helper kolommen bespaart u werkblad onroerend goed, en de prestaties kunnen even goed zijn als ze minder vaak in een werkmap worden gebruikt.

Gezien dezelfde gegevensset als hierboven gebruikt, is hier de formule die u het resultaat geeft :

= VERT.ZOEKEN ($ E3 & “|” & F $ 2, CHOOSE ({1,2} , $ A $ 2: $ A $ 19 & “|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19 ), 2,0)

Aangezien dit een matrixformule is, gebruikt u deze met Control + Shift + Enter in plaats van alleen met Enter.

Hoe werkt dit?

De formule gebruikt ook het concept van een hulpkolom. Het verschil is dat in plaats van de hulpkolom in het werkblad te plaatsen, het wordt beschouwd als virtuele hulpgegevens die deel uitmaken van de formule.

Ik zal u laten zien wat ik bedoel met virtuele hulpgegevens.

In de bovenstaande illustratie, als ik het CHOOSE-gedeelte van de formule selecteer en op F9 druk, wordt het resultaat getoond dat de CHOOSE-formule zou geven.

Het resultaat is {“Matt | Unit Test”, 91; “Bob | Unit Test”, 52; ……}

Het is een array met een komma staat voor de volgende cel in dezelfde rij en puntkomma geeft aan dat de volgende gegevens in de volgende kolom staan. Daarom maakt deze formule twee kolommen met gegevens: één kolom heeft de unieke identificatie en één kolom heeft de score.

Nu , wanneer u de functie VERT.ZOEKEN gebruikt, zoekt het eenvoudig naar de waarde in de eerste kolom (van deze virtuele gegevens uit 2 kolommen) en retourneert de bijbehorende score.

Download de Voorbeeldbestand

U kunt ook andere formules gebruiken om een zoekopdracht uit te voeren met meerdere criteria (zoals INDEX / MATCH of SUMPRODUCT).