XLOOKUP -funksjonen er eksklusiv for insiderprogrammet til office 365. LOOKUP -funksjonen har mange funksjoner som overvinner mange av svakhetene ved VLOOKUP- og HLOOKUP -funksjonen, men dessverre er den ikke tilgjengelig for oss foreløpig. Men ikke bekymre deg, vi kan lage en XLOOKUP -funksjon som fungerer nøyaktig det samme som den kommende XLOOKUP -funksjonen MS Excel. Vi legger til funksjonaliteter en etter en.
VBA -kode for XLOOKUP -funksjonen
UDF -oppslagsfunksjonen nedenfor vil løse mange problemer. Kopier den eller last ned xl-tillegget under filen nedenfor.
Funksjon XLOOKUP (lk Som variant, lCol som område, rCol som område) XLOOKUP = WorksheetFunction.Index (rCol, WorksheetFunction.Match (lk, lCol, 0)) Avslutt funksjon
Forklaring:
Koden ovenfor er bare grunnleggende INDEX-MATCH som brukes i VBA. Dette forenkler mange ting en ny bruker står overfor. Hvis løser kompleksiteten til INDEX-MATCH-funksjonen og bruker bare tre argumenter. Du kan kopiere den i excel-filen din, eller laste ned .xlam-filen nedenfor og installere den som en Excel-tillegg. Hvis du ikke vet hvordan du oppretter og bruker et tillegg, klikk her, det vil hjelpe deg.
XLOOKUP-tillegg
la oss se hvordan det fungerer på Excel -regneark.
Syntaks for XLOOKUP
| = XLOOKUP (oppslag_verdi, oppslag_rute, resultat_rute) |
oppslagsverdi: Dette er verdien du vil søke i oppslag_array.
oppslag_array: Det er et endimensjonalt område der du vil søke i oppslag_verdi.
result_array: Det er også et endimensjonalt område. Dette er området du vil hente verdien fra.
La oss se denne XLOOKUP -funksjonen i aksjon.
XLOOKUP Eksempler:
Her har jeg en datatabell i excel. La oss utforske noen funksjoner ved hjelp av denne datatabellen.
Funksjonalitet 1. Nøyaktig Slå opp på venstre og høyre side av oppslagsverdien.
Som vi vet at Excel VLOOKUP -funksjonen ikke kan hente verdier fra venstre for oppslagsverdien. For det må du bruke den komplekse INDEX-MATCH-kombinasjonen. Men ikke nå lenger.
Forutsatt at vi må hente all informasjonen som er tilgjengelig i tabellen med noen rulletall. I så fall må du også hente regionen, som er til venstre for kolonnen med nummer nummer.
Skriv denne formelen, I2:
| = XLOOKUP (H2, $ B $ 2: $ B $ 14, $ A $ 2: $ A $ 14) |
Vi får resultatet Nord for rulle nummer 112. Kopier eller dra formelen i cellene nedenfor for å fylle dem opp med de respektive regionene.
Hvordan virker det?
Mekanismen er enkel. Denne funksjonen slår opp oppslag_verdi i oppslag_array og returnerer indeksen for en første eksakt samsvar. Deretter bruker du indeksen til å hente verdien fra result_array. Denne funksjonen fungerer perfekt med navngitte områder.
På samme måte bruker du denne formelen for å hente verdien fra hver kolonne.
Funksjonalitet 2. Eksakt Horisontal Slå opp over og under oppslagsverdien.
XLOOKUP fungerer også som en eksakt HLOOKUP -funksjon. HLOOKUP -funksjonen har samme begrensning som VLOOKUP har. Det kan ikke hente verdi over oppslagsverdien. Men XLOOKUP fungerer ikke bare som HLOOKUP, det overvinner også den svakheten. La oss se hvordan.
Hypotetisk, hvis du vil sammenligne to poster. Oppslagsrekorden du allerede har. Rekorden du vil sammenligne med er over oppslagsområdet. Bruk i så fall denne formelen.
| = XLOOKUP (H7, $ A $ 9: $ E $ 9, $ A $ 2: $ E $ 2) |
dra ned formelen, og du har hele oversikten over sammenligning av rader.
Funksjonalitet 3. Du trenger ikke kolonnummer og standard eksakt samsvar.
Når du bruker VLOOKUP -funksjonen, må du fortelle kolonnenummeret du vil hente verdiene fra. For det må du telle kolonnene eller bruke noen triks, ta hjelp av andre funksjoner. Med denne UDF XLOOKUP trenger du ikke gjøre det.
Hvis du bruker VLOOKUP for bare å hente noen verdi fra en kolonne eller for å sjekke om det finnes en verdi i kolonnen, er dette den beste løsningen i henhold til meg.
Funksjonalitet 4. Erstatter INDEX-MATCH, VLOOKUP, HLOOKUP funksjon
For enkle oppgaver erstatter vår XLOOKUP-funksjon de ovennevnte funksjonene.
Begrensninger for XLOOKUP:
Når det gjelder komplekse formler, som VLOOKUP med Dynamic Col Index der vi VLOOKUP identifiserer oppslagskolonnen med overskrifter, vil denne XLOOKUP mislykkes.
En annen begrensning er at hvis du må slå opp flere tilfeldige kolonner eller rader fra tabellen, vil denne funksjonen være ubrukelig ettersom du må skrive denne formelen igjen og igjen. Dette kan overvinnes ved å bruke navngitte områder.
Foreløpig har vi ikke lagt til den omtrentlige funksjonaliteten, så selvfølgelig kan du ikke få den omtrentlige kampen. Det vil vi legge til for tidlig.
Hvis XLOOKUP -funksjonen ikke finner oppslagsverdien, returnerer den #VALUE -feilen ikke #N/A.
Så ja gutta, slik bruker du XLOOKUP til å hente, søke og validere verdier i excel -tabeller. Du kan bruke denne brukerdefinerte funksjonen for et problemfritt oppslag til venstre eller opp av oppslagsverdien. Hvis du fortsatt har tvil eller noen spesifikke krav knyttet til denne funksjonen eller EXCEL 2010/2013/2016/2019/365 eller VBA -relatert spørring, kan du stille det i kommentarfeltet nedenfor. Du vil sikkert få svar.
Lag VBA -funksjon for å returnere matrise | For å returnere en matrise fra brukerdefinert funksjon, må vi deklarere den når vi navngir UDF.
Matriser i Excel Formul | Finn ut hvilke matriser som er i excel.
Hvordan lage brukerdefinert funksjon gjennom VBA | Lær hvordan du oppretter brukerdefinerte funksjoner i Excel
Bruke en brukerdefinert funksjon (UDF) fra en annen arbeidsbok ved hjelp av VBA i Microsoft Excel | Bruk den brukerdefinerte funksjonen i en annen arbeidsbok i Excel
Returner feilverdier fra brukerdefinerte funksjoner ved hjelp av VBA i Microsoft Excel | Lær hvordan du kan returnere feilverdier fra en brukerdefinert funksjon
Populære artikler:
Del Excel -ark i flere filer basert på kolonne ved hjelp av VBA | Denne VBA -koden deler excel -arkbase på unike verdier i en spesifisert kolonne. Last ned arbeidsfilen.
Slå av advarselsmeldinger ved hjelp av VBA i Microsoft Excel 2016 | For å slå av advarsler som avbryter den kjørende VBA -koden, bruker vi applikasjonsklassen.
Legg til og lagre ny arbeidsbok ved hjelp av VBA i Microsoft Excel 2016 | For å legge til og lagre arbeidsbøker ved hjelp av VBA bruker vi Workbooks -klassen. Workbooks.Add legger til ny arbeidsbok enkelt, men …