Excel’s nieuwe XLOOKUP zal VERT.ZOEKEN vervangen en een krachtige vervanging bieden voor een van de meest populaire functies van Excel. Deze nieuwe functie lost enkele beperkingen van VERT.ZOEKEN op en heeft extra functionaliteit. Dit is wat u moet weten.
Wat is XLOOKUP?
De nieuwe XLOOKUP-functie heeft oplossingen voor enkele van de grootste beperkingen van VERT.ZOEKEN. Bovendien vervangt het ook HORIZ.ZOEKEN. XLOOKUP kan bijvoorbeeld naar links kijken, is standaard een exacte overeenkomst en stelt u in staat een celbereik op te geven in plaats van een kolomnummer. VERT.ZOEKEN is niet zo eenvoudig te gebruiken of zo veelzijdig. We laten je zien hoe het allemaal werkt.
Op dit moment is XLOOKUP alleen beschikbaar voor gebruikers van het Insiders-programma. Iedereen kan lid worden van het Insiders-programma om toegang te krijgen tot de nieuwste Excel-functies zodra deze beschikbaar zijn. Microsoft begint het binnenkort uit te rollen naar alle Office 365-gebruikers.
Hoe de XLOOKUP-functie te gebruiken
Laten we meteen naar binnen duiken met een voorbeeld van XLOOKUP in actie. Neem de onderstaande voorbeeldgegevens. We willen de afdeling uit kolom F retourneren voor elke ID in kolom A.
Dit is een klassiek voorbeeld van het zoeken naar exacte overeenkomsten. De XLOOKUP-functie vereist slechts drie stukjes informatie.
De afbeelding hieronder toont XLOOKUP met zes argumenten, maar alleen de eerste drie zijn nodig voor een exacte match. Dus laten we ons op hen concentreren:
- Opzoekwaarde: Waar ben je naar op zoek.
- Opzoekmatrix: Waar moet je kijken.
- Return_array: het bereik met de waarde die moet worden geretourneerd.
De volgende formule werkt voor dit voorbeeld: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)
Laten we nu een aantal voordelen bekijken die XLOOKUP heeft ten opzichte van VERT.ZOEKEN hier.
Geen kolomindexnummer meer
Het beruchte derde argument van VERT.ZOEKEN was om het kolomnummer op te geven van de informatie die uit een tabelarray moet worden geretourneerd. Dit is niet langer een probleem omdat u met XLOOKUP het bereik kunt selecteren waaruit u wilt terugkeren (kolom F in dit voorbeeld).
En vergeet niet, XLOOKUP kan de gegevens links van de geselecteerde cel bekijken, in tegenstelling tot VERT.ZOEKEN. Hieronder meer hierover.
Je hebt ook niet langer het probleem van een verbroken formule wanneer nieuwe kolommen worden ingevoegd. Als dat in uw spreadsheet zou gebeuren, wordt het retourbereik automatisch aangepast.
Exact Match is de standaard
Bij het leren van VERT.ZOEKEN was het altijd verwarrend waarom je moest specificeren dat een exacte overeenkomst gewenst was.
Gelukkig gaat XLOOKUP standaard naar een exacte overeenkomst – de veel meest voorkomende reden om een opzoekformule te gebruiken). Dit vermindert de noodzaak om dat vijfde argument te beantwoorden en zorgt voor minder fouten door gebruikers die de formule nog niet kennen.
Kortom, XLOOKUP stelt minder vragen dan VLOOKUP, is gebruiksvriendelijker en ook duurzamer.
XLOOKUP kan naar links kijken
Door een opzoekbereik te kunnen selecteren, is XLOOKUP veelzijdiger dan VERT.ZOEKEN. Met XLOOKUP doet de volgorde van de tabelkolommen er niet toe.
VERT.ZOEKEN werd beperkt door te zoeken in de meest linkse kolom van een tabel en vervolgens terug te keren van een opgegeven aantal kolommen naar rechts.
In het onderstaande voorbeeld moeten we een ID opzoeken (kolom E) en de naam van de persoon retourneren (kolom D).
De volgende formule kan dit bereiken: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)
Wat te doen als het niet wordt gevonden
Gebruikers van opzoekfuncties zijn zeer bekend met het # N / A-foutbericht dat hen begroet wanneer hun VERT.ZOEKEN of hun MATCH-functie niet kan vinden wat het nodig heeft. En daar is vaak een logische reden voor.
Daarom onderzoeken gebruikers snel hoe ze deze fout kunnen verbergen, omdat deze niet correct of nuttig is. En er zijn natuurlijk manieren om dat te doen.
XLOOKUP wordt geleverd met zijn eigen ingebouwde “indien niet gevonden” -argument om dergelijke fouten af te handelen. Laten we het in actie zien met het vorige voorbeeld, maar met een verkeerd getypte ID.
De volgende formule geeft de tekst “Onjuiste ID” weer in plaats van het foutbericht: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")
XLOOKUP gebruiken voor het opzoeken van een bereik
Hoewel niet zo gebruikelijk als de exacte overeenkomst, is een zeer effectief gebruik van een opzoekformule het zoeken naar een waarde in bereiken. Neem het volgende voorbeeld. We willen de korting teruggeven afhankelijk van het bestede bedrag.
Dit keer zijn we niet op zoek naar een specifieke waarde. We moeten weten waar de waarden in kolom B binnen de bereiken in kolom E vallen. Dat zal de verdiende korting bepalen.
XLOOKUP heeft een optioneel vijfde argument (onthoud dat het standaard de exacte overeenkomst is) genaamd match mode.
U kunt zien dat XLOOKUP meer mogelijkheden heeft met overeenkomsten bij benadering dan die van VERT.ZOEKEN.
Er is de optie om de dichtstbijzijnde overeenkomst kleiner dan (-1) of het dichtst groter dan (1) de gezochte waarde te vinden. Er is ook een optie om jokertekens (2) te gebruiken, zoals de? of de *. Deze instelling is niet standaard ingeschakeld zoals bij VERT.ZOEKEN.
De formule in dit voorbeeld retourneert het dichtstbijzijnde kleiner dan de gezochte waarde als er geen exacte overeenkomst wordt gevonden: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)
Er is echter een fout in cel C7 waar de # N / A-fout wordt geretourneerd (het argument ‘indien niet gevonden’ is niet gebruikt). Dit had een korting van 0% moeten opleveren, omdat 64 uitgaven niet voldoen aan de criteria voor enige korting.
Een ander voordeel van de XLOOKUP-functie is dat het opzoekbereik niet in oplopende volgorde hoeft te zijn zoals VERT.ZOEKEN.
Voer een nieuwe rij in onder aan de opzoektabel en open vervolgens de formule. Breid het gebruikte bereik uit door op de hoeken te klikken en ze te verslepen.
De formule corrigeert de fout onmiddellijk. Het is geen probleem om de “0” onderaan het bereik te hebben.
Persoonlijk zou ik de tabel nog steeds sorteren op de opzoekkolom. “0” onderaan zou me gek maken. Maar het feit dat de formule niet kapot ging, is briljant.
XLOOKUP Vervangt ook de HLOOKUP-functie
Zoals vermeld, is de XLOOKUP-functie hier ook om HLOOKUP te vervangen. Een functie om twee te vervangen. Uitstekend!
De functie HORIZ.ZOEKEN is de horizontale zoekactie, die wordt gebruikt om langs rijen te zoeken.
Niet zo bekend als zijn broer of zus VERT.ZOEKEN, maar handig voor voorbeelden zoals hieronder, waarbij de kopteksten in kolom A staan en de gegevens op rij 4 en 5 staan.
XLOOKUP kan in beide richtingen kijken: kolommen omlaag en ook langs rijen. We hebben niet langer twee verschillende functies nodig.
In dit voorbeeld wordt de formule gebruikt om de verkoopwaarde te retourneren die betrekking heeft op de naam in cel A2. Het kijkt langs rij 4 om de naam te vinden, en retourneert de waarde van rij 5: =XLOOKUP(A2,B4:E4,B5:E5)
XLOOKUP kan van onderaf kijken
Meestal moet u een lijst doorzoeken om het eerste (vaak enige) exemplaar van een waarde te vinden. XLOOKUP heeft een zesde argument met de naam zoekmodus. Dit stelt ons in staat om de zoekactie onderaan te laten beginnen en een lijst op te zoeken om in plaats daarvan het laatste exemplaar van een waarde te vinden.
In onderstaand voorbeeld willen we het voorraadniveau per product in kolom A terugvinden.
De opzoektabel staat op datumvolgorde en er zijn meerdere voorraadcontroles per product. We willen het voorraadniveau retourneren van de laatste keer dat het werd gecontroleerd (laatste keer dat de product-ID voorkomt).
Het zesde argument van de XLOOKUP-functie biedt vier opties. We zijn geïnteresseerd in het gebruik van de optie “Zoek als laatste-naar-eerst”.
De ingevulde formule wordt hier weergegeven: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)
In deze formule werden het vierde en vijfde argument genegeerd. Het is optioneel en we wilden de standaard van een exacte match.
Naar boven afronden
De functie XLOOKUP is de langverwachte opvolger van zowel de functies VERT.ZOEKEN als HORIZ.ZOEKEN.
In dit artikel zijn verschillende voorbeelden gebruikt om de voordelen van XLOOKUP te demonstreren. Een daarvan is dat XLOOKUP kan worden gebruikt in bladen, werkmappen en ook met tabellen. De voorbeelden zijn in het artikel eenvoudig gehouden om ons begrip te helpen.
Omdat dynamische arrays binnenkort in Excel worden geïntroduceerd, kan het ook een reeks waarden retourneren. Dit is zeker het ontdekken waard.
De dagen van VERT.ZOEKEN zijn genummerd. XLOOKUP is hier en zal binnenkort de de facto opzoekformule zijn.