Excel XLOOKUP-functie: alles wat u moet weten (10 voorbeelden)

Bekijk video - Excel XLOOKUP-functie (10 XLOOKUP-voorbeelden)

Excel XZOEKEN-functie is eindelijk aangekomen.

Als je VERT.ZOEKEN of INDEX/MATCH hebt gebruikt, weet ik zeker dat je dol zult zijn op de flexibiliteit die de XLZOEKEN-functie biedt.

In deze zelfstudie zal ik alles bespreken wat er te weten valt over de XLOOKUP-functie en enkele voorbeelden die u zullen helpen te weten hoe u deze het beste kunt gebruiken.

Dus laten we beginnen!

Wat is XLOOKUP?

XLOOKUP is een nieuwe functie is Office 365 en is een nieuwe en verbeterde versie van de VLOOKUP/HLOOKUP-functie.

Het doet alles wat VLOOKUP vroeger deed, en nog veel meer.

XZOEKEN is een functie waarmee u snel naar een waarde in een gegevensset (verticaal of horizontaal) kunt zoeken en de overeenkomstige waarde in een andere rij/kolom kunt retourneren.

Als u bijvoorbeeld de scores van studenten voor een examen hebt, kunt u XZOEKEN gebruiken om snel te controleren hoeveel een student heeft gescoord met behulp van de naam van de student.

De kracht van deze functie zal nog duidelijker worden naarmate ik dieper in een aantal duik XZOEKEN voorbeelden verderop in deze zelfstudie.

Maar voordat ik inga op de voorbeelden, is er een grote vraag: hoe krijg ik toegang tot XLOOKUP?

Hoe krijg ik toegang tot XLOOKUP?

Vanaf nu is XLOOKUP alleen beschikbaar voor de gebruikers van Office 365.

Dus als u eerdere versies van Excel (2010/2013/2016/2019) gebruikt, kunt u deze functie niet gebruiken.

Ik weet ook niet zeker of dit ooit voor eerdere versies zou worden uitgebracht of niet (misschien kan Microsoft een invoegtoepassing maken zoals ze deden voor Power Query). Maar vanaf nu kun je het alleen gebruiken als je Office 365 gebruikt.

Klik hier om te upgraden naar Office 365

Als u al Office 365 gebruikt (thuis-, persoonlijk- of universiteitseditie) en er geen toegang toe hebt, kunt u naar het tabblad Bestand gaan en vervolgens op Account klikken.

Er zou een Office Insider-programma zijn en u kunt klikken en deelnemen aan het Office Insider-programma. Dit geeft u toegang tot de XZOEKEN-functie.

Ik verwacht dat XLOOKUP binnenkort beschikbaar zal zijn op alle Office 365-versies.

Opmerking: XLOOKUP is ook beschikbaar voor Office 365 voor Mac en Excel voor het web (Excel online)

XZOEKEN Functie Syntaxis

Hieronder staat de syntaxis van de XZOEKEN-functie:

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

Als je VERT.ZOEKEN hebt gebruikt, zul je merken dat de syntaxis vrij gelijkaardig is, met natuurlijk een aantal geweldige extra functies.

Maak je geen zorgen als de syntaxis en het argument een beetje te veel lijken. Ik behandel deze later in deze tutorial met enkele eenvoudige XLOOKUP-voorbeelden die het glashelder zullen maken.

De XLOOKUP-functie kan 6 argumenten bevatten (3 verplicht en 3 optioneel):

1. opzoekwaarde - de waarde die u zoekt
2. lookup_array - de array waarin u de opzoekwaarde zoekt
3. return_array - de array waaruit u de waarde wilt ophalen en retourneren (overeenkomend met de positie waar de opzoekwaarde wordt gevonden)
4. [if_not_found] - de waarde die moet worden geretourneerd als de opzoekwaarde niet wordt gevonden. Als u dit argument niet opgeeft, wordt een fout #N/A geretourneerd
5. [match_mode] - Hier kunt u het gewenste type wedstrijd specificeren:
   • 0 - Exacte overeenkomst, waarbij de lookup_value exact moet overeenkomen met de waarde in de lookup_array. Dit is de standaardoptie.
   • -1 - Zoekt naar de exacte overeenkomst, maar als deze wordt gevonden, wordt het volgende kleinere item/waarde geretourneerd
   • 1 - Zoekt naar de exacte overeenkomst, maar als deze wordt gevonden, wordt het eerstvolgende grotere item/waarde geretourneerd
   • 2 - Gedeeltelijk matchen met jokertekens (* of ~)
