Wat te weten
- Maak eerst een INDEX-functie en start vervolgens de geneste MATCH-functie door het argument Lookup_value in te voeren.
- Voeg vervolgens het argument Lookup_array toe, gevolgd door het argument Match_type, en specificeer vervolgens het kolombereik.
- Verander de geneste functie in een matrixformule door op Ctrl+ Shift+ Enter te drukken. Voeg ten slotte de zoektermen toe aan het werkblad.
Dit artikel legt uit hoe u een opzoekformule kunt maken 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. Informatie omvat Excel voor Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 en Excel voor Mac.
Volg de handleiding
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 tutorial niet de opmaak bevat die in de afbeelding te zien is.)
- Voer het bovenste gegevensbereik in de cellen D1 tot F2 in.
- Voer het tweede bereik in de 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. Het matrixformulier retourneert de gegevens uit een database of tabel met gegevens. Het referentieformulier geeft de celverwijzing of locatie van de gegevens in de tabel.
In deze tutorial wordt het array-formulier 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:
- Selecteer cel F3 om het de actieve cel te maken. In deze cel wordt de geneste functie ingevoerd.
-
Ga naar Formules.
Image - Kies Lookup & Reference om de vervolgkeuzelijst met functies te openen.
- Selecteer INDEX om het dialoogvenster Select Arguments te openen.
- Kies array, row_num, column_num.
- Selecteer OK om het Functieargumenten dialoogvenster te openen. In Excel voor Mac wordt de Formula Builder geopend.
- Plaats de cursor in het Array tekstvak.
-
Markeer 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.
Image
Start de geneste MATCH-functie
Als de ene functie in een andere wordt genest, is het niet mogelijk om de formulebouwer van de tweede of geneste 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 door het argument Lookup_value in te voeren. De Lookup_value is de locatie of celverwijzing voor de zoekterm die in de database moet worden gevonden.
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 het ampersand-symbool (&).
- Plaats in het Functieargumenten dialoogvenster de cursor in het Row_num tekstvak.
- Enter MATCH(.
- Selecteer cel D3 om die celverwijzing in het dialoogvenster in te voeren.
- Voer & (het ampersand) in na de celverwijzing D3 om een tweede celverwijzing toe te voegen.
- Selecteer cel E3 om de tweede celverwijzing in te voeren.
-
Enter , (een komma) na de celverwijzing E3 om de invoer van het argument Lookup_value van de functie VERGELIJKEN te voltooien.
Image In de laatste stap van de tutorial 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 celbereik waarin de MATCH-functie zoekt om het argument Lookup_value te vinden dat in de vorige stap van de zelfstudie is toegevoegd.
Omdat er twee zoekvelden werden geïdentificeerd in het argument Lookup_array, moet hetzelfde worden gedaan voor de Lookup_array. De MATCH-functie zoekt slechts één array voor elke opgegeven term. Om meerdere arrays in te voeren, gebruik je het ampersand om de arrays samen te voegen.
- Plaats de cursor aan het einde van de gegevens in het Row_num tekstvak. De cursor verschijnt na de komma aan het einde van de huidige invoer.
- Markeer de cellen D6 tot en met D11 in het werkblad om het bereik in te voeren. Dit bereik is de eerste array waarin de functie zoekt.
- Voer & (een ampersand) in na de celverwijzingen D6:D11. Dit symbool zorgt ervoor dat de functie twee arrays doorzoekt.
- Markeer de cellen E6 tot en met E11 in het werkblad om het bereik in te voeren. Dit bereik is de tweede array waarin de functie zoekt.
-
Voer , (een komma) in na de celverwijzing E3 om de invoer van het argument Lookup_array van de MATCH-functie te voltooien.
Image - Laat het dialoogvenster open voor de volgende stap in de tutorial.
Voeg het MATCH-typeargument toe
Het derde en laatste argument van de MATCH-functie is het Match_type-argument. Dit argument vertelt Excel hoe de Lookup_value overeenkomt met waarden in de Lookup_array. De beschikbare keuzes zijn 1, 0 of -1.
Dit argument is optioneel. Als het 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.
- If Match_type=0, MATCH vindt de eerste waarde die gelijk is aan de Lookup_value. De Lookup_array-gegevens kunnen in elke volgorde worden gesorteerd.
- If Match_type=-1, MATCH vindt 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 INDEX-functie:
- Voer 0 (een nul) in na de komma in het Row_num tekstvak. Dit getal zorgt ervoor dat de geneste functie exacte overeenkomsten retourneert met de termen die zijn ingevoerd in de cellen D3 en E3.
-
Voer ) in (een sluitend ronde haakje) om de MATCH-functie te voltooien.
Image - Laat het dialoogvenster open voor de volgende stap in de tutorial.
Voltooi de INDEX-functie
De MATCH-functie is klaar. 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.
- Plaats de cursor in het Column_num tekstvak.
-
Voer 3 in (het getal drie). Dit getal vertelt de formule om naar gegevens te zoeken in de derde kolom van het bereik D6 tot en met F11.
Image - Laat het dialoogvenster open voor de volgende stap in de tutorial.
Maak de matrixformule
Voordat u het dialoogvenster sluit, verandert u de geneste functie in een matrixformule. 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.
Als u een matrixformule in Excel wilt maken, drukt u op de toetsen CTRL, SHIFT en ENTERtoetsen tegelijk. Eenmaal ingedrukt, wordt de functie omgeven door accolades, wat aangeeft dat de functie nu een array is.
- Selecteer OK om het dialoogvenster te sluiten. Selecteer in Excel voor Mac Klaar.
- Selecteer cel F3 om de formule te bekijken en plaats de cursor aan het einde van de formule in de formulebalk.
- Om de formule naar een matrix te converteren, drukt u op CTRL+ SHIFT+ ENTER.
- A N/A-fout verschijnt in cel F3. Dit is de cel waar de functie is ingevoerd.
-
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 de Lookup_value zoekt. Nadat gegevens aan deze twee cellen zijn toegevoegd, wordt de fout vervangen door informatie uit de database.
Image
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.
- Selecteer cel D3.
- Enter Widgets.
- Selecteer cel E3.
- Typ Titanium en druk op Enter.
- De naam van de leverancier, Widgets Inc., verschijnt in cel F3. Dit is de enige vermelde leverancier die Titanium Widgets verkoopt.
-
Selecteer cel F3. De functie verschijnt in de formulebalk boven het werkblad.
{=INDEX(D6:F11, MATCH(D3&E3, D6:D11&E6:E11, 0), 3)}
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.
Image
