Excels nye XLOOKUP vil erstatte VLOOKUP, og gir en kraftig erstatning til en av Excels mest populære funksjoner. Denne nye funksjonen løser noen av VLOOKUPs begrensninger og har ekstra funksjonalitet. Her er det du trenger å vite.
Innholdsfortegnelse
Hva er XLOOKUP?
Den nye XLOOKUP-funksjonen har løsninger for noen av de største begrensningene til VLOOKUP. I tillegg erstatter den også HLOOKUP. For eksempel kan XLOOKUP se til venstre, har som standard nøyaktig samsvar, og lar deg spesifisere et celleområde i stedet for et kolonnenummer. VLOOKUP er ikke så lett å bruke eller like allsidig. Vi viser deg hvordan det hele fungerer.
For øyeblikket er XLOOKUP kun tilgjengelig for brukere på Insiders-programmet. Hvem som helst kan bli med i Insiders-programmet for å få tilgang til de nyeste Excel-funksjonene så snart de blir tilgjengelige. Microsoft vil snart begynne å rulle den ut til alle Office 365-brukere.
Slik bruker du XLOOKUP-funksjonen
La oss dykke rett inn med et eksempel på XLOOKUP i aksjon. Ta eksempeldataene nedenfor. Vi ønsker å returnere avdelingen fra kolonne F for hver ID i kolonne A.
Dette er et klassisk eksakt oppslagseksempel. XLOOKUP-funksjonen krever bare tre opplysninger.
Bildet nedenfor viser XLOOKUP med seks argumenter, men bare de tre første er nødvendige for en eksakt match. Så la oss fokusere på dem:
Lookup_value: Det du leter etter.
Lookup_array: Hvor du skal lete.
Return_array: området som inneholder verdien som skal returneres.
Følgende formel vil fungere for dette eksemplet: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)
La oss nå utforske et par fordeler XLOOKUP har fremfor VLOOKUP her.
Ikke mer kolonneindeksnummer
Det beryktede tredje argumentet til VLOOKUP var å spesifisere kolonnenummeret til informasjonen som skulle returneres fra en tabellmatrise. Dette er ikke lenger et problem fordi XLOOKUP lar deg velge området du vil returnere fra (kolonne F i dette eksemplet).
Og ikke glem, XLOOKUP kan se dataene til venstre for den valgte cellen, i motsetning til VLOOKUP. Mer om dette nedenfor.
Du har heller ikke lenger problemet med en brutt formel når nye kolonner settes inn. Hvis det skjedde i regnearket ditt, vil returområdet justeres automatisk.
Eksakt samsvar er standard
Det var alltid forvirrende når du lærte VLOOKUP hvorfor du måtte spesifisere en eksakt match var ønsket.
Heldigvis har XLOOKUP som standard et eksakt samsvar – den langt mer vanlige grunnen til å bruke en oppslagsformel). Dette reduserer behovet for å svare på det femte argumentet og sikrer færre feil for brukere som er nye i formelen.
Så kort sagt, XLOOKUP stiller færre spørsmål enn VLOOKUP, er mer brukervennlig og er også mer holdbar.
XLOOKUP kan se til venstre
Å kunne velge et oppslagsområde gjør XLOOKUP mer allsidig enn VLOOKUP. Med XLOOKUP spiller ikke rekkefølgen på tabellkolonnene noen rolle.
VLOOKUP ble begrenset ved å søke i kolonnen lengst til venstre i en tabell og deretter returnere fra et spesifisert antall kolonner til høyre.
I eksemplet nedenfor må vi slå opp en ID (kolonne E) og returnere personens navn (kolonne D).
Følgende formel kan oppnå dette: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)
Hva du skal gjøre hvis du ikke finner den
Brukere av oppslagsfunksjoner er godt kjent med #N/A feilmeldingen som møter dem når deres VLOOKUP eller deres MATCH-funksjon ikke finner det den trenger. Og ofte er det en logisk grunn til dette.
Derfor undersøker brukere raskt hvordan de kan skjule denne feilen fordi den ikke er riktig eller nyttig. Og det finnes selvfølgelig måter å gjøre det på.
XLOOKUP kommer med sitt eget innebygde «hvis ikke funnet»-argument for å håndtere slike feil. La oss se det i aksjon med forrige eksempel, men med en feilskrevet ID.
Følgende formel vil vise teksten «Feil ID» i stedet for feilmeldingen: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,»Feil ID»)
Bruke XLOOKUP for et områdeoppslag
Selv om det ikke er så vanlig som det eksakte samsvaret, er en veldig effektiv bruk av en oppslagsformel å se etter en verdi i områder. Ta følgende eksempel. Vi ønsker å returnere rabatten avhengig av beløpet som er brukt.
Denne gangen er vi ikke ute etter en bestemt verdi. Vi må vite hvor verdiene i kolonne B faller innenfor områdene i kolonne E. Det vil avgjøre rabatten som oppnås.
XLOOKUP har et valgfritt femte argument (husk, det er standard til eksakt samsvar) kalt matchmodus.
Du kan se at XLOOKUP har større muligheter med omtrentlige treff enn VLOOKUP.
Det er muligheten til å finne den nærmeste matchen mindre enn (-1) eller nærmest større enn (1) verdien du så etter. Det er også en mulighet for å bruke jokertegn (2) som ? eller *. Denne innstillingen er ikke på som standard slik den var med VLOOKUP.
Formelen i dette eksemplet returnerer den nærmeste mindre enn verdien du ser etter hvis et eksakt samsvar ikke blir funnet: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)
Imidlertid er det en feil i celle C7 der #N/A-feilen returneres (argumentet «hvis ikke funnet» ble ikke brukt). Dette burde ha gitt en rabatt på 0 % fordi utgifter 64 ikke når kriteriene for noen rabatt.
En annen fordel med XLOOKUP-funksjonen er at den ikke krever at oppslagsområdet er i stigende rekkefølge slik VLOOKUP gjør.
Skriv inn en ny rad nederst i oppslagstabellen og åpne deretter formelen. Utvid det brukte området ved å klikke og dra hjørnene.
Formelen retter feilen umiddelbart. Det er ikke et problem å ha «0» nederst i området.
Personlig ville jeg fortsatt sortert tabellen etter oppslagskolonnen. Å ha «0» nederst ville gjøre meg gal. Men det faktum at formelen ikke gikk i stykker er strålende.
XLOOKUP Erstatter også HLOOKUP-funksjonen
Som nevnt er XLOOKUP-funksjonen også her for å erstatte HLOOKUP. En funksjon for å erstatte to. Utmerket!
HLOOKUP-funksjonen er det horisontale oppslaget, som brukes til å søke langs rader.
Ikke like kjent som sin søsken VLOOKUP, men nyttig for eksempler som nedenfor der overskriftene er i kolonne A, og dataene er langs rad 4 og 5.
XLOOKUP kan se i begge retninger – nedover kolonner og også langs rader. Vi trenger ikke lenger to forskjellige funksjoner.
I dette eksemplet brukes formelen til å returnere salgsverdien knyttet til navnet i celle A2. Den ser langs rad 4 for å finne navnet, og returnerer verdien fra rad 5: =XLOOKUP(A2,B4:E4,B5:E5)
XLOOKUP kan se fra bunnen og opp
Vanligvis må du lete etter en liste for å finne den første (ofte eneste) forekomsten av en verdi. XLOOKUP har et sjette argument kalt søkemodus. Dette gjør det mulig for oss å bytte oppslag til å starte nederst og slå opp en liste for å finne den siste forekomsten av en verdi i stedet.
I eksemplet nedenfor ønsker vi å finne lagernivået for hvert produkt i kolonne A.
Oppslagstabellen er i datorekkefølge, og det er flere lagerkontroller per produkt. Vi ønsker å returnere lagernivået fra forrige gang det ble kontrollert (siste forekomst av produkt-ID).
Det sjette argumentet til XLOOKUP-funksjonen gir fire alternativer. Vi er interessert i å bruke «Søk sist til først»-alternativet.
Den fullførte formelen vises her: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)
I denne formelen ble det fjerde og femte argumentet ignorert. Det er valgfritt, og vi ønsket standarden for eksakt samsvar.
Round-Up
XLOOKUP-funksjonen er etterlengtet etterfølger til både OPSLAKK- og OPPSLAG-funksjonene.
En rekke eksempler ble brukt i denne artikkelen for å demonstrere fordelene med XLOOKUP. En av dem er at XLOOKUP kan brukes på tvers av ark, arbeidsbøker og også med tabeller. Eksemplene ble holdt enkle i artikkelen for å hjelpe vår forståelse.
På grunn av dynamiske matriser blir introdusert i Excel snart kan den også returnere en rekke verdier. Dette er definitivt noe verdt å utforske videre.
Dagene for VLOOKUP er talte. XLOOKUP er her og vil snart være de facto-oppslagsformelen.