6. [zoekmodus] - Hier specificeert u hoe de XLOOKUP-functie moet zoeken in de lookup_array
   • 1 - Dit is de standaardoptie waarbij de functie begint te zoeken naar de lookup_value van boven (eerste item) naar beneden (laatste item) in de lookup_array
   • -1 - Zoekt van onder naar boven. Handig wanneer u de laatste overeenkomende waarde in de lookup_array wilt vinden
   • 2 - Voert een binaire zoekopdracht uit waarbij de gegevens in oplopende volgorde moeten worden gesorteerd. Indien niet gesorteerd, kan dit fouten of verkeerde resultaten geven
   • -2 - Voert een binaire zoekopdracht uit waarbij de gegevens in aflopende volgorde moeten worden gesorteerd. Indien niet gesorteerd, kan dit fouten of verkeerde resultaten geven

XZOEKEN Functie Voorbeelden

Laten we nu naar het interessante deel gaan - enkele praktische XLOOKUP-voorbeelden.

Deze voorbeelden zullen u helpen beter te begrijpen hoe XZOEKEN werkt, hoe het verschilt van VERT.ZOEKEN en INDEX/VERGELIJKEN en enkele verbeteringen en beperkingen van deze functie.

Klik hier om het voorbeeldbestand te downloaden en volg mee

Voorbeeld 1: Een opzoekwaarde ophalen

Stel dat je de volgende dataset hebt en je wilt de wiskundescore voor Greg ophalen (de opzoekwaarde).

Hieronder is de formule die dit doet:

=XZOEKEN(F2,A2:A15,B2:B15)

In de bovenstaande formule heb ik zojuist de verplichte argumenten gebruikt waar het naar de naam zoekt (van boven naar beneden), een exacte overeenkomst vindt en de overeenkomstige waarde van B2:B15 retourneert.

Een duidelijk verschil dat de XLOOKUP- en VLOOKUP-functie heeft, is de manier waarop ze de lookup-array verwerken. In VERT.ZOEKEN hebt u de hele array waar de opzoekwaarde zich in de meest linkse kolom bevindt en vervolgens geeft u het kolomnummer op van waaruit u het resultaat wilt ophalen. Met XLOOKUP daarentegen kun je lookup_array en return_array afzonderlijk kiezen

Een direct voordeel van het hebben van de lookup_array en return_array als afzonderlijke argumenten betekent dat u nu kunt kijk naar links. VERT.ZOEKEN had deze beperking waarbij je alleen een waarde kunt opzoeken en vinden die zich aan de rechterkant bevindt. Maar met XLOOKUP is die beperking verdwenen.

Hier is een voorbeeld. Ik heb dezelfde dataset, met de naam aan de rechterkant en de return_range aan de linkerkant.

Hieronder staat de formule die ik kan gebruiken om de score voor Greg in Math te krijgen (wat betekent dat ik links van de lookup_value moet kijken)

=XZOEKEN(F2,D2:D15,A2:A15)

XLOOKUP lost een ander groot probleem op - Als u een nieuwe kolom invoegt of kolommen verplaatst, zijn de resulterende gegevens nog steeds correct. VERT.ZOEKEN zou in dergelijke gevallen waarschijnlijk breken of een onjuist resultaat geven, aangezien de kolomindexwaarde meestal hard gecodeerd is.

Voorbeeld 2: Een volledig record opzoeken en ophalen

Laten we dezelfde gegevens als voorbeeld nemen.

