Meerdere waarden opzoeken in Excel: stap voor stap

Bij het werken met grote gegevenssets in Microsoft Excel kan het een uitdaging zijn om meerdere waarden te vinden die aan verschillende voorwaarden voldoen. Sommige ingebouwde functies, bijv. VERT.ZOEKEN, zijn oorspronkelijk ontworpen om met enkele waarden te werken.

Verschillende Excel-functies kunnen worden gecombineerd om meerdere waarden op te zoeken. Ze omvatten VLOOKUP-, INDEX-, MATCH- en IF-functies. Huidige versies van Excel kunnen dynamische arrays gebruiken, terwijl oudere versies matrixformules gebruiken.

Dit artikel laat u precies zien hoe u deze functies gebruikt in formules die meerdere waarden in uw gegevens zoeken.

Laten we gaan!

Inhoudsopgave

• VERT.ZOEKEN gebruiken met meerdere waarden
  • Wat zijn matrixformules?
  • VERT.ZOEKEN Voorbeeld
  • Laatste versies van Excel
  • Oudere versies
  • Exacte overeenkomst versus geschatte overeenkomst
  • Meer over VERT.ZOEKEN
• INDEX en MATCH gebruiken om meerdere waarden op te zoeken
  • INDEX en MATCH samen gebruiken in Excel 365
  • Werken met oudere versies van Excel
• Hoe de IF-functie te gebruiken om meerdere waarden te vinden
  • Huidige versies van Excel
  • Oudere versies van Excel
• SUMPRODUCT gebruiken voor meerdere criteria
• 3 veelvoorkomende fouten met opzoekfuncties
  • 1. #N/A fouten
  • 2. #REF! Fouten
  • 3. Circulaire fouten
• IFERROR gebruiken met opzoekfuncties
• 7 geavanceerde opzoektechnieken
  • 1. Relatieve positie opzoeken
  • 2. KLEINE functie
  • 3. RIJ-functie
  • 4. FILTER-functie
  • 5. Hulpkolommen
  • 6. Dynamische reeksen
  • 7. Vermogensquery
• Laatste gedachten

VERT.ZOEKEN gebruiken met meerdere waarden

De functie VERT.ZOEKEN wordt vaak gebruikt om afzonderlijke waarden in een reeks gegevens te vinden. Met deze opzoekformule kun je echter ook meerdere matches opzoeken in Excel.

Standaard retourneert het alleen de eerste overeenkomende waarde die het vindt. U kunt de functie echter aanpassen om meerdere waarden te retourneren door een matrixformule te gebruiken.

Wat zijn matrixformules?

Een matrixformule is een formule die berekeningen kan uitvoeren op gegevensreeksen. Het wordt een matrixformule genoemd omdat het een matrix van resultaten kan retourneren in plaats van een enkele waarde.

Er zijn verschillende eenvoudige stappen bij het maken van een matrixformule:

1. Selecteer een bereik van cellen om te zoeken.

2. Voer de formule in de formulebalk in.

3. Druk op Ctrl + Shift + Enter om het te voltooien.

De syntaxis van een matrixformule is vergelijkbaar met die van een gewone formule, maar bevat accolades {} rond de formule. De accolades geven aan dat de formule een matrixformule is en dat deze een matrix met waarden retourneert.

De voorbeelden in dit artikel laten u zien hoe u matrixformules correct gebruikt.

VERT.ZOEKEN Voorbeeld

Ons voorbeeld heeft vijf items in kolom A van een werkblad:

• Appel

• Banaan

• Brood

• Wortel

• Kers

De taak is om te controleren of er drie specifieke soorten fruit in deze lijst staan: appel, banaan en kers.

Laatste versies van Excel

De syntaxis van VERT.ZOEKEN is afhankelijk van de versie van Excel die u gebruikt.

De meest recente versies ( Excel 365 of Excel 2021 ) ondersteunen dynamische arrays. Met deze functie kunnen formules meerdere resultaten retourneren die in aangrenzende cellen "overlopen".

Dit is de syntaxis (het gelijkteken start de formule):

=VERT.ZOEKEN(opzoekwaarde, tabelmatrix, col_index_getal, [bereik_opzoeken])

• lookup_value: de waarde die u wilt opzoeken.

• table_array: De volledige gegevenstabel die u wilt doorzoeken.

• col_index_num: het tabelkolomnummer in de table_array dat de gegevens bevat die u wilt retourneren.

• range_lookup: Optioneel. Geeft aan of u een exacte overeenkomst of een benaderende overeenkomst wilt.

Ons specifieke voorbeeld gebruikt deze formule:

=VERT.ZOEKEN({“appel”,,”banaan”,,”kers”},A1:A5,1,ONWAAR)

Wanneer de formule wordt ingevoerd in cel B1, lopen de resultaten over in cellen C1 en D1. Deze afbeelding toont het voorbeeld in actie:

Oudere versies

Als u een oudere versie van Excel gebruikt die geen dynamische arrays ondersteunt (bijv. Excel 2019 of eerder), moet u een iets andere benadering gebruiken met een matrixformule.

Volg deze stappen:

1. Klik op de cel waar u de resultaten voor het eerste item wilt weergeven (bijvoorbeeld kolom B).

2. Typ de volgende formule zonder nog op Enter te drukken: =VERT.ZOEKEN({“appel”,,”banaan”,,”kers”}, A1:A5, 1, FALSE)

3. Druk op Ctrl + Shift + Enter om er een matrixformule van te maken.

4. Kopieer cel B1 en plak deze in de onderstaande cel (of gebruik de vulgrepen).

Wanneer u Ctrl + Shift + Enter gebruikt, voegt Excel accolades toe rond de formule. Dit geeft aan dat het een matrixformule is.

Exacte overeenkomst versus geschatte overeenkomst

De functie VERT.ZOEKEN gebruikt standaard een overeenkomst bij benadering. Dit betekent dat het de dichtstbijzijnde overeenkomst retourneert die het kan vinden, zelfs als de celwaarden geen exacte overeenkomst zijn.

Als u een exacte overeenkomst wilt uitvoeren, kunt u het argument range_lookup instellen op FALSE.

Houd er rekening mee dat overeenkomsten bij benadering het beste werken met geordende numerieke waarden. Het is meestal niet geschikt als de celwaarde tekst is.

Meer over VERT.ZOEKEN

Wil je meer weten over deze veelzijdige functie, bekijk dan deze artikelen:

• hoe twee kolommen in Excel te vergelijken met VERT.ZOEKEN

• hoe u afwijkingen kunt vinden met VERT.ZOEKEN

Nu u klaar bent met de functie VERT.ZOEKEN, laten we eens kijken naar twee andere functies die op een andere manier kunnen doen wat ze doen: INDEX en MATCH.

INDEX en MATCH gebruiken om meerdere waarden op te zoeken

U kunt de functies INDEX en MATCH combineren om meerdere waarden in meerdere rijen te vinden.

De INDEX-functie in Excel retourneert een waarde of verwijzing naar een cel binnen een opgegeven bereik.

=INDEX(matrix, rij_getal, [kolom_getal])

• array: het bereik van cellen waarin naar de waarde moet worden gezocht.

• row_num: Het rijnummer binnen de array waaruit een waarde moet worden geretourneerd.

• column_num: (Optioneel) Het kolomnummer binnen de array waaruit een waarde moet worden geretourneerd. Indien weggelaten, retourneert de functie de hele rij.

De MATCH-functie in Excel retourneert de positie van een waarde binnen een opgegeven bereik.

=VERGELIJKEN(opzoekwaarde; opzoekmatrix; [vergelijkingstype])

• lookup_value: de waarde waarnaar moet worden gezocht binnen de lookup_array.

• lookup_array: het cellenbereik dat moet worden doorzocht op de lookup_value.

• match_type: (Optioneel) Het type match dat moet worden uitgevoerd. Indien weggelaten, voert de functie een exacte overeenkomst uit.

INDEX en MATCH samen gebruiken in Excel 365

Als u INDEX en MATCH samen wilt gebruiken om meerdere waarden in Excel op te zoeken, moet u een matrixformule gebruiken.

Werkend met de eerdere voorbeeldgegevens, is dit de formule in Excel 365:

=INDEX(A1:A5, VERGELIJKEN({“appel”,,”banaan”,,”kers”}, A1:A5, 0))

Het bovenstaande voorbeeld valt als volgt uiteen:

• INDEX: dit retourneert de waarde van een cel in een opgegeven bereik op basis van een bepaald rij- en kolomnummer. In dit geval wordt de waarde uit het bereik A1:A5 geretourneerd.

• A1:A5: Dit is het gedefinieerde tabelbereik waar u naar de waarde zoekt en waaruit het resultaat wordt geretourneerd.

• MATCH: dit zoekt naar een gespecificeerd item in een celbereik en geeft de relatieve positie van dat item in het bereik.

• {“appel”,,”banaan”,,”kers”}: dit is de matrixconstante die de waarden bevat die u wilt opzoeken.

• A1:A5: dit is het bereik waarin MATCH zoekt naar de waarden van de matrixconstante.

• 0: dit is het matchtype voor de MATCH-functie. In dit geval is het 0, wat betekent dat u zoekt naar een exacte overeenkomst in plaats van een nauwe overeenkomst.

 Deze afbeelding toont de formule in actie:

Werken met oudere versies van Excel

Als u een ouder Excel-bestand gebruikt dat geen dynamische arrays ondersteunt (bijv. Excel 2019 of eerder), moet u een andere aanpak gebruiken.

Omdat oudere versies de formules die in aangrenzende cellen "overlopen" niet ondersteunen, moet u het gebruik opsplitsen in drie afzonderlijke formules.

Volg deze stappen:

1. Klik op de cel waar u het resultaat voor het eerste item wilt hebben (bijvoorbeeld cel B1)

2. Typ de onderstaande formule:

3. =INDEX(A1:A5, VERGELIJKEN(“appel”, A1:A5, 0))

4. Druk op Enter om de formule uit te voeren.

5. Typ deze formule in cel B2: =INDEX(A1:A5, MATCH(“banaan”, A1:A5, 0))

6. Typ deze formule in cel B3: =INDEX(A1:A5, MATCH(“kers”, A1:A5, 0))

Deze afbeelding toont celverwijzing B3:

INDEX- en MATCH-functies zijn niet de enige waarmee u meerdere waarden kunt vinden. In de volgende sectie bekijken we hoe u de ALS-functie als alternatief kunt gebruiken.

Hoe de IF-functie te gebruiken om meerdere waarden te vinden

Een andere manier om meerdere celwaarden op te zoeken op basis van bepaalde criteria, is door de ALS-functie te gebruiken met andere functies.

Met de ALS-functie kunt u meerdere voorwaarden testen en verschillende resultaten retourneren, afhankelijk van de uitkomst van die tests.

Stel dat u een tabel met verkoopgegevens hebt met kolommen voor Product en Verkoop. U wilt het verkoopbedrag van twee van de drie producten opzoeken en optellen.

Huidige versies van Excel

Om de som van de kolom Verkoop te vinden waar het product "Apple" of "Banaan" is met behulp van de ALS-functie, kunt u een matrixformule gebruiken met ALS-, SOM- en OF-functies.

Ervan uitgaande dat uw gegevens in cel A1 beginnen, gebruikt u de volgende formule:

=SOM(ALS((A2:A4=”Appel”)+(A2:A4=”Baan”), B2:B4, 0))

De sectie (A2:A4=”Appel”)+(A2:A4=”Banaan”) maakt een matrix met een waarde van 1 als de cel in bereik A2:A4 "Appel" of "Banaan" bevat, en anders 0 .

De IF-instructie controleert elk element van het array-argument. Als de waarde 1 is (dat wil zeggen, het product is "Appel" of "Banaan"), krijgt het de overeenkomstige waarde in de kolom Verkoop (bereik B2:B4); anders duurt het 0.

De SUM-functie telt de waarden van de ALS-functie op, waardoor de verkoopwaarden voor zowel "Apple" als "Banaan" effectief worden opgeteld.

Deze afbeelding toont de formule in actie op het zoekbereik:

Oudere versies van Excel

In Excel 2019 of eerder moet u een matrixformule gebruiken. Volg deze stappen:

1. Typ de formule maar druk niet op Enter.

2. Druk op Ctrl + Shift + Enter om er een matrixformule van te maken.

Excel voegt accolades {} toe rond de formule, wat aangeeft dat het een matrixformule is.

Vervolgens bekijken we hoe u SUMPRODUCT kunt gebruiken om verschillende waarden op te zoeken op basis van uw criteria. Laten we gaan!

SUMPRODUCT gebruiken voor meerdere criteria

Met de SOMPRODUCT-functie kunt u ook meerdere waarden opzoeken op basis van meerdere criteria.

Omdat het gebruik van een matrixformule niet vereist is, is de syntaxis hetzelfde, ongeacht de versie van Excel.

Met dezelfde gegevens als in het vorige voorbeeld ziet de formule er als volgt uit:

=SOMPRODUCT((A2:A4=”Appel”)+(A2:A4=”Banaan”), B2:B4)

De sectie (A2:A4=”Appel”)+(A2:A4=”Banaan”) maakt een matrix met een waarde van 1 als de cel in bereik A2:A4 "Appel" of "Banaan" bevat, en anders 0 .

De functie SOMPRODUCT vermenigvuldigt de elementen van de array met de overeenkomstige elementen in de kolom Verkoop (bereik B2:B4). Vervolgens worden de resulterende waarden bij elkaar opgeteld, waardoor de verkoopwaarden voor zowel "Apple" als "Banaan" effectief worden opgeteld.

De onderstaande formule laat het in actie zien:

Excel-functies zijn geweldig als ze werken zoals verwacht, maar soms kun je fouten tegenkomen. In het volgende gedeelte behandelen we enkele veelvoorkomende fouten en hoe u hiermee om kunt gaan.

3 veelvoorkomende fouten met opzoekfuncties

Opzoekfuncties kunnen soms fouten retourneren die frustrerend en tijdrovend kunnen zijn om op te lossen. De drie meest voorkomende fouten die u zult tegenkomen zijn:

1. #N/A Fouten

2. #REF! Fouten

3. Circulaire fouten

1. #N/A fouten

De fout #N/A treedt op wanneer de opzoekwaarde niet kan worden gevonden in de opzoekarray.

Er zijn verschillende redenen waarom deze fout kan optreden, waaronder:

• de opzoekwaarde is verkeerd gespeld of onjuist.

• de opzoekarray is niet in oplopende volgorde gesorteerd.

• de opzoekwaarde staat niet in de dataset.

Als de opzoekwaarde niet in de gegevensset staat, is dit nuttige informatie. Onervaren Excel-gebruikers kunnen echter denken dat #N/A betekent dat er iets mis is gegaan met de formule. In het volgende gedeelte wordt uitgelegd hoe u dit gebruiksvriendelijker kunt maken.

2. #REF! Fouten

De #REF! fout treedt op wanneer de opzoekarray of retourarray wordt verwijderd of verplaatst.

Deze fout kan worden verholpen door de celverwijzingen in de opzoekfunctie bij te werken.

3. Circulaire fouten

Terwijl u functies combineert in complexe formules, kan Excel u vertellen dat u een kringverwijzing hebt.

U zult merken dat deze gemakkelijker te onderzoeken zijn door onze gids voor het vinden van kringverwijzingen in Excel te gebruiken .

IFERROR gebruiken met opzoekfuncties

De IFERROR-functie is een handig hulpmiddel voor het afhandelen van fouten in opzoekfuncties. Hiermee kunt u een waarde of formule opgeven die moet worden geretourneerd als de opzoekfunctie een fout retourneert.

De syntaxis voor de functie ALS.FOUT is als volgt:

=ALS.FOUT(waarde; waarde_als_fout)

· waarde: waarde of formule die u wilt evalueren.

· Value_if_error: de waarde of formule die moet worden geretourneerd als het eerste argument een fout retourneert.

Stel dat u een functie VERT.ZOEKEN hebt die meerdere waarden in een tabel opzoekt. In de onderstaande afbeelding bestaat een van de waarden niet in het gezochte gegevensbereik.

Zoals u kunt zien, wordt de fout #N/A weergegeven, wat verwarrend kan zijn voor onervaren Excel-gebruikers.

In plaats daarvan kunt u IFERROR gebruiken om een ​​lege cel weer te geven of een bericht met de tekst "Niet gevonden" met deze syntaxis:

=ALS.FOUT(VERT.ZOEKEN(opzoekwaarde; tabelmatrix; kolomindex; ONWAAR); "Niet gevonden")

Als in dit voorbeeld de functie VERT.ZOEKEN een fout retourneert, retourneert de functie IFERROR in plaats daarvan het bericht "Niet gevonden".

Deze afbeelding toont de formule in actie. Kolom B heeft de ontbrekende waarde, terwijl kolom C en kolom D overeenkomsten hebben gevonden.

We hebben tot nu toe veel terrein behandeld en u bent eindelijk klaar om meer geavanceerde technieken voor opzoekingen te leren, wat het onderwerp is van de volgende sectie.

7 geavanceerde opzoektechnieken

Het opzoeken van meerdere waarden in Excel kan een uitdagende taak zijn, vooral als het gaat om grote datasets. U kunt prestatieproblemen ondervinden door trage verwerking.

Er zijn zeven geavanceerde opzoektechnieken die u kunt gebruiken om het proces eenvoudiger en efficiënter te maken.

• Relatieve positie opzoeken

• KLEINE functie

• RIJ functie

• FILTER-functie

• Helper kolommen

• Dynamische arrays

• Power-query

1. Relatieve positie opzoeken

Een van de eenvoudigste manieren om meerdere waarden in Excel op te zoeken, is door relatieve posities op te zoeken. Dit omvat het specificeren van de rij- en kolomoffsets van een bepaalde cel om de gewenste waarde te lokaliseren.

Als u bijvoorbeeld een gegevenstabel heeft en een waarde wilt opzoeken die zich twee rijen lager en drie kolommen rechts van een bepaalde cel bevindt, kunt u de volgende formule gebruiken:

=VERSCHUIVING(cel, 2, 3)

2. KLEINE functie

Een andere handige techniek voor het opzoeken van meerdere waarden is het gebruik van de functie SMALL. Deze functie retourneert de n-de kleinste waarde in een celbereik.

Door deze functie te combineren met andere opzoekfuncties zoals INDEX en MATCH, kunt u meerdere waarden opzoeken op basis van specifieke criteria.

De volgende formule zoekt bijvoorbeeld de op een na kleinste waarde op in een celbereik dat aan een bepaalde voorwaarde voldoet:

=INDEX(gegevens, VERGELIJKEN(KLEIN(ALS(criteria, bereik), 2), bereik, 0))

3. RIJ-functie

De ROW-functie kan ook worden gebruikt voor het opzoeken van meerdere waarden in Excel. Deze functie retourneert het rijnummer van een bepaalde cel, dat kan worden gebruikt om naar cellen in een gegevenstabel te verwijzen.

De volgende formule zoekt bijvoorbeeld een waarde op in een tabel op basis van een unieke identifier:

=INDEX(gegevens, VERGELIJKEN(unieke_identificatie, gegevens[unieke_identificatie], 0), kolom_index_nummer)

4. FILTER-functie

De functie FILTER is niet beschikbaar in oudere versies van Excel.

In Excel 365 kunt u het gebruiken om een ​​celbereik te filteren op basis van bepaalde criteria en alleen de waarden retourneren die aan die criteria voldoen. Dit is de syntaxis en drie argumenten:

=FILTER(matrix, opnemen, [indien_leeg])

• array: de specifieke gegevens die u wilt filteren.

• include: Het include-argument is de criteria of voorwaarden die u op de array wilt toepassen.

• [if_empty] (optioneel): de waarde die moet worden geretourneerd als er geen rijen of kolommen voldoen aan de criteria die zijn opgegeven in het include-argument.

De volgende formule werkt bijvoorbeeld op de voorbeeldgegevens om overeenkomsten te vinden voor twee van de drie items in de eerste kolom en hun overeenkomstige waarden in de tweede kolom op te tellen.

=SOM(FILTER(B2:B4, (A2:A4=”Appel”)+(A2:A4=”Banaan”)))

Deze afbeelding laat zien hoeveel rijen overeenkomen en de som van de verschillende waarden:

5. Hulpkolommen

U kunt een hulpkolom gebruiken om meerdere velden samen te voegen binnen een functie zoals VERT.ZOEKEN.

Stel, u werkt met voor- en achternaam op een apart blad. U kunt ze samenvoegen in een hulpkolom waarnaar wordt verwezen in de uiteindelijke formule.

6. Dynamische reeksen

Zoals u in eerdere voorbeelden hebt geleerd, kunnen Microsoft 365-gebruikers profiteren van dynamische matrices voor het opzoeken van meerdere waarden in Excel.

Met dynamische matrices kunt u meerdere waarden uit één formule retourneren, waardoor het gemakkelijker wordt om grote hoeveelheden gegevens op te zoeken.

7. Vermogensquery

Power Query is een krachtige tool in Excel die kan worden gebruikt om waarden te retourneren op basis van meerdere criteria.

Deze video vindt bijvoorbeeld rommelige gegevens in een spreadsheet en ruimt deze op.

Laatste gedachten

Dat is het einde van onze diepe duik in de kunst van het opzoeken van meerdere waarden in Excel! Zodra u de VLOOKUP-, INDEX-, MATCH- en matrixformules onder de knie hebt, zult u merken dat u als een warm mes door boter door complexe datasets heen suist.

De sleutel is om de syntaxis en de logica achter elke formule te begrijpen. Houd er rekening mee dat deze formules complex kunnen zijn, dus het is belangrijk om de tijd te nemen en uw formules grondig te testen voordat u erop vertrouwt voor belangrijke gegevensanalyse.

Excel is een krachtig hulpmiddel, maar het is slechts zo goed als de gebruiker. Dus blijf die vaardigheden aanscherpen, blijf experimenteren en u zult al snel de meester worden in het opzoeken van meerdere waarden. Tot de volgende keer, blijf die cijfers kraken en laat Excel het harde werk voor je doen!