XLOOKUP -funktionen er eksklusiv til insiderprogrammet til office 365. LOOKUP -funktionen har mange funktioner, der overvinder mange af svaghederne ved VLOOKUP- og HLOOKUP -funktionen, men den er desværre ikke tilgængelig for os i øjeblikket. Men bare rolig, vi kan oprette en XLOOKUP -funktion, der fungerer nøjagtig det samme som den kommende XLOOKUP -funktion MS Excel. Vi tilføjer funktionaliteter til det en efter en.
VBA -kode for XLOOKUP -funktion
Nedenstående UDF -opslagsfunktion vil løse mange problemer. Kopiér det, eller download xl-tilføjelsesprogrammet nedenfor.
Funktion XLOOKUP (lk som variant, lCol som område, rCol som område) XLOOKUP = WorksheetFunction.Index (rCol, WorksheetFunction.Match (lk, lCol, 0)) Afslut funktion
Forklaring:
Ovenstående kode er bare grundlæggende INDEX-MATCH brugt i VBA. Dette forenkler mange af de ting, som en ny bruger står over for. Hvis løser kompleksiteten af INDEX-MATCH-funktionen og kun bruger tre argumenter. Du kan kopiere den i din excel-fil eller downloade .xlam-filen herunder og installere den som en tilføjelses-excel. Hvis du ikke ved, hvordan du opretter og bruger et tilføjelsesprogram, skal du klikke her, det hjælper dig.
XLOOKUP-tilføjelsesprogram
lad os se, hvordan det fungerer på Excel -regneark.
Syntaks for XLOOKUP
| = XLOOKUP (opslag_værdi, opslag_array, resultat_array) |
opslagsværdi: Dette er den værdi, du vil søge i opslag_array.
opslag_array: Det er et endimensionelt område, hvor du vil søge i opslagsværdi.
result_array: Det er også et endimensionelt område. Dette er det område, hvorfra du vil hente værdien.
Lad os se denne XLOOKUP -funktion i aktion.
XLOOKUP Eksempler:
Her har jeg en datatabel i excel. Lad os undersøge nogle funktioner ved hjælp af denne datatabel.
Funktionalitet 1. Eksakt Opslag i venstre og højre side af opslagsværdien.
Som vi ved, kan Excel VLOOKUP -funktionen ikke hente værdier fra venstre for opslagsværdien. Til det skal du bruge den komplekse INDEX-MATCH-kombination. Men ikke længere.
Forudsat at vi skal hente alle de oplysninger, der er tilgængelige i tabellen over nogle rullenumre. I så fald bliver du også nødt til at hente regionen, som er til venstre for rullenummerkolonnen.
Skriv denne formel, I2:
| = XLOOKUP (H2, $ B $ 2: $ B $ 14, $ A $ 2: $ A $ 14) |
Vi får resultatet Nord for rulle nummer 112. Kopier eller træk formlen ned i cellerne nedenfor for at fylde dem op med de respektive regioner.
Hvordan virker det?
Mekanismen er enkel. Denne funktion slår op på opslagsværdi i opslag_array og returnerer indekset for et første eksakt match. Brug derefter dette indeks til at hente værdien fra resultat_array. Denne funktion fungerer perfekt med navngivne områder.
På samme måde kan du bruge denne formel til at hente værdien fra hver kolonne.
Funktionalitet 2. Nøjagtig Vandret Slå op over og under opslagsværdien.
XLOOKUP fungerer også som en nøjagtig HLOOKUP -funktion. HLOOKUP -funktionen har den samme begrænsning som VLOOKUP har. Det kan ikke hente værdi ovenover opslagsværdien. Men XLOOKUP fungerer ikke kun som HLOOKUP, det overvinder også denne svaghed. Lad os se hvordan.
Hypotetisk, hvis du vil sammenligne to poster. Opslagsposten du allerede har. Rekorden, du vil sammenligne med, er over opslag_området. Brug i så fald denne formel.
| = XLOOKUP (H7, $ A $ 9: $ E $ 9, $ A $ 2: $ E $ 2) |
træk formlen ned, og du har hele rekorden for at sammenligne række.
Funktionalitet 3. Intet behov for kolonnummer og standard eksakt match.
Når du bruger VLOOKUP -funktionen, skal du fortælle det kolonnenummer, som du vil hente værdierne fra. Til det skal du tælle kolonnerne eller bruge nogle tricks, tage hjælp af andre funktioner. Med denne UDF XLOOKUP behøver du ikke at gøre det.
Hvis du bruger VLOOKUP til bare at hente en værdi fra en kolonne eller for at kontrollere, om der findes en værdi i kolonnen, er dette den bedste løsning pr. Mig.
Funktionalitet 4. Erstatter INDEX-MATCH, VLOOKUP, HLOOKUP funktion
Ved enkle opgaver erstatter vores XLOOKUP-funktion de ovennævnte funktioner.
Begrænsninger for XLOOKUP:
Når det kommer til komplekse formler, som VLOOKUP med Dynamic Col Index, hvor vi VLOOKUP identificerer opslagskolonnen med overskrifter, mislykkes denne XLOOKUP.
En anden begrænsning er, at hvis du skal slå flere tilfældige kolonner eller rækker op fra tabellen, vil denne funktion være ubrugelig, da du skal skrive denne formel igen og igen. Dette kan overvindes ved at bruge navngivne intervaller.
For nu har vi ikke tilføjet den omtrentlige funktionalitet, så du kan selvfølgelig ikke få den omtrentlige match. Det tilføjer vi for tidligt.
Hvis XLOOKUP -funktionen ikke kan finde opslagsværdien, returnerer den #VÆRDI -fejl ikke #N/A.
Så ja fyre, sådan bruger du XLOOKUP til at hente, søge og validere værdier i excel -tabeller. Du kan bruge denne brugerdefinerede funktion til et problemfrit opslag til venstre eller op for opslagsværdien. Hvis du stadig er i tvivl eller et specifikt krav i forbindelse med denne funktion eller EXCEL 2010/2013/2016/2019/365 eller VBA -relateret forespørgsel, så spørg det i kommentarfeltet herunder. Du får helt sikkert et svar.
Opret VBA -funktion for at returnere matrix | For at returnere et array fra brugerdefineret funktion skal vi erklære det, når vi navngiver UDF.
Arrays i Excel Formul | Lær, hvad arrays er i excel.
Sådan oprettes brugerdefineret funktion via VBA | Lær, hvordan du opretter brugerdefinerede funktioner i Excel
Brug af en brugerdefineret funktion (UDF) fra en anden projektmappe ved hjælp af VBA i Microsoft Excel | Brug den brugerdefinerede funktion i en anden Excel-projektmappe
Returner fejlværdier fra brugerdefinerede funktioner ved hjælp af VBA i Microsoft Excel | Lær, hvordan du kan returnere fejlværdier fra en brugerdefineret funktion
Populære artikler:
Opdel Excel -ark i flere filer baseret på kolonne ved hjælp af VBA | Denne VBA -kode deler excelarkbase på unikke værdier i en specificeret kolonne. Download arbejdsfilen.
Slå advarselsmeddelelser fra ved hjælp af VBA i Microsoft Excel 2016 | For at deaktivere advarselsmeddelelser, der afbryder den kørende VBA -kode, bruger vi applikationsklassen.
Tilføj og gem ny arbejdsmappe ved hjælp af VBA i Microsoft Excel 2016 | For at tilføje og gemme projektmapper ved hjælp af VBA bruger vi Workbooks -klassen. Workbooks.Add tilføjer dog let en ny projektmappe …