De XLOOKUP-functie is exclusief voor het insider-programma van office 365. De LOOKUP-functie heeft veel functionaliteiten die veel van de zwakke punten van de VLOOKUP- en HLOOKUP-functie overwinnen, maar helaas is deze momenteel niet voor ons beschikbaar. Maar maak je geen zorgen, we kunnen een XLOOKUP-functie maken die precies hetzelfde werkt als de aankomende XLOOKUP-functie MS Excel. We zullen er één voor één functionaliteiten aan toevoegen.
VBA-code van de XZOEKEN-functie
De onderstaande UDF-opzoekfunctie zal veel problemen oplossen. Kopieer het of download de onderstaande xl-add-in.
Functie XLOOKUP(lk As Variant, lCol As Range, rCol As Range) XLOOKUP = WorksheetFunction.Index(rCol, WorksheetFunction.Match(lk, lCol, 0)) End Function
Uitleg:
De bovenstaande code is slechts een eenvoudige INDEX-MATCH die wordt gebruikt in VBA. Dit vereenvoudigt veel van de dingen waarmee een nieuwe gebruiker wordt geconfronteerd. If lost de complexiteit van de functie INDEX-MATCH op en gebruikt slechts drie argumenten. U kunt het naar uw Excel-bestand kopiëren of het .xlam-bestand hieronder downloaden en installeren als een Excel-invoegtoepassing. Als u niet weet hoe u een invoegtoepassing moet maken en gebruiken, klik dan hier, dit zal u helpen.
XLOOKUP-invoegtoepassing
laten we eens kijken hoe het werkt op een Excel-werkblad.
Syntaxis van XZOEKEN
| =XLOOKUP(lookup_value, lookup_array, result_array) |
opzoekwaarde: Dit is de waarde die u wilt zoeken in de lookup_array.
lookup_array: Het is een eendimensionaal bereik waarin u wilt zoeken in de opzoekwaarde.
resultaat_array: Het is ook een eendimensionaal bereik. Dit is het bereik waaruit u de waarde wilt ophalen.
Laten we eens kijken naar deze XLOOKUP-functie in actie.
XZOEKEN Voorbeelden:
Hier heb ik een gegevenstabel in Excel. Laten we wat functionaliteit onderzoeken met behulp van deze gegevenstabel.
Functionaliteit 1. Exact Opzoeken aan de linker- en rechterkant van de opzoekwaarde.
Zoals we weten, kan de Excel VERT.ZOEKEN-functie geen waarden ophalen aan de linkerkant van de opzoekwaarde. Daarvoor moet je de complexe combinatie INDEX-MATCH gebruiken. Maar nu niet meer.
Ervan uitgaande dat we alle beschikbare informatie in de tabel van sommige rolnummers moeten ophalen. In dat geval moet u ook de regio ophalen, die zich aan de linkerkant van de kolom met het rolnummer bevindt.
Schrijf deze formule, I2:
| =XZOEKEN(H2,$B$2:$B$14,$A$2:$A$14) |
We krijgen het resultaat Noord voor rolnummer 112. Kopieer of sleep de formule in de onderstaande cellen om ze te vullen met de respectieve regio's.
Hoe werkt het?
Het mechanisme is eenvoudig. Deze functie zoekt de opzoekwaarde in lookup_array en retourneert de index van een eerste exacte overeenkomst. Gebruikt vervolgens die index om de waarde op te halen van resultaat_array. Deze functie werkt perfect met benoemde bereiken.
Gebruik op dezelfde manier deze formule om de waarde uit elke kolom op te halen.
Functionaliteit 2. Exact Horizontaal Opzoeken boven en onder de opzoekwaarde.
De XLOOKUP werkt ook als een exacte HORIZ.ZOEKEN-functie. De functie HORIZ.ZOEKEN heeft dezelfde beperking als de VERT.ZOEKEN. Het kan geen waarde ophalen van boven de opzoekwaarde. Maar XLOOKUP werkt niet alleen als HLOOKUP, het overwint ook die zwakte. Laten we eens kijken hoe.
Hypothetisch, als u twee records wilt vergelijken. Het opzoekrecord dat u al heeft. Het record waarmee u wilt vergelijken, bevindt zich boven het opzoekbereik. Gebruik dan deze formule.
| =XLZOEKEN(H7,$A$9:$E$9,$A$2:$E$2) |
sleep de formule naar beneden en je hebt het hele record van het vergelijken van rijen.
Functionaliteit 3. Geen behoefte aan kolomnummer en standaard exacte match.
Wanneer u de functie VERT.ZOEKEN gebruikt, moet u het kolomnummer opgeven waaruit u de waarden wilt ophalen. Daarvoor moet je de kolommen tellen of een paar trucjes gebruiken, de hulp van andere functies gebruiken. Met deze UDF XLOOKUP hoeft u dat niet te doen.
Als u VERT.ZOEKEN gebruikt om alleen een waarde uit één kolom op te halen of om te controleren of er een waarde in de kolom bestaat, is dit volgens mij de beste oplossing.
Functionaliteit 4. Vervangt de functie INDEX-MATCH, VLOOKUP, HLOOKUP
Voor eenvoudige taken vervangt onze XLOOKUP-functie de bovengenoemde functies.
Beperkingen van XZOEKEN:
Als het gaat om complexe formules, zoals VERT.ZOEKEN met Dynamic Col Index, waarbij we VERT.ZOEKEN de opzoekkolom met kopteksten identificeert, zal dit XLZOEKEN mislukken.
Een andere beperking is dat als je meerdere willekeurige kolommen of rijen uit de tabel moet opzoeken, deze functie nutteloos is omdat je deze formule steeds opnieuw moet schrijven. Dit kan worden ondervangen door benoemde bereiken te gebruiken.
Voor nu hebben we de geschatte functionaliteit niet toegevoegd, dus u kunt natuurlijk niet de geschatte overeenkomst krijgen. Dat voegen we te snel toe.
Als de XZOEKEN-functie de opzoekwaarde niet kan vinden, wordt de fout #WAARDE geretourneerd en niet #N/A.
Dus ja jongens, dit is hoe je XLOOKUP gebruikt om waarden in Excel-tabellen op te halen, te zoeken en te valideren. U kunt deze door de gebruiker gedefinieerde functie gebruiken voor een probleemloze opzoeking aan de linker- of bovenkant van de opzoekwaarde. Als u nog steeds enige twijfel of een specifieke vereiste heeft met betrekking tot deze functie of EXCEL 2010/2013/2016/2019/365 of VBA-gerelateerde vraag, vraag het dan in de opmerkingen hieronder. Je krijgt zeker antwoord.
Maak een VBA-functie om array terug te geven | Om een array van een door de gebruiker gedefinieerde functie te retourneren, moeten we deze declareren wanneer we de UDF een naam geven.
Arrays in Excel Formul|Leer wat arrays zijn in Excel.
Hoe een door de gebruiker gedefinieerde functie te creëren via VBA | Leer hoe u door de gebruiker gedefinieerde functies in Excel kunt maken
Een door de gebruiker gedefinieerde functie (UDF) uit een andere werkmap gebruiken met VBA in Microsoft Excel | Gebruik de door de gebruiker gedefinieerde functie in een andere werkmap van Excel
Retourneer foutwaarden van door de gebruiker gedefinieerde functies met VBA in Microsoft Excel | Leer hoe u foutwaarden kunt retourneren vanuit een door de gebruiker gedefinieerde functie
Populaire artikels:
Excel-blad splitsen in meerdere bestanden op basis van kolom met behulp van VBA | Deze VBA-code splitst Excel-blad op basis van unieke waarden in een opgegeven kolom. Download het werkbestand.
Schakel waarschuwingsberichten uit met VBA in Microsoft Excel 2016 | Om waarschuwingsberichten uit te schakelen die de actieve VBA-code onderbreken, gebruiken we de klasse Application.
Nieuwe werkmap toevoegen en opslaan met VBA in Microsoft Excel 2016 | Om werkmappen toe te voegen en op te slaan met VBA gebruiken we Workbooks class. Workbooks.Add voegt eenvoudig een nieuwe werkmap toe, maar…