In dit geval wil ik niet alleen de score van Greg in wiskunde ophalen. Ik wil de scores in alle vakken hebben.

In dit geval kan ik de onderstaande formule gebruiken:

=XZOEKEN(F2,A2:A15,B2:D15)

De bovenstaande formule gebruikt een return_array-bereik dat meer is dan een kolom (B2:D15). Dus wanneer de opzoekwaarde wordt gevonden in A2:A15, retourneert de formule de hele rij uit de return_array.

U kunt ook niet alleen cellen verwijderen die deel uitmaken van de array die automatisch is ingevuld. In dit voorbeeld kunt u H2 of I2 niet verwijderen. Als je het probeert, gebeurt er niets. Als u deze cellen selecteert, wordt de formule in de formulebalk grijs weergegeven (om aan te geven dat deze niet kan worden gewijzigd)

U kunt de formule in cel G2 verwijderen (waar we deze oorspronkelijk hebben ingevoerd), het volledige resultaat wordt verwijderd.

Dit is een nuttige verbetering, aangezien eerder bij VERT.ZOEKEN u het kolomnummer afzonderlijk voor elke formule moet opgeven.

Voorbeeld 3: Tweerichtings opzoeken met XLOOKUP (horizontaal en verticaal opzoeken)

Hieronder staat een dataset waarvan ik de score van Greg in Math (het onderwerp in cel G2) wil weten.

Dit kan door middel van een two-way lookup waarbij ik zoek naar de naam in kolom A en de vaknaam in rij 1. Het voordeel van deze two-way lookup is dat het resultaat onafhankelijk is van de studentnaam van de vaknaam. Als ik de onderwerpnaam verander in Chemie, zou deze tweerichtings-XZOEKEN-formule nog steeds werken en me het juiste resultaat geven.

Hieronder vindt u de formule die de tweerichtingszoekopdracht uitvoert en het juiste resultaat geeft:

=XLZOEKEN(G1,B1:D1,XLZOEKEN(F2,A2:A15,B2:D15))

Deze formule gebruikt een geneste XLOOKUP, waarbij ik deze eerst gebruik om alle cijfers van de student in cel F2 op te halen.

Dus het resultaat van XLOOKUP(F2,A2:A15,B2:D15) is {21,94,81}, wat in dit geval een reeks punten is die door Greg zijn gescoord.

Dit wordt vervolgens opnieuw gebruikt in de buitenste XZOEKEN-formule als de retourarray. In de buitenste XLOOKUP-formule zoek ik naar de onderwerpnaam (die zich in cel G1) bevindt en de opzoekarray is B1:D1.

Als de onderwerpnaam Math is, haalt deze buitenste XZOEKEN-formule de eerste waarde op uit de retourarray - die in dit voorbeeld {21,94,81} is.

Dit doet hetzelfde wat tot nu toe werd bereikt met de combinatie INDEX en MATCH

Klik hier om het voorbeeldbestand te downloaden en volg mee

Voorbeeld 4: Wanneer opzoekwaarde niet wordt gevonden (Foutafhandeling)

Foutafhandeling is nu toegevoegd aan de XZOEKEN-formule.

Het vierde argument in de XLOOKUP-functie is [if_not_found], waar u kunt specificeren wat u wilt voor het geval de lookup niet kan worden gevonden.

Stel dat je de dataset hebt zoals hieronder weergegeven, waar je de Math-score wilt krijgen voor het geval als de overeenkomst, en in het geval dat de naam niet wordt gevonden, je wilt terugkeren - 'Niet verschenen'

De onderstaande formule doet dit:

=XLOOKUP(F2,A2:A15,B2:B15,"Niet verschenen")

In dit geval heb ik hard gecodeerd wat ik wil krijgen voor het geval er geen overeenkomst is. U kunt ook een celverwijzing naar een cel of een formule gebruiken.

Voorbeeld 5: Geneste XLOOKUP (opzoeken in meerdere bereiken)

Het geniale van het [if_not_found]-argument is dat je hiermee kunt gebruiken geneste XZOEKEN-formule.

Stel dat u bijvoorbeeld twee afzonderlijke lijsten heeft, zoals hieronder weergegeven. Hoewel ik deze twee tabellen op hetzelfde blad heb, kunt u deze in afzonderlijke bladen of zelfs in werkmappen plaatsen.

Hieronder vindt u de geneste XZOEKEN-formule die de naam in beide tabellen controleert en de overeenkomstige waarde uit de opgegeven kolom retourneert.

=XLZOEKEN(A12,A2:A8,B2:B8,XLZOEKEN(A12,F2:F8,G2:G8))

In de bovenstaande formule heb ik het argument [if_not_found] gebruikt om een ​​andere XLOOKUP-formule te gebruiken. Hiermee kunt u de tweede XZOEKEN in dezelfde formule toevoegen en twee tabellen scannen met een enkele formule.

Ik weet niet zeker hoeveel geneste XLOOKUP's u in een formule kunt gebruiken. Ik heb het tot 10 uur geprobeerd en het werkte, toen gaf ik het op

Voorbeeld 6: Vind de laatste overeenkomende waarde

Deze was hard nodig en XLOOKUP maakte dit mogelijk. U hoeft nu geen ingewikkelde manieren te vinden om de laatste overeenkomende waarde in een bereik te krijgen.

Stel dat u de onderstaande dataset heeft en u wilt controleren wanneer de laatste persoon op elke afdeling is aangenomen en wat de aanwervingsdatum was.

De onderstaande formule zoekt de laatste waarde voor elke afdeling op en geeft de naam van de laatste aanwerving:

=XZOEKEN(F1,$B$2:$B$15,$A$2:$A$15,,,-1)

En de onderstaande formule geeft de huurdatum van de laatste huur voor elke afdeling:

=XZOEKEN(F1,$B$2:$B$15,$C$2:$C$15,,,-1)

Omdat XLOOKUP een ingebouwde functie heeft om de richting van de zoekopdracht te specificeren (van eerste naar laatste of laatste naar eerste), wordt dit gedaan met een eenvoudige formule. Bij verticale gegevens kijken VERT.ZOEKEN en INDEX/VERGELIJKEN altijd van boven naar beneden, maar met XZOEKEN kunnen ze ook de richting van beneden naar boven specificeren.

Voorbeeld 7: Geschatte overeenkomst met XLOOKUP (Find Tax Rate)

Een andere opmerkelijke verbetering met XLOOKUP is dat er nu vier matchmodi zijn (VLOOKUP heeft er 2 en MATCH heeft er 3).

U kunt een van de vier argumenten opgeven om te bepalen hoe de opzoekwaarde moet worden vergeleken:

• 0 - Exacte overeenkomst, waarbij de lookup_value exact moet overeenkomen met de waarde in de lookup_array. Dit is de standaardoptie.
• -1 - Zoekt naar de exacte overeenkomst, maar als deze wordt gevonden, wordt het volgende kleinere item/waarde geretourneerd
• 1 - Zoekt naar de exacte overeenkomst, maar als deze wordt gevonden, wordt het eerstvolgende grotere item/waarde geretourneerd
• 2 - Gedeeltelijk matchen met jokertekens (* of ~)

Maar het beste is dat u zich geen zorgen hoeft te maken of uw gegevens in oplopende of aflopende volgorde zijn gesorteerd. Zelfs als de gegevens niet zijn gesorteerd, zorgt XLOOKUP ervoor.

Hieronder heb ik een dataset waar ik de commissie van elke persoon wil vinden - en de commissie moet worden berekend met behulp van de tabel aan de rechterkant.

Hieronder is de formule die dit zal doen:

=XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,0,-1)*B2

Dit gebruikt gewoon de verkoopwaarde als de lookup en kijkt door de lookup-tabel aan de rechterkant. In deze formule heb ik -1 gebruikt als het vijfde argument ([match_mode]), wat betekent dat het naar een exacte overeenkomst zal zoeken, en als het er geen vindt, zal het de waarde retourneren die net kleiner is dan de opzoekwaarde .

En zoals ik al zei, u hoeft zich geen zorgen te maken of uw gegevens op niet zijn gesorteerd.

Klik hier om het voorbeeldbestand te downloaden en volg mee

Voorbeeld 8: Horizontaal opzoeken

XLOOKUP kan zowel verticaal als horizontaal opzoeken.

Hieronder heb ik een dataset waarin ik de namen van studenten en hun scores in rijen heb, en ik wil de score voor de naam in cel B7 ophalen.

De onderstaande formule doet dit:

=XZOEKEN(B7,B1:O1,B2:O2)

Dit is niets anders dan een simpele opzoeking (vergelijkbaar met wat we zagen in voorbeeld 1), maar horizontaal.

Alle voorbeelden die ik behandel over verticaal opzoeken, kunnen ook worden gedaan met een horizontale zoekopdracht met XLOOKUP (vaarwel tegen VERT.ZOEKEN en HORIZ.ZOEKEN).

Voorbeeld 9: Voorwaardelijk opzoeken (XLOOKUP gebruiken met andere formules)

Dit is een enigszins geavanceerd voorbeeld en toont ook de kracht van XLOOKUP wanneer u complexe zoekopdrachten moet uitvoeren.

Hieronder staat een dataset waarin ik de namen van studenten en hun scores heb, en ik wil de naam weten van de student die het maximum heeft gescoord in elk onderwerp en het aantal studenten dat meer dan 80 heeft gescoord in elk onderwerp.

Hieronder staat de formule die de naam geeft van de student met de hoogste cijfers voor elk onderwerp:

=XLOOKUP(MAX(XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15)),XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15),$A $2:$A$15)

Omdat XLOOKUP kan worden gebruikt om een ​​hele array te retourneren, heb ik het gebruikt om eerst alle cijfers voor het vereiste onderwerp te krijgen.

Als ik bijvoorbeeld voor wiskunde XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15) gebruik, krijg ik alle scores in wiskunde. Ik kan dan de MAX-functie gebruiken om de maximale score in dit bereik te vinden.

Deze maximale score wordt dan mijn opzoekwaarde en het opzoekbereik zou de array zijn die wordt geretourneerd door XLOOKUP(G1,$B$1:$D$1,$B$2:$D$15)

Ik gebruik dit binnen een andere XLOOKUP-formule om de naam op te halen van de student die het maximale aantal punten heeft behaald.

En om het aantal studenten te tellen dat meer dan 80 heeft gescoord, gebruikt u de onderstaande formule:

=AANTAL.ALS(XZOEKEN(G1,$B$1:$D$1,$B$2:$D$15),">80")

Deze gebruikt eenvoudig de XLOOKUP-formule om een ​​bereik van alle waarden voor het gegeven onderwerp te krijgen. Het verpakt het vervolgens in de AANTAL.ALS-functie om het aantal scores te krijgen dat meer dan 80 is.

Voorbeeld 10: Jokerteken gebruiken in XZOEKEN

Net zoals u jokertekens kunt gebruiken in VLOOKUP en MATCH, kunt u dit ook doen met XLOOKUP.

Maar er is een verschil.

In XLOOKUP moet u specificeren dat u jokertekens gebruikt (in het vijfde argument). Als u dit niet opgeeft, geeft XLOOKUP u een foutmelding.

Hieronder is een dataset waar ik bedrijfsnamen en hun marktkapitalisatie heb.

Ik wil een bedrijfsnaam opzoeken in kolom D en de marktkapitalisatie ophalen uit de tabel aan de linkerkant. En aangezien de namen in kolom D geen exacte overeenkomsten zijn, zal ik jokertekens moeten gebruiken.

Hieronder is de formule die dit zal doen:

=XLOOKUP("*"&D2&"*",$A$2:$A$11,$B$2:$B$11,,2)

In de bovenstaande formule heb ik een asterisk (*)-jokerteken gebruikt voor als na D2 (het moet tussen dubbele aanhalingstekens staan ​​​​en worden samengevoegd met D2 met behulp van ampersand).

Dit vertelt de formule om door alle cellen te kijken, en als het het woord in cel D2 (dat is Apple) bevat, beschouw het dan als een exacte overeenkomst. Het maakt niet uit hoeveel en welke tekens er voor en na de tekst in cel D2 staan.

En om ervoor te zorgen dat XLOOKUP jokertekens accepteert, is het vijfde argument ingesteld op 2 (wildcardtekenovereenkomst).

Voorbeeld 11: Zoek de laatste waarde in de kolom

Omdat u met XLOOKUP van onder naar boven kunt zoeken, kunt u eenvoudig de laatste waarde in een lijst vinden en de bijbehorende waarde uit een kolom halen.

Stel dat u een dataset heeft zoals hieronder weergegeven en u wilt weten wat het laatste bedrijf is en wat de marktkapitalisatie van dit laatste bedrijf is.

De onderstaande formule geeft u de naam van het laatste bedrijf:

=XLOOKUP("*",A2:A11,A2:A11,,2,-1)

En de onderstaande formule geeft de marktkapitalisatie van het laatste bedrijf in de lijst:

=XLOOKUP("*",A2:A11,B2:B11,,2,-1)

Deze formules gebruiken opnieuw jokertekens. Hierin heb ik een sterretje (*) gebruikt als de opzoekwaarde, wat betekent dat dit de eerste cel die het tegenkomt als een exacte overeenkomst zou beschouwen (omdat een sterretje elk teken en een willekeurig aantal tekens kan zijn).

En aangezien de richting van onder naar boven is (voor de verticaal gerangschikte gegevens), wordt de laatste waarde in de lijst geretourneerd.

En de tweede formule gebruikt sindsdien een afzonderlijk return_range om de marktkapitalisatie van de achternaam in de lijst te krijgen.

Klik hier om het voorbeeldbestand te downloaden en volg mee

Wat als u geen XLOOKUP heeft?

Aangezien XLOOKUP waarschijnlijk alleen beschikbaar zal zijn voor Office 365-gebruikers, is een manier om het te krijgen een upgrade naar Office 365.

Als je al Office 365 Home-, Personal- of University-editie hebt, heb je al toegang tot XLOOKUP. Het enige dat u hoeft te doen, is deelnemen aan het Office Insider-programma.

Ga hiervoor naar het tabblad Bestand, klik op Account en klik vervolgens op de optie Office insider. Er zou een optie zijn om deel te nemen aan het insider-programma.

Als je andere Office 365-abonnementen hebt (zoals Enterprise), weet ik zeker dat XLOOKUP en andere geweldige functies (zoals dynamische arrays, formules zoals SORTEREN en FILTER) binnenkort beschikbaar zullen komen.

Als u Excel 2010/2013/2016/2019 gebruikt, heeft u geen XLOOKUP en moet u de combinatie VERT.ZOEKEN, HORIZ.ZOEKEN en INDEX/VERGELIJKEN blijven gebruiken om het beste uit opzoekformules te halen.

XZOEKEN Achterwaartse compatibiliteit

Dit is iets waar je voorzichtig mee moet zijn - XLOOKUP is NIET achterwaarts compatibel.

Dit betekent dat als u een bestand maakt en de formule XLOOKUP gebruikt en het vervolgens opent in een versie die geen XLOOKUP heeft, er fouten worden weergegeven.

Aangezien XLOOKUP een enorme stap voorwaarts in de goede richting is, geloof ik dat dit de standaard opzoekformule zal worden, maar het zal zeker een paar jaar duren voordat het algemeen wordt aangenomen. Ik zie tenslotte nog steeds mensen die Excel 2003 gebruiken.

Dit zijn dus 11 XLOOKUP-voorbeelden die u kunnen helpen al het opzoeken en verwijzen sneller te doen en het ook gebruiksvriendelijker maken.

Ik hoop dat je deze tutorial nuttig vond!