Een Excel-opzoekformule maken met meerdere criteria?

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 Match_type argument en geef vervolgens het kolombereik op.
• Verander vervolgens de geneste functie in een matrixformule door op . te drukken Ctrl+Verschuiving+Enter. 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. 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 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 formulieren 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 Form 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. Kiezen Opzoeken en verwijzen om de vervolgkeuzelijst met functies te openen.

4. Selecteer INHOUDSOPGAVE om de te openen Argumenten selecteren dialoog venster.

5. Kiezen array,row_num,column_num.

6. Selecteer OK om de te openen Functieargumenten dialoog venster. In Excel voor Mac wordt de Formula Builder geopend.

7. Plaats de cursor in de Array tekstvak.

8. Markeer cellen D6 door 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

Wanneer 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 (&).

1. In de Functieargumenten dialoogvenster, plaats de cursor in de Row_num tekstvak.

2. Enter BIJ ELKAAR PASSEN(.

3. Selecteer cel D3 om die celverwijzing in het dialoogvenster in te voeren.

4. Enter & (het ampersand) na de celverwijzing D3 om een ​​tweede celverwijzing toe te voegen.

5. Selecteer cel E3 om de tweede celverwijzing in te voeren.

6. Enter , (een komma) 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 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 zijn 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, gebruikt u het ampersand om de arrays samen te voegen.

1. Plaats de cursor aan het einde van de gegevens in de Row_num tekstvak. De cursor verschijnt na de komma aan het einde van de huidige invoer.

2. Markeer cellen D6 door D11 in het werkblad om het bereik in te voeren. Dit bereik is de eerste array waarin de functie zoekt.

3. Enter & (een ampersand) na de celverwijzingen D6:D11. Dit symbool zorgt ervoor dat de functie twee arrays doorzoekt.

4. Markeer cellen E6 door E11 in het werkblad om het bereik in te voeren. Dit bereik is de tweede array waarin de functie zoekt.

5. Enter , (een komma) na de celverwijzing E3 om de invoer van het argument Lookup_array van de functie MATCH te voltooien.

   

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

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.
• Als Match_type = 0, vindt MATCH de eerste waarde die gelijk is aan de Lookup_value. De Lookup_array-gegevens kunnen in elke 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 INDEX-functie:

1. Enter 0 (een nul) na de komma in de 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.

2. Enter ) (een sluitend ronde haakje) 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 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 voor titanium widgets.

1. Plaats de cursor in de Column_num tekstvak.

2. Enter 3 (de nummer drie). Dit nummer vertelt de formule om naar gegevens te zoeken in de derde kolom van het bereik D6 tot en met F11.

   

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

De matrixformule maken

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 zelfstudie komen twee termen overeen: Widgets uit kolom 1 en Titanium uit kolom 2. Om een ​​matrixformule in Excel te maken, drukt u op de CTRL, VERSCHUIVING, en ENTER toetsen tegelijk. 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 in Excel voor Mac Gedaan.

2. Selecteer cel F3 om de formule te bekijken, plaats dan de cursor aan het einde van de formule in de formulebalk.

3. Om de formule naar een matrix te converteren, drukt u op CTRL+VERSCHUIVING+ENTER.

4. A #N/A fout verschijnt in cel F3. Dit is de cel waar 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 de Lookup_value zoekt. 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, retourneert deze de waarde uit de derde kolom.

1. Selecteer cel D3.

2. Enter Widgets.

3. Selecteer cel E3.

4. Type Titaniumen 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. {=INDEX(D6:F11,VERGELIJKEN(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, door de functie geretourneerd.