Hoe retourneer je een waarde als een bepaalde waarde bestaat in een bepaald bereik in Excel?
Bij het werken met gegevens in Excel is het vaak noodzakelijk om te bepalen of een specifieke waarde voorkomt binnen een gedefinieerd bereik, en zo ja, om een waarde uit een aangrenzende cel die overeenkomt met die invoer op te halen. Bijvoorbeeld, zoals te zien is in de linker screenshot, als je op zoek bent naar het getal 5 binnen een lijst of bereik, wil je mogelijk automatisch de bijbehorende aangrenzende waarde retourneren – wat handig kan zijn voor taken zoals het opzoeken van product-ID's, gebruikersinformatie ophalen of codes matchen aan waarden zonder handmatig te zoeken.
Retourneer een waarde als een bepaalde waarde bestaat in een bepaald bereik
Retourneer een waarde als een bepaalde waarde bestaat in een bepaald bereik door gebruik te maken van de VLOOKUP-functie
Om snel een waarde die gekoppeld is aan een specifieke invoer uit een datatabel of bereik in Excel op te halen, biedt de VLOOKUP-functie een eenvoudige oplossing.
Deze methode is vooral effectief als uw zoekkolom (waar u de waarde zoekt) de meest linkse kolom van uw gegevensbereik is, en u gegevens wilt retourneren uit een kolom rechts ervan. Het wordt vaak gebruikt om codes, namen, ID’s of referentienummers te zoeken en gerelateerde details gemakkelijk op te halen.
1. Selecteer een lege cel waar u het resultaat wilt laten verschijnen. Voer de volgende formule in de Formulebalk in:
=VLOOKUP(E2,A2:C8,3,TRUE)Druk op Enter om de formule uit te voeren. Zie screenshot:
In dit voorbeeld, als het getal 5 (in cel E2) wordt gevonden binnen het gespecificeerde getallenbereik in kolom A (bijvoorbeeld tussen 4 en 6), zal Excel deze waarde opzoeken en onmiddellijk de bijbehorende waarde uit de derde kolom (kolom C) van bereik A2:C8 invullen in uw geselecteerde cel. In de illustratie wordt “Addin 012” geretourneerd omdat het getal 5 zich bevindt binnen het bereik 4-6.
Opmerking: In de formule verwijst E2 naar de zoekwaarde, A2:C8 is het gegevensbereik dat de zoekwaarden en de terugkerende waarden omvat, en 3 geeft aan dat de retourwaarde uit de derde kolom van het gespecificeerde bereik moet komen. Pas deze verwijzingen aan zoals nodig voor uw werkblad.
Tips & Valkuilen:
- Zorg ervoor dat het zoekbereik (A2:C8) zowel de zoekkolom als de retourkolom omvat.
- Wanneer u VLOOKUP gebruikt met het argument WAAR, moet de zoekkolom in oplopende volgorde gesorteerd zijn, anders krijgt u mogelijk onverwachte resultaten.
- Gebruik ONWAAR als vierde argument voor exacte overeenkomsten, maar voor bereikzoekopdrachten (zoals in dit voorbeeld) laat u het op WAAR staan.
- Als uw gegevens vaak veranderen, controleer dan uw verwijzingen dubbel om fouten in de uitlijning te voorkomen.
Retourneer een waarde als een bepaalde waarde bestaat in een bepaald bereik door gebruik te maken van INDEX en MATCH-functies
De combinatie van INDEX en MATCH is een flexibele methode om een waarde te retourneren wanneer een bepaalde waarde bestaat in een bepaald bereik. In tegenstelling tot VLOOKUP, kunnen INDEX en MATCH een waarde in elke kolom zoeken en een resultaat uit elke andere kolom retourneren, ongeacht de volgorde. Dit is vooral handig als uw zoekkolom niet de meest linkse kolom is of als u meer aanpasbaarheid nodig hebt in uw datastructuur.
1. Selecteer een lege cel waar u het resultaat wilt laten verschijnen (bijvoorbeeld F2). Voer de volgende formule in in de Formulebalk:
=INDEX(C2:C8, MATCH(E2, A2:A8,1))Druk op Enter om de formule te bevestigen.
- MATCH(E2, A2:A8, 1) zoekt naar de positie van de grootste waarde ≤ E2 in kolom A. (Dit vereist dat kolom A in oplopende volgorde is gesorteerd.)
- INDEX(C2:C8, ...) retourneert de waarde uit kolom C op de rijnummer gevonden door MATCH.
Deze formule zoekt naar de waarde in E2 binnen het bereik A2:A8. Als de waarde wordt gevonden (bijvoorbeeld, 5 ligt tussen 4 en 6 in een van de rijen), retourneert de MATCH-functie de relatieve positie, en haalt INDEX de waarde uit de overeenkomstige rij van C2:C8. De '1' in MATCH geeft een benaderende overeenkomst aan, dus zorg ervoor dat uw zoekbereik correct is gesorteerd.
- Als u een exacte overeenkomst wilt, gebruikt u
0als derde argument in MATCH. - INDEX en MATCH ondersteunen zowel verticale als horizontale gegevensoriëntatie.
- Als de waarde niet wordt gevonden, retourneert de formule #N/B; overweeg om te wrappen met
ALS.FOUTvoor vriendelijkere uitkomsten.
Retourneer een waarde als een bepaalde waarde bestaat in een bepaald bereik door gebruik te maken van XLOOKUP-functie
De XLOOKUP-functie is een moderne alternatief voor het opzoeken van waarden in Excel 365 en Excel 2019. XLOOKUP overwint veel van de beperkingen van VLOOKUP, zoals restricties op de positie van de zoekkolom en automatische exacte/benaderende overeenkomsten.
1. Typ de volgende formule in uw gewenste uitvoercel (bijvoorbeeld F2):
=XLOOKUP(1, (E2>=A2:A8)*(E2<=B2:B8), C2:C8)Na het invoeren van de formule drukt u op Enter om het resultaat in de geselecteerde cel te zien.
- (E2>=A2:A8) controleert of E2 groter dan of gelijk is aan elke waarde in kolom A.
- (E2<=B2:B8) controleert of E2 kleiner dan of gelijk is aan elke waarde in kolom B.
- Het vermenigvuldigen van deze twee voorwaarden creëert een array van 1 en 0, waarbij 1 betekent dat E2 tussen A en B ligt op die rij.
- XLOOKUP(1, ..., C2:C8) zoekt naar de eerste 1 en retourneert de bijbehorende waarde uit kolom C.
- XLOOKUP past dynamisch aan als kolommen worden ingevoegd of verplaatst, in tegenstelling tot VLOOKUP met vaste kolomnummers.
- Werkt voor zowel verticale als horizontale gegevens.
- Vereist Excel 365 of 2021; voor oudere versies, gebruik andere methoden zoals hierboven beschreven.
Ontdek de Magie van Excel met Kutools AI
- Slimme Uitvoering: Voer celbewerkingen uit, analyseer gegevens en maak diagrammen – allemaal aangestuurd door eenvoudige commando's.
- Aangepaste Formules: Genereer op maat gemaakte formules om uw workflows te versnellen.
- VBA-codering: Schrijf en implementeer VBA-code moeiteloos.
- Formule-uitleg: Begrijp complexe formules gemakkelijk.
- Tekstvertaling: Overbrug taalbarrières binnen uw spreadsheets.
Gerelateerde Artikelen:
- Hoe vind je een waarde via VLOOKUP en retourneer je Waar of Onwaar / Ja of Nee in Excel?
- Hoe retourneer je een waarde via VLOOKUP in een aangrenzende of volgende cel in Excel?
- Hoe retourneer je een waarde in een andere cel als een cel bepaalde tekst bevat in Excel?
Beste productiviteitstools voor Office
Verbeter je Excel-vaardigheden met Kutools voor Excel en ervaar ongeëvenaarde efficiëntie. Kutools voor Excel biedt meer dan300 geavanceerde functies om je productiviteit te verhogen en tijd te besparen. Klik hier om de functie te kiezen die je het meest nodig hebt...
Office Tab brengt een tabbladinterface naar Office en maakt je werk veel eenvoudiger
- Activeer tabbladbewerking en -lezen in Word, Excel, PowerPoint, Publisher, Access, Visio en Project.
- Open en maak meerdere documenten in nieuwe tabbladen van hetzelfde venster, in plaats van in nieuwe vensters.
- Verhoog je productiviteit met50% en bespaar dagelijks honderden muisklikken!
Alle Kutools-invoegtoepassingen. Eén installatieprogramma
Kutools for Office-suite bundelt invoegtoepassingen voor Excel, Word, Outlook & PowerPoint plus Office Tab Pro, ideaal voor teams die werken met Office-toepassingen.
- Alles-in-één suite — invoegtoepassingen voor Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Eén installatieprogramma, één licentie — in enkele minuten geïnstalleerd (MSI-ready)
- Werkt beter samen — gestroomlijnde productiviteit over meerdere Office-toepassingen
- 30 dagen volledige proef — geen registratie, geen creditcard nodig
- Beste prijs — bespaar ten opzichte van losse aanschaf van invoegtoepassingen