Excels nye XLOOKUP vil erstatte VLOOKUP, hvilket giver en kraftfuld erstatning til en af Excels mest populære funktioner. Denne nye funktion løser nogle af VLOOKUPs begrænsninger og har ekstra funktionalitet. Her er hvad du behøver at vide.
Indholdsfortegnelse
Hvad er XLOOKUP?
Den nye XLOOKUP-funktion har løsninger til nogle af de største begrænsninger ved VLOOKUP. Derudover erstatter den også HLOOKUP. For eksempel kan XLOOKUP se til venstre, indstiller som standard til et nøjagtigt match og giver dig mulighed for at angive et celleområde i stedet for et kolonnenummer. VLOOKUP er ikke så let at bruge eller så alsidigt. Vi viser dig, hvordan det hele fungerer.
I øjeblikket er XLOOKUP kun tilgængelig for brugere på Insiders-programmet. Enhver kan deltage i Insiders-programmet for at få adgang til de nyeste Excel-funktioner, så snart de bliver tilgængelige. Microsoft vil snart begynde at udrulle det til alle Office 365-brugere.
Sådan bruges XLOOKUP-funktionen
Lad os dykke direkte ind med et eksempel på XLOOKUP i aktion. Tag eksempeldataene nedenfor. Vi ønsker at returnere afdelingen fra kolonne F for hvert ID i kolonne A.
Dette er et klassisk eksakt søgningseksempel. XLOOKUP-funktionen kræver kun tre stykker information.
Billedet nedenfor viser XLOOKUP med seks argumenter, men kun de tre første er nødvendige for et nøjagtigt match. Så lad os fokusere på dem:
Lookup_value: Hvad du leder efter.
Lookup_array: Hvor skal man kigge.
Return_array: det område, der indeholder den værdi, der skal returneres.
Følgende formel fungerer for dette eksempel: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)
Lad os nu udforske et par fordele, XLOOKUP har i forhold til VLOOKUP her.
Ikke mere kolonneindeksnummer
Det berygtede tredje argument for VLOOKUP var at angive kolonnenummeret for den information, der skulle returneres fra et tabelarray. Dette er ikke længere et problem, fordi XLOOKUP giver dig mulighed for at vælge det område, du vil vende tilbage fra (kolonne F i dette eksempel).
Og glem ikke, XLOOKUP kan se dataene til venstre for den valgte celle, i modsætning til VLOOKUP. Mere om dette nedenfor.
Du har heller ikke længere problemet med en brudt formel, når nye kolonner indsættes. Hvis det skete i dit regneark, ville returområdet justeres automatisk.
Præcis match er standard
Det var altid forvirrende, når du lærte VLOOKUP, hvorfor du var nødt til at angive et nøjagtigt match var ønsket.
Heldigvis har XLOOKUP som standard et nøjagtigt match – den langt mere almindelige grund til at bruge en opslagsformel). Dette reducerer behovet for at besvare det femte argument og sikrer færre fejl hos brugere, der er nye til formlen.
Så kort fortalt stiller XLOOKUP færre spørgsmål end VLOOKUP, er mere brugervenligt og også mere holdbart.
XLOOKUP kan se til venstre
At kunne vælge et opslagsområde gør XLOOKUP mere alsidigt end VLOOKUP. Med XLOOKUP er rækkefølgen af tabelkolonnerne ligegyldig.
VLOOKUP blev begrænset ved at søge i kolonnen længst til venstre i en tabel og derefter vende tilbage fra et specificeret antal kolonner til højre.
I eksemplet nedenfor skal vi slå et ID op (kolonne E) og returnere personens navn (kolonne D).
Følgende formel kan opnå dette: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)
Hvad skal man gøre, hvis den ikke findes
Brugere af opslagsfunktioner er meget fortrolige med #N/A-fejlmeddelelsen, der byder dem velkommen, når deres VLOOKUP eller deres MATCH-funktion ikke kan finde det, de skal bruge. Og ofte er der en logisk grund til dette.
Derfor undersøger brugerne hurtigt, hvordan man skjuler denne fejl, fordi den ikke er korrekt eller nyttig. Og selvfølgelig er der måder at gøre det på.
XLOOKUP kommer med sit eget indbyggede “hvis ikke fundet”-argument til at håndtere sådanne fejl. Lad os se det i aktion med det forrige eksempel, men med et forkert indtastet ID.
Følgende formel vil vise teksten “Forkert ID” i stedet for fejlmeddelelsen: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,”Forkert ID”)
Brug af XLOOKUP til et områdeopslag
Selvom det ikke er så almindeligt som det nøjagtige match, er en meget effektiv brug af en opslagsformel at lede efter en værdi i intervaller. Tag følgende eksempel. Vi ønsker at returnere rabatten afhængigt af det brugte beløb.
Denne gang leder vi ikke efter en bestemt værdi. Vi er nødt til at vide, hvor værdierne i kolonne B falder inden for intervallerne i kolonne E. Det vil bestemme den optjente rabat.
XLOOKUP har et valgfrit femte argument (husk, det er standard til det nøjagtige match) ved navn match-tilstand.
Du kan se, at XLOOKUP har større muligheder med omtrentlige match end VLOOKUP.
Der er mulighed for at finde det nærmeste match mindre end (-1) eller tættest større end (1) den værdi, der blev søgt efter. Der er også mulighed for at bruge jokertegn (2), såsom ? eller den *. Denne indstilling er ikke aktiveret som standard, som den var med VLOOKUP.
Formlen i dette eksempel returnerer den tætteste mindre end den værdi, der blev søgt efter, hvis der ikke findes et nøjagtigt match: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)
Der er dog en fejl i celle C7, hvor fejlen #N/A returneres (argumentet ‘hvis ikke fundet’ blev ikke brugt). Dette burde have returneret en rabat på 0 %, fordi forbrug 64 ikke når kriterierne for nogen rabat.
En anden fordel ved XLOOKUP-funktionen er, at den ikke kræver, at opslagsområdet er i stigende rækkefølge, som VLOOKUP gør.
Indtast en ny række nederst i opslagstabellen, og åbn derefter formlen. Udvid det brugte område ved at klikke og trække i hjørnerne.
Formlen retter fejlen med det samme. Det er ikke et problem at have “0” i bunden af intervallet.
Personligt ville jeg stadig sortere tabellen efter opslagskolonnen. At have “0” i bunden ville gøre mig sindssyg. Men det faktum, at formlen ikke gik i stykker, er genialt.
XLOOKUP erstatter også HLOOKUP-funktionen
Som nævnt er XLOOKUP-funktionen også her for at erstatte HLOOKUP. En funktion til at erstatte to. Fremragende!
HOPSLAG-funktionen er det vandrette opslag, der bruges til at søge langs rækker.
Ikke så kendt som sin søskende VLOOKUP, men nyttigt til eksempler som nedenfor, hvor overskrifterne er i kolonne A, og dataene er langs række 4 og 5.
XLOOKUP kan se i begge retninger – nedad i kolonner og også langs rækker. Vi har ikke længere brug for to forskellige funktioner.
I dette eksempel bruges formlen til at returnere salgsværdien relateret til navnet i celle A2. Den ser langs række 4 for at finde navnet og returnerer værdien fra række 5: =XLOOKUP(A2,B4:E4,B5:E5)
XLOOKUP kan se nedefra og op
Typisk skal du jagte en liste for at finde den første (ofte eneste) forekomst af en værdi. XLOOKUP har et sjette argument ved navn søgetilstand. Dette gør det muligt for os at skifte opslag til at starte nederst og slå en liste op for i stedet at finde den sidste forekomst af en værdi.
I eksemplet nedenfor vil vi gerne finde lagerniveauet for hvert produkt i kolonne A.
Opslagstabellen er i datorækkefølge, og der er flere lagertjek pr. produkt. Vi ønsker at returnere lagerniveauet fra sidste gang, det blev kontrolleret (sidste forekomst af produkt-id’et).
Det sjette argument i XLOOKUP-funktionen giver fire muligheder. Vi er interesserede i at bruge muligheden “Søg sidst til først”.
Den færdige formel vises her: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)
I denne formel blev det fjerde og femte argument ignoreret. Det er valgfrit, og vi ønskede standarden for et nøjagtigt match.
Runde op
XLOOKUP-funktionen er med spænding ventet efterfølger til både OPSLAG- og OPSLAG-funktionerne.
En række eksempler blev brugt i denne artikel for at demonstrere fordelene ved XLOOKUP. En af dem er, at XLOOKUP kan bruges på tværs af ark, projektmapper og også med tabeller. Eksemplerne blev holdt enkle i artiklen for at hjælpe vores forståelse.
På grund af dynamiske arrays bliver introduceret i Excel snart kan den også returnere en række værdier. Dette er bestemt noget, der er værd at undersøge nærmere.
Dagene for VLOOKUP er talte. XLOOKUP er her og vil snart være de facto-opslagsformlen.