Sådan bruges XLOOKUP-funktionen i Microsoft Excel

Excels nye XLOOKUP vil erstatte VLOOKUP, hvilket giver en stærk erstatning til en af ​​Excels mest populære funktioner. Denne nye funktion løser nogle af VLOOKUP's begrænsninger og har ekstra funktionalitet. Her er hvad du har brug for at vide.

Hvad er XLOOKUP?

Den nye XLOOKUP-funktion har løsninger til nogle af de største begrænsninger ved VLOOKUP. Plus, det erstatter også HLOOKUP. For eksempel kan XLOOKUP se til venstre, som standard være et nøjagtigt match og give dig mulighed for at specificere et celleområde i stedet for et kolonnetal. VLOOKUP er ikke så let at bruge eller så alsidig. Vi viser dig, hvordan det hele fungerer.

I øjeblikket er XLOOKUP kun tilgængelig for brugere på Insiders-programmet. Alle kan deltage i Insiders-programmet for at få adgang til de nyeste Excel-funktioner, så snart de bliver tilgængelige. Microsoft begynder snart at rulle det ud til alle Office 365-brugere.

Sådan bruges XLOOKUP-funktionen

Lad os dykke lige ind med et eksempel på XLOOKUP i aktion. Tag nedenstående data. Vi vil returnere afdelingen fra kolonne F for hvert id i kolonne A.

Dette er et klassisk eksempel på eksakt match-opslag. XLOOKUP-funktionen kræver kun tre stykker information.

Billedet nedenfor viser XLOOKUP med seks argumenter, men kun de første tre er nødvendige for en nøjagtig matchning. Så lad os fokusere på dem:

  • Lookup_value:  Hvad du leder efter.
  • Lookup_array:  Hvor skal man kigge.
  • Return_array:  området, der indeholder den værdi, der skal returneres.

Følgende formel fungerer i dette eksempel: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)

Lad os nu undersøge et par fordele, som XLOOKUP har over VLOOKUP her.

Intet mere kolonneindeksnummer

Det berygtede tredje argument for VLOOKUP var at specificere søjlenummeret på de oplysninger, der skal 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 data til venstre for den valgte celle i modsætning til VLOOKUP. Mere om dette nedenfor.

Du har heller ikke længere spørgsmålet om en brudt formel, når nye kolonner indsættes. Hvis det skete i dit regneark, justeres returområdet automatisk.

Præcis match er standard

Det var altid forvirrende, når man lærte VLOOKUP, hvorfor man skulle angive et nøjagtigt match var ønsket.

Heldigvis er 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 fra brugere, der er nye i formlen.

Så kort sagt stiller XLOOKUP færre spørgsmål end VLOOKUP, er mere brugervenlig og er også mere holdbar.

XLOOKUP kan se til venstre

At kunne vælge et opslagsområde gør XLOOKUP mere alsidig end VLOOKUP. Med XLOOKUP betyder rækkefølgen af ​​tabelkolonnerne ikke noget.

VLOOKUP blev begrænset ved at søge i den venstre kolonne i en tabel og derefter vende tilbage fra et angivet antal kolonner til højre.

I eksemplet nedenfor skal vi slå et ID (kolonne E) op og returnere personens navn (kolonne D).

Følgende formel kan opnå dette: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)

Hvad skal jeg gøre, hvis det ikke findes

Brugere af opslagsfunktioner er meget fortrolige med fejlmeddelelsen # N / A, der byder dem velkommen, når deres VLOOKUP eller deres MATCH-funktion ikke kan finde det, den har brug for. Og ofte er der en logisk grund til dette.

Derfor undersøger brugerne hurtigt, hvordan de skjuler denne fejl, fordi den ikke er korrekt eller nyttig. Og selvfølgelig er der måder at gøre det på.

XLOOKUP leveres med sit eget indbyggede "hvis ikke fundet" argument til at håndtere sådanne fejl. Lad os se det i aktion med det foregående eksempel, men med et forkert indtastet ID.

Den følgende formel viser teksten "Forkert ID" i stedet for fejlmeddelelsen: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Incorrect ID")

Brug af XLOOKUP til en rækkeopslag

Selvom det ikke er så almindeligt som det nøjagtige match, er en meget effektiv anvendelse af en opslagsformel at lede efter en værdi i intervaller. Tag følgende eksempel. Vi ønsker at returnere rabatten afhængigt af det anvendte beløb.

Denne gang leder vi ikke efter en bestemt værdi. Vi har brug for at vide, hvor værdierne i kolonne B falder inden for intervallerne i kolonne E. Det bestemmer den optjente rabat.

XLOOKUP har et valgfrit femte argument (husk, det er standard det nøjagtige match) navngivet matchtilstand.

Du kan se, at XLOOKUP har større muligheder med omtrentlige matches end VLOOKUP.

Der er mulighed for at finde det tætteste match, der er mindre end (-1) eller nærmest større end (1), den værdi, du kiggede efter. Der er også en mulighed for at bruge jokertegn (2) som? eller den *. Denne indstilling er ikke aktiveret som standard, som den var med VLOOKUP.

Formlen i dette eksempel returnerer det nærmeste mindre end den ledte værdi, 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 # N / A-fejlen returneres (argumentet 'hvis ikke fundet' blev ikke brugt). Dette burde have returneret 0% rabat, fordi udgifter 64 ikke når kriterierne for nogen rabat.

En anden fordel ved XLOOKUP-funktionen er, at den ikke kræver, at opslagsområdet skal være i stigende rækkefølge, som VLOOKUP gør.

Indtast en ny række i bunden af ​​opslagstabellen, og åbn derefter formlen. Udvid det anvendte interval ved at klikke og trække i hjørnerne.

Formlen retter straks fejlen. Det er ikke et problem med at have "0" nederst i området.

Personligt ville jeg stadig sortere tabellen efter opslagskolonnen. At have “0” i bunden ville gøre mig skør. Men det faktum, at formlen ikke brød, er strålende.

XLOOKUP Erstatter også HLOOKUP-funktionen

Som nævnt er XLOOKUP-funktionen også her for at erstatte HLOOKUP. Én funktion til erstatning af to. Fremragende!

HLOOKUP-funktionen er den vandrette opslag, der bruges til at søge langs rækker.

Ikke så kendt som dets 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 - ned 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 vedrørende navnet i celle A2. Det 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 jage en liste for at finde den første (ofte kun) forekomst af en værdi. XLOOKUP har et sjette argument med navnet søgetilstand. Dette giver os mulighed for at skifte opslag for at starte i bunden og slå op på en liste for at finde den sidste forekomst af en værdi i stedet.

I eksemplet nedenfor vil vi gerne finde lagerniveauet for hvert produkt i kolonne A.

Opslagstabellen er i dato rækkefølge, og der er flere lagerkontrol pr. Produkt. Vi ønsker at returnere lagerniveauet fra sidste gang det blev kontrolleret (sidste forekomst af produkt-id).

Det sjette argument i XLOOKUP-funktionen giver fire muligheder. Vi er interesserede i at bruge muligheden "Søg sidste til første".

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 standard for et nøjagtigt match.

Runde op

XLOOKUP-funktionen er den længe ventede efterfølger til både VLOOKUP- og HLOOKUP-funktionerne.

En række eksempler blev brugt i denne artikel for at demonstrere fordelene ved XLOOKUP. Den ene 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 os med at forstå.

På grund af at dynamiske arrays snart introduceres i Excel, kan det også returnere en række værdier. Dette er bestemt noget, der er værd at udforske yderligere.

Dage til VLOOKUP er nummereret. XLOOKUP er her og vil snart være de facto-opslagsformlen.