Een Excel-opzoekformule maken met meerdere criteria

Wat te weten

• Maak eerst een INDEX-functie en start vervolgens de geneste MATCH-functie door het Lookup_value-argument in te voeren.
• Voeg vervolgens het argument Lookup_array toe, gevolgd door het argument Match_type en geef vervolgens het kolombereik op.
• Verander vervolgens de geneste functie in een matrixformule door op Ctrl + Shift + Enter te drukken. Voeg ten slotte de zoektermen toe aan het werkblad.

In dit artikel wordt uitgelegd hoe u een opzoekformule maakt die meerdere criteria in Excel gebruikt om informatie in een database of tabel met gegevens te vinden met behulp van een matrixformule. De matrixformule omvat het nesten van de MATCH-functie in de INDEX-functie. De informatie heeft betrekking op Excel voor Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 en Excel voor Mac.

Volg mee met de zelfstudie

Om de stappen in deze zelfstudie te volgen, voert u de voorbeeldgegevens in de volgende cellen in, zoals weergegeven in de onderstaande afbeelding. Rijen 3 en 4 zijn leeg gelaten om plaats te bieden aan de matrixformule die tijdens deze zelfstudie is gemaakt. (Merk op dat deze zelfstudie niet de opmaak bevat die in de afbeelding te zien is.)

• Voer het hoogste gegevensbereik in de cellen D1 tot F2 in.
• Voer het tweede bereik in cellen D5 tot F11 in.

Maak een INDEX-functie in Excel

De INDEX-functie is een van de weinige functies in Excel die meerdere vormen heeft. De functie heeft een Array Form en een Reference Form. De Array Form retourneert de gegevens uit een database of tabel met gegevens. Het referentieformulier geeft de celverwijzing of locatie van de gegevens in de tabel weer.

In deze zelfstudie wordt het matrixformulier gebruikt om de naam van de leverancier voor titanium-widgets te vinden, in plaats van de celverwijzing naar deze leverancier in de database.

Volg deze stappen om de INDEX-functie te maken:

1. Selecteer cel F3 om er de actieve cel van te maken. In deze cel wordt de geneste functie ingevoerd.
2. Ga naar Formules.

   

3. Kies Opzoeken en verwijzen om de vervolgkeuzelijst met functies te openen.
4. Selecteer INDEX om het dialoogvenster Argumenten selecteren te openen.
5. Kies matrix,rij_getal,kolom_getal.
6. Selecteer OK om het dialoogvenster Functieargumenten te openen. In Excel voor Mac wordt de opbouwfunctie voor formules geopend.
7. Plaats de cursor in het tekstvak Array.
8. Markeer de cellen D6 tot en met F11 in het werkblad om het bereik in het dialoogvenster in te voeren.
   Laat het dialoogvenster Functieargumenten open. De formule is niet af. U voltooit de formule in de onderstaande instructies.
   

Start de geneste MATCH-functie

Bij het nesten van een functie in een andere, is het niet mogelijk om de tweede, of geneste, formulebouwer van de functie te openen om de benodigde argumenten in te voeren. De geneste functie moet worden ingevoerd als een van de argumenten van de eerste functie.

Bij het handmatig invoeren van functies worden de argumenten van de functie van elkaar gescheiden door een komma.

De eerste stap om de geneste MATCH-functie in te voeren, is het invoeren van het argument Lookup_value. De Lookup_value is de locatie of celverwijzing voor de zoekterm die moet worden gevonden in de database.

De Lookup_value accepteert slechts één zoekcriterium of term. Als u naar meerdere criteria wilt zoeken, breidt u de Lookup_value uit door twee of meer celverwijzingen samen te voegen of samen te voegen met behulp van het ampersand-symbool (&).

1. Plaats in het dialoogvenster Functieargumenten de cursor in het tekstvak Rij_getal.
2. Voer VERGELIJKEN(.
3. Selecteer cel D3 om die celverwijzing in het dialoogvenster in te voeren.
4. Voer & (het ampersand) in na de celverwijzing D3 om een ​​tweede celverwijzing toe te voegen.
5. Selecteer cel E3 om de tweede celverwijzing in te voeren.
6. Voer , (een komma) in na de celverwijzing E3 om de invoer van het argument Lookup_value van de functie VERGELIJKEN te voltooien.

   
   In de laatste stap van de zelfstudie worden de Lookup_values ​​ingevoerd in de cellen D3 en E3 van het werkblad.

Voltooi de geneste MATCH-functie

Deze stap behandelt het toevoegen van het argument Lookup_array voor de geneste MATCH-functie. De Lookup_array is het cellenbereik dat de functie VERGELIJKEN doorzoekt om het argument Lookup_value te vinden dat in de vorige stap van de zelfstudie is toegevoegd.

Omdat er twee zoekvelden zijn geïdentificeerd in het argument Lookup_array, moet hetzelfde worden gedaan voor de Lookup_array. De functie VERGELIJKEN doorzoekt slechts één array voor elke opgegeven term. Om meerdere arrays in te voeren, gebruikt u het ampersand om de arrays samen te voegen.

1. Plaats de cursor aan het einde van de gegevens in het tekstvak Row_num. De cursor verschijnt na de komma aan het einde van het huidige item.
2. Markeer de cellen D6 tot en met D11 in het werkblad om het bereik in te voeren. Dit bereik is de eerste array die de functie doorzoekt.
3. Voer & (een ampersand) in na de celverwijzingen D6:D11. Dit symbool zorgt ervoor dat de functie twee arrays doorzoekt.
4. Markeer de cellen E6 tot en met E11 in het werkblad om het bereik in te voeren. Dit bereik is de tweede array die de functie doorzoekt.
5. Voer , (een komma) in na de celverwijzing E3 om de invoer van het argument Lookup_array van de functie VERGELIJKEN te voltooien.

   

6. Laat het dialoogvenster open voor de volgende stap in de zelfstudie.

Voeg het MATCH Type-argument toe

Het derde en laatste argument van de MATCH-functie is het Match_type argument. Dit argument vertelt Excel hoe de Lookup_value moet worden vergeleken met waarden in de Lookup_array. De beschikbare keuzes zijn 1, 0 of -1.

Dit argument is optioneel. Als deze wordt weggelaten, gebruikt de functie de standaardwaarde 1.

• Als Match_type = 1 of wordt weggelaten, vindt MATCH de grootste waarde die kleiner is dan of gelijk is aan de Lookup_value. De Lookup_array-gegevens moeten in oplopende volgorde worden gesorteerd.
• Als Match_type = 0, vindt MATCH de eerste waarde die gelijk is aan de Lookup_value. De Lookup_array-gegevens kunnen in willekeurige volgorde worden gesorteerd.
• Als Match_type = -1, vindt MATCH de kleinste waarde die groter is dan of gelijk is aan de Lookup_value. De Lookup_array-gegevens moeten in aflopende volgorde worden gesorteerd.

Voer deze stappen in na de komma die in de vorige stap is ingevoerd op de regel Row_num in de functie INDEX:

1. Voer 0 (een nul) in na de komma in het tekstvak Row_num. Dit getal zorgt ervoor dat de geneste functie exacte overeenkomsten retourneert met de termen die zijn ingevoerd in de cellen D3 en E3.
2. Voer ) in (een afsluitende ronde haak) om de MATCH-functie te voltooien.

   

3. Laat het dialoogvenster open voor de volgende stap in de zelfstudie.

Voltooi de INDEX-functie

De MATCH-functie is voltooid. Het is tijd om naar het tekstvak Column_num van het dialoogvenster te gaan en het laatste argument voor de INDEX-functie in te voeren. Dit argument vertelt Excel dat het kolomnummer in het bereik D6 tot en met F11 ligt. Dit bereik is waar het de informatie vindt die door de functie wordt geretourneerd. In dit geval een leverancier van titanium widgets.

1. Plaats de cursor in het tekstvak Column_num.
2. Voer 3 in (het getal drie). Dit nummer vertelt de formule om te zoeken naar gegevens in de derde kolom van het bereik D6 tot en met F11.

   

3. Laat het dialoogvenster open voor de volgende stap in de zelfstudie.

Maak de matrixformule

Verander de geneste functie in een matrixformule voordat u het dialoogvenster sluit. Met deze array kan de functie zoeken naar meerdere termen in de gegevenstabel. In deze tutorial komen twee termen overeen: Widgets uit kolom 1 en Titanium uit kolom 2.

Om een ​​matrixformule in Excel te maken, drukt u tegelijkertijd op de toetsen CTRL, SHIFT en ENTER. Eenmaal ingedrukt, wordt de functie omgeven door accolades, wat aangeeft dat de functie nu een array is.

1. Selecteer OK om het dialoogvenster te sluiten. Selecteer Gereed in Excel voor Mac.
2. Selecteer cel F3 om de formule te bekijken en plaats de cursor aan het einde van de formule in de formulebalk.
3. Druk op CTRL + SHIFT + ENTER om de formule naar een matrix te converteren.
4. Er verschijnt een fout #N/A in cel F3. Dit is de cel waarin de functie is ingevoerd.
5. De fout #N/A verschijnt in cel F3 omdat de cellen D3 en E3 leeg zijn. D3 en E3 zijn de cellen waar de functie naar zoekt om de Lookup_value te vinden. Nadat gegevens aan deze twee cellen zijn toegevoegd, wordt de fout vervangen door informatie uit de database.

   

Voeg de zoekcriteria toe

De laatste stap is het toevoegen van de zoektermen aan het werkblad. Deze stap komt overeen met de termen Widgets uit kolom 1 en Titanium uit kolom 2.

Als de formule een overeenkomst vindt voor beide termen in de juiste kolommen in de database, wordt de waarde uit de derde kolom geretourneerd.

1. Selecteer cel D3.
2. Voer Widgets in.
3. Selecteer cel E3.
4. Typ Titanium en druk op Enter.
5. De naam van de leverancier, Widgets Inc., verschijnt in cel F3. Dit is de enige vermelde leverancier die Titanium Widgets verkoopt.
6. Selecteer cel F3. De functie verschijnt in de formulebalk boven het werkblad. In dit voorbeeld is er maar één leverancier voor titanium widgets. Als er meer dan één leverancier was, wordt de leverancier die als eerste in de database wordt vermeld, geretourneerd door de functie.
   
{=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}