Hoe de XLOOKUP-functie in Excel te gebruiken

Wat is de Xlookup-functie in Excel ? Hoe gebruik ik Xlookup in Excel ? Laten we het uitzoeken met LuckyTemplates.com!

Als u regelmatig Excel gebruikt, bent u waarschijnlijk redelijk bekend met de opzoekfuncties. Als u het niet weet, hoeft u het alleen maar te begrijpen als een Excel-functie die wordt gebruikt om naar specifieke gegevens of waarden in rijen of kolommen in een spreadsheet te zoeken.

Deze functie is vooral handig als u veel gegevens in een Excel-spreadsheet moet verwerken. In Excel beschikt u over verschillende typen ZOEKEN-functies. XLOOKUP is er één van. Dus wat is de XLOOKUP-functie, wat is de XLOOKUP-formule en hoe gebruik je deze? Laten we het uitzoeken met LuckyTemplates.com!

Dingen die u moet weten over de XLOOKUP-functie in Excel

• Wat is XLOOKUP?
  • Hoe u toegang krijgt tot de XLOOKUP-functie
  • Syntaxis van de functie XLOOKUP
• Voor- en nadelen van de XLOOKUP-functie in Excel
  • Voordelen van de XLOOKUP-functie
  • Nadelen van de XLOOKUP-functie
• Hoe de XLOOKUP-functie te gebruiken
  • Er zijn geen extra kolomindexnummers
  • Standaard exacte match
  • XLOOKUP vindt u aan de linkerkant
  • Gebruik XLOOKUP om naar bereiken te zoeken
  • XLOOKUP vervangt de functie HLOOKUP
  • XLOOKUP kan van onder naar boven worden bekeken

Wat is de XLOOKUP-functie in Excel?

In wezen vervangt XLOOKUP oude functies zoals HLOOKUP, VLOOKUP en LOOKUP. Het ondersteunt het opzoeken van gegevens zowel verticaal als horizontaal.

Met andere woorden, de XLOOKUP-functie in Excel helpt u snel waarden te vinden in de verstrekte dataset, zowel horizontaal als verticaal. Vervolgens retourneert het de overeenkomstige waarde volgens verschillende rijen en kolommen.

Er zijn veel manieren om het gebruik van de XLOOKUP-functie te maximaliseren.

Hoe u toegang krijgt tot de XLOOKUP-functie

Momenteel is XLOOKUP alleen beschikbaar voor gebruikers van het Insiders-programma. Iedereen die deelneemt aan het Insider-programma heeft toegang tot de nieuwste Excel-functies zodra deze beschikbaar komen. Microsoft zal deze functie binnenkort uitrollen naar alle gebruikers van Office 365. XLOOKUP is helaas niet beschikbaar voor gebruikers met de versies Office 2010, 2013, 2016 en 2019.

Als u de Microsoft 365-suite niet op uw computer gebruikt, moet u mogelijk upgraden naar Microsoft 365 om toegang te krijgen tot de XLOOKUP-functionaliteit. En als u Microsoft 365 gebruikt, ziet u de optie ingeschakeld in Excel. XLOOKUP is ook beschikbaar op Office 365 Online.

Syntaxis van de functie XLOOKUP

De syntaxis van de functie XLOOKUP is vergelijkbaar met de syntaxis van de functies VERT.ZOEKEN en HLOOKUP. Als u ze al eerder heeft gebruikt, zult u het handiger vinden om XLOOKUP te gebruiken. Hier is de syntaxis voor de XLOOKUP-functie in Excel:

= XLOOKUP ( lookup_value , lookup_array , return_array , [if_not_found], [match_mode], [search_mode])

De functie XLOOKUP ondersteunt maximaal 6 argumenten en dit zijn hun waarden.

1. lookup_value (vereist): de waarde die u wilt vinden.

2. lookup_array (vereist): de array waarin u naar de opzoekwaarde wilt zoeken.

3. return_array (vereist): Array van waaruit u waarden wilt ophalen en retourneren wanneer de opzoekwaarde wordt gevonden.

4. [if_not_found] (optioneel): Als er geen overeenkomst wordt gevonden, is dit de geretourneerde waarde.

5. [match_mode] (optioneel): Met dit argument kunt u het matchtype opgeven waarnaar u op zoek bent. Er zijn verschillende waarden om dit te specificeren:

• 0 - Zoekt naar een exacte overeenkomst en de waarde moet exact overeenkomen met de waarde in lookup_array. Deze waarde wordt ook als standaard ingesteld als deze niet wordt vermeld.
• -1 - Zoekt naar een exacte match en eenmaal gevonden, keert hij terug naar de volgende kleinere waarde.
• 1 - Zoekt naar een exacte overeenkomst en retourneert de eerstvolgende grotere waarde als deze wordt gevonden.
• 2 - Voer gedeeltelijke matching uit met jokertekens waarbij *, ? en ~ heeft een speciale betekenis.

6. [search_mode] (optioneel): wordt gebruikt om de zoekmodus van XLOOKUP in lookup_array op te geven. Er zijn verschillende waarden om hetzelfde te specificeren:

• 1 - Voer een zoekopdracht uit vanaf het eerste item. Deze waarde wordt standaard ingesteld als er niets is opgegeven.
• -1 - Voert een omgekeerde zoekopdracht uit, beginnend bij het laatste item.
• 2 - Voer een binaire zoekopdracht uit in lookup_array waarbij de gegevens in oplopende volgorde moeten worden gesorteerd. Als gegevens niet worden gesorteerd, kunnen er fouten of verkeerde resultaten ontstaan.
• -2 - Voer een binaire zoekopdracht uit in lookup_array waarbij de gegevens in aflopende volgorde moeten worden gesorteerd. Als gegevens niet worden gesorteerd, kunnen er fouten of verkeerde resultaten ontstaan.

Voor- en nadelen van de XLOOKUP-functie in Excel

XLOOKUP is nog steeds een voordelige functie vergeleken met VLOOKUP en INDEX/ATCH. Maar het heeft ook zijn eigen nadelen.

Voordelen van de XLOOKUP-functie

• De kaak werkt zowel verticaal als horizontaal.
• Er zijn slechts 3 argumenten nodig, in plaats van 4 zoals in VLOOKUP en INDEX MATCH.
• Standaard altijd een exacte overeenkomst.
• Gedeeltelijke opzoekingen kunnen worden uitgevoerd met behulp van jokertekens.
• Opzoekingen kunnen in aflopende volgorde worden uitgevoerd.
• Gebruik één functie in plaats van twee in INDEX MATCH.

Nadelen van de XLOOKUP-functie

• Optionele argumenten kunnen voor beginners ingewikkeld lijken.
• Het kan tijdrovender zijn als u twee bereiken selecteert en er te veel cellen in het werkblad staan.
• Retourneert een fout bij het opzoeken en retourneren van een array die niet dezelfde lengte heeft.
• Onthoud zowel het opzoek- als het retourbereik.

Voordelen van het gebruik van de Excel-functie XLOOKUP

• XLOOKUP maakt de meest gebruikte formules in Excel eenvoudiger en minder foutgevoelig . U hoeft alleen maar =XLOOKUP (te vinden gegevens, lijst, resultatenlijst) te schrijven en u krijgt het antwoord of #N/A als de waarde niet wordt gevonden.
• De standaard opzoekresultaten zijn exact . Een van de nadelen van VERT.ZOEKEN is dat je FALSE als laatste parameter moet vermelden om het juiste resultaat te krijgen. XLOOKUP lost dit op door standaard een exacte match uit te voeren. U kunt de parameter Match Mode gebruiken om het opzoekgedrag te wijzigen als u dat wilt.
• De vierde parameter ondersteunt het geval waarin de waarde niet wordt gevonden . In de meeste zakelijke situaties zult u gedwongen worden de opzoekformule te 'verpakken' met IFERROR of IFNA om fouten te elimineren. XLOOKUP biedt een vierde parameter, zodat u het gewenste standaardresultaat kunt kennen als de waarde niet wordt gevonden.
• XLOOKUP biedt selectieparameters om speciale situaties te vinden . U kunt van boven of onder zoeken, op speciale tekens en snellere opties om gefilterde gegevens te vinden.

Hoe de XLOOKUP-functie te gebruiken

Bekijk het onderstaande voorbeeld om te begrijpen hoe XLOOKUP werkt. In dit voorbeeld moeten we voor elke ID in kolom A het onderdeel uit kolom F retourneren.

Dit is een voorbeeld dat zoekt naar exacte resultaten, maar de functie XLOOKUP vereist slechts drie soorten informatie.

De onderstaande afbeelding toont XLOOKUP met 5 argumenten, maar de eerste drie argumenten zijn nodig voor correcte resultaten. Laten we ons daarom op hen concentreren:

• Opzoekwaarde : Opzoekwaarde
• Opzoekarray : zoekbereik
• Return_array : bereik dat de waarde bevat die moet worden geretourneerd

In dit voorbeeld gebruiken we de formule:

=XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Laten we nu enkele voordelen van XLOOKUP ten opzichte van VLOOKUP onderzoeken.

Er zijn geen extra kolomindexnummers

Het derde argument van VERT.ZOEKEN is het opgeven van het aantal kolommen met informatie dat uit het tabelbereik moet worden geretourneerd. Dit is niet langer een probleem met XLOOKUP, omdat de gebruiker met de functie het retourbereik kan selecteren. (kolom F in dit voorbeeld).

En vergeet niet dat XLOOKUP de resterende gegevens van de geselecteerde cel kan zien, in tegenstelling tot VLOOKUP.

Ook zul je geen formulefouten tegenkomen bij het toevoegen van nieuwe kolommen. Als dit in het spreadsheet gebeurt, wordt het retourbereik automatisch aangepast.

Standaard exacte match

Bij gebruik van VERT.ZOEKEN moet de gebruiker desgewenst een exacte overeenkomst opgeven. Maar bij XLOOKUP is de standaard een exacte match. Dit vermindert het vierde argument en zorgt voor minder fouten voor nieuwe gebruikers. Kortom, XLOOKUP vereist minder vragen dan VLOOKUP en is gebruiksvriendelijk.

XLOOKUP vindt u aan de linkerkant

De mogelijkheid om opzoekbereiken te selecteren geeft XLOOKUP meer flexibiliteit dan VLOOKUP. Met XLOOKUP doet de volgorde van de kolommen in de tabel er niet toe.

VERT.ZOEKEN wordt beperkt door de meest linkse kolom van de tabel te doorzoeken en vervolgens terug te keren vanaf een opgegeven aantal kolommen aan de rechterkant.

In het onderstaande voorbeeld moeten we zoeken naar de ID (kolom E) en de naam van de persoon retourneren (kolom D).

Gebruik de onderstaande formule:

=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Gebruik XLOOKUP om naar bereiken te zoeken

Hoewel dit niet zo gebruikelijk is als exact zoeken, kunt u een zoekformule gebruiken om een ​​waarde binnen een bereik te vinden. Zo willen wij bijvoorbeeld een korting teruggeven, afhankelijk van het bestede bedrag.

Deze keer zijn we niet op zoek naar een specifieke waarde en moeten we weten binnen welk bereik de waarden in kolom B vallen in kolom E om de korting te bepalen.

XLOOKUP heeft een optioneel vierde argument (onthoud dat dit standaard de absolute overeenkomst is) genaamd match_mode.

U kunt zien dat XLOOKUP geschatte overeenkomsten beter kan vinden dan VLOOKUP.

Er is een optie om de dichtstbijzijnde overeenkomst kleiner dan (-1) of de dichtstbijzijnde overeenkomst groter dan (1) de zoekwaarde te vinden. Er is ook een optie om jokertekens (2) te gebruiken, zoals ? of *. Deze instelling is niet standaard ingeschakeld, zoals bij VERT.ZOEKEN.

De formule in dit voorbeeld retourneert de dichtstbijzijnde waarde kleiner dan de zoekwaarde als er geen exacte overeenkomst wordt gevonden:

=XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,-1)

Er was echter een fout in cel C7, de korting van 0% had moeten worden geretourneerd omdat er slechts 64 waren uitgegeven, wat niet voldeed aan de criteria om de korting te ontvangen.

Een ander voordeel van de functie XLOOKUP is dat er geen zoekbereik in oplopende volgorde nodig is, zoals VLOOKUP.

Voer een nieuwe rij in onder aan de opzoektabel en open vervolgens de formule. Vergroot het gebruiksbereik door op de hoeken te klikken en te slepen.

De formule corrigeert de fout onmiddellijk. Het heeft er geen probleem mee om 0 aan het einde van de bereiktabel te hebben.

XLOOKUP vervangt de functie HLOOKUP

Zoals gezegd kan de XLOOKUP-functie ook de HLOOKUP-functie vervangen. Eén functie kan twee functies vervangen, wat is er beter?

De functie HLOOKUP zoekt horizontaal omhoog en wordt gebruikt om per rij te zoeken.

Deze functie is niet zo beroemd als VERT.ZOEKEN, maar is handig in gevallen waarin de titel in kolom A staat en de gegevens in rijen 4 en 5, zoals in het onderstaande voorbeeld.

XLOOKUP kan in beide richtingen zoeken, langs kolommen en langs rijen.

In dit voorbeeld wordt een formule gebruikt om de verkoopwaarde te retourneren die is gekoppeld aan de naam in kolom A2. Het kijkt naar rij 4 voor de naam en retourneert de waarde uit rij 5:

=XLOOKUP(A2,B4:E4,B5:E5)

XLOOKUP kan van onder naar boven worden bekeken

Normaal gesproken moet u van boven naar beneden in een lijst kijken om de eerste keer dat een waarde voorkomt te vinden. XLOOKUP heeft een vijfde argument met de naam search_mode. Met dit argument kan de zoekopdracht worden omgeleid, zodat deze onderaan een lijst begint om de laatste aanwezigheid van een waarde te vinden.

In het onderstaande voorbeeld moeten we het voorraadniveau van elk product in kolom A vinden.

De opzoektabel is gerangschikt op datumvolgorde. We willen het voorraadniveau van de laatste controle retourneren (laatste exemplaar van Product-ID).

Het vijfde argument van de XLOOKUP-functie biedt vier opties. Hier gebruiken we de optie Zoeken van laatste naar eerste .

Formule gebruikt in dit voorbeeld:

=XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,-1)

In deze formule wordt het vierde argument weggelaten. Het is optioneel en we willen dat de standaard exact overeenkomt.

De XLOOKUP-functie is de verwachte opvolger van zowel de VLOOKUP- als de HLOOKUP-functie. In dit artikel is een reeks voorbeelden gebruikt om de voordelen van de XLOOKUP-functie te demonstreren.

XLOOKUP is een grote verbetering als het gaat om opzoekfuncties. De enige beperking van de XLOOKUP-functie is dat deze niet achterwaarts compatibel is. Als u spreadsheets deelt met oudere versies van Microsoft Office Suite, ontvangt u mogelijk fouten.

U hebt echter toegang tot XLOOKUP vanuit Office 365 Online. XLOOKUP is nog in ontwikkeling, dus het zal enige tijd duren voordat Excel-gebruikers het kunnen gebruiken.

Ik wens je succes!

Verschil tussen XLOOKUP en VERT.ZOEKEN

	VERZOEKEN	XLZOEKEN
Standaard exacte overeenkomst	N	Y
Retourneert de waarde rechts van de zoekopdracht	Y	Y
Retourneert de waarde links van de zoekopdracht	N	Y
Kijk per rij omhoog	N	Y
Retourneert meer dan één waarde	N	Y
Moet de opzoekkolom classificeren	Alleen geschatte overeenkomsten	Alleen binair zoeken
Zoek van boven naar beneden	Y	Y
Zoek van onder naar boven	N	Y
Binaire zoekopdracht	N	Y
Pas het bericht aan als de opzoekwaarde niet wordt gevonden	N	Y
Zoek op jokerteken	Y	Y
Exact zoeken	Y	Y
Geschatte zoekopdracht retourneert de volgende kleinere waarde	Y	Y
Geschatte zoekopdracht retourneert de volgende grotere waarde	N	Y
Er kan een onjuiste waarde worden geretourneerd als de opzoekwaarden niet zijn gesorteerd	Y - bij benadering	N - bij benadering, Y - binair
Als u een nieuwe kolom toevoegt om de tabel te vinden, kan de formule worden verbroken	Y	N

Waarom is XLOOKUP beter dan VERT.ZOEKEN?

Hoewel VLOOKUP zijn eigen toepassingen heeft, heeft het beperktere functies dan XLOOKUP. Dit maakt XLOOKUP de meest populaire keuze als er grote hoeveelheden gegevens moeten worden verwerkt. Het resultaat is dat het sneller werkt en minder fouten bevat.

• XLOOKUP beperkt het gegevensbereik niet : XLOOKUP is flexibeler dan VLOOKUP. VERT.ZOEKEN kan waarden opzoeken die zich alleen in de meest linkse kolom van een tabel bevinden en waarden retourneren uit de overeenkomstige kolom aan de rechterkant. Ondertussen vereist XLOOKUP alleen eenvoudiger stappen en kan waarden retourneren voor elke kolom in beide richtingen.
• Het standaardresultaat van XLOOKUP is de meest nauwkeurige waarde .
• Standaard vindt de functie VERT.ZOEKEN in Excel altijd geschatte resultaten . Als u echter exacte resultaten nodig heeft, moet u FALSE invoeren in het argument range_lookup.
• De functie XLOOKUP zoekt naar een opzoekwaarde in een specifiek bereik en gebruikt standaard altijd de exacte waarde. Als u er geen kunt vinden, kunt u het argument bewerken om een ​​geschatte overeenkomst te vinden die kleiner of groter is dan de opzoekwaarde.

Andere redenen:

• XLOOKUP kan het invoegen of verwijderen van kolommen verwerken.
• XLOOKUP implementeert verticale en horizontale zoekopdrachten.
• XLOOKUP biedt slimmere geschatte overeenkomsten.
• XLOOKUP heeft minder celverwijzingen nodig.