Den nye XLOOKUP-funksjonen i Excel er designet for å ta over for den populære VLOOKUP-funksjonen. XLOOKUP representerer en betydelig forbedring, ikke bare ved å erstatte VLOOKUP, men også ved å tilby ekstra funksjonalitet og overvinne tidligere begrensninger. La oss utforske hva denne nye funksjonen har å by på.
Hva er XLOOKUP?
XLOOKUP er mer enn bare en oppgradering av VLOOKUP; den erstatter også HLOOKUP, og kombinerer dermed funksjonaliteten til to funksjoner i én. En av de største fordelene med XLOOKUP er dens evne til å søke til venstre for oppslagskolonnen, noe som VLOOKUP ikke tillot. I tillegg har XLOOKUP nøyaktig samsvar som standard, og den bruker celleområder i stedet for kolonnenumre, noe som gjør den enklere og mer fleksibel i bruk. Vi vil demonstrere hvordan denne funksjonen fungerer.
Foreløpig er XLOOKUP tilgjengelig for brukere som deltar i Microsofts Insiders-program. Hvis du ønsker å prøve de nyeste funksjonene i Excel før alle andre, kan du melde deg inn i Insiders-programmet. Microsoft planlegger å rulle ut XLOOKUP til alle Office 365-brukere i nær fremtid.
Hvordan bruke XLOOKUP
La oss se på et konkret eksempel på hvordan XLOOKUP brukes. Tenk deg at vi har en liste over ID-er i kolonne A, og vi ønsker å finne tilhørende avdelinger fra kolonne F.
Dette er et typisk eksempel på et nøyaktig oppslag. XLOOKUP trenger kun tre obligatoriske opplysninger for å utføre et slikt oppslag.
Selv om XLOOKUP kan ha opptil seks argumenter, er det de tre første som er mest relevante for et eksakt treff. Disse er:
- Lookup_value: Verdien du leter etter.
- Lookup_array: Området der du skal lete.
- Return_array: Området som inneholder verdien du vil returnere.
Formelen for dette eksemplet vil være: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)
La oss nå se nærmere på noen av de fordelene XLOOKUP har over VLOOKUP.
Ingen kolonneindeksnummer
I VLOOKUP var det tredje argumentet, kolonneindeksnummeret, ofte en kilde til frustrasjon. XLOOKUP eliminerer dette problemet ved å la deg angi hele området du ønsker å returnere verdier fra (kolonne F i dette tilfellet).
En annen viktig fordel er at XLOOKUP kan hente data til venstre for oppslagskolonnen, i motsetning til VLOOKUP. Vi kommer tilbake til dette senere.
Med XLOOKUP unngår du også problemer med ødelagte formler når du legger til nye kolonner i regnearket. Returområdet vil automatisk justere seg.
Eksakt treff som standard
Mange som har brukt VLOOKUP har opplevd forvirring rundt hvorfor man måtte spesifisere et eksakt treff. Dette er ikke lenger et problem.
XLOOKUP har som standard eksakt treff, som er den vanligste grunnen til å bruke en oppslagsformel. Dette betyr at det femte argumentet ikke er nødvendig så ofte, og det reduserer potensielle feil, spesielt for nye brukere av formelen.
XLOOKUP krever færre opplysninger enn VLOOKUP, er enklere å bruke og mer robust.
XLOOKUP kan søke til venstre
Muligheten til å velge et returområde gjør XLOOKUP mer fleksibel enn VLOOKUP. Med XLOOKUP spiller det ingen rolle hvilken rekkefølge kolonnene i tabellen har.
VLOOKUP var begrenset til å søke i den venstre kolonnen i en tabell og hente verdier fra et spesifikt antall kolonner til høyre.
La oss se på et eksempel der vi vil slå opp en ID (kolonne E) og returnere navnet til personen (kolonne D).
Med XLOOKUP kan dette enkelt gjøres ved hjelp av denne formelen: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)
Hva skjer hvis oppslaget ikke finner noe?
Brukere av oppslagsfunksjoner kjenner godt til #N/A-feilmeldingen som dukker opp når VLOOKUP eller MATCH ikke finner det de leter etter. Ofte er det en god grunn til at oppslaget ikke finner verdien.
Mange brukere ønsker å skjule disse feilmeldingene fordi de ikke gir nyttig informasjon. Det finnes selvfølgelig måter å gjøre dette på.
XLOOKUP har et innebygd argument kalt «if_not_found» som håndterer denne typen feil. La oss bruke det i det forrige eksemplet med en feil ID.
Denne formelen vil vise teksten «Feil ID» i stedet for feilmeldingen: =XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,"Feil ID")
Bruke XLOOKUP for områdeoppslag
Selv om det ikke er like vanlig som eksakte treff, er det nyttig å kunne bruke oppslagsformler for å lete etter verdier innenfor bestemte områder. I dette eksemplet ønsker vi å returnere en rabatt basert på beløpet som er brukt.
Denne gangen leter vi ikke etter en bestemt verdi. Vi må finne ut hvor verdiene i kolonne B faller innenfor områdene i kolonne E. Dette vil bestemme rabatten som skal gis.
XLOOKUP har et femte argument som er valgfritt, kalt «match_mode». Husk at funksjonen har eksakt treff som standard.
Du ser at XLOOKUP har bedre muligheter for omtrentlige treff enn VLOOKUP.
Du kan velge å finne den nærmeste verdien som er mindre enn (-1) eller den nærmeste verdien som er større enn (1). Det er også mulig å bruke jokertegn (2) som ? eller *. Denne innstillingen er ikke på som standard slik det var i VLOOKUP.
Formelen i dette eksemplet returnerer den nærmeste verdien som er mindre enn den vi leter etter hvis det ikke finnes et eksakt treff: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)
Vi ser imidlertid en feil i celle C7 der #N/A returneres (argumentet «if_not_found» ble ikke brukt). Denne cellen burde ha vist en rabatt på 0% fordi beløpet 64 ikke oppfyller kravene for noen rabatt.
En annen fordel med XLOOKUP er at oppslagsområdet ikke trenger å være i stigende rekkefølge slik det var med VLOOKUP.
Legg til en ny rad nederst i oppslagstabellen, og utvid det brukte området i formelen ved å klikke og dra hjørnene.
Formelen retter opp feilen umiddelbart. Det er ikke noe problem å ha «0» nederst i området.
Personlig ville jeg fortsatt sortert tabellen etter oppslagskolonnen. Det å ha «0» nederst ville gjort meg gal. Men det faktum at formelen ikke gikk i stykker, er imponerende.
XLOOKUP erstatter HLOOKUP
Som nevnt, er XLOOKUP også laget for å erstatte HLOOKUP. Det er altså en funksjon som erstatter to, noe som er utmerket.
HLOOKUP brukes for horisontale oppslag, det vil si for å søke langs rader.
HLOOKUP er ikke like kjent som VLOOKUP, men er nyttig i eksempler der overskriftene er i kolonne A, og dataene er langs rad 4 og 5.
XLOOKUP kan søke i begge retninger – nedover kolonner og langs rader. Vi trenger ikke lenger to forskjellige funksjoner.
I dette eksemplet brukes formelen til å returnere salgsverdien knyttet til navnet i celle A2. Den søker langs rad 4 for å finne navnet og returnerer verdien fra rad 5: =XLOOKUP(A2,B4:E4,B5:E5)
XLOOKUP kan søke fra bunnen og opp
Vanligvis må vi lete etter den første forekomsten av en verdi i en liste. XLOOKUP har et sjette argument som heter «search_mode». Dette gjør det mulig for oss å endre oppslaget slik at det starter nederst i listen og finner den siste forekomsten av en verdi i stedet.
I eksemplet nedenfor ønsker vi å finne lagernivået for hvert produkt i kolonne A.
Oppslagstabellen er sortert etter dato, og det er flere lagerkontroller for hvert produkt. Vi ønsker å returnere lagernivået fra forrige gang det ble kontrollert (siste forekomst av produkt-ID).
Det sjette argumentet i XLOOKUP-funksjonen gir oss fire alternativer. Vi er interessert i «Søk sist til først»-alternativet.
Den fullstendige formelen blir da: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)
I denne formelen ble det fjerde og femte argumentet ignorert. De er valgfrie, og vi ønsket standarden for eksakt treff.
Oppsummering
XLOOKUP er en etterlengtet nykommer som forventes å erstatte både VLOOKUP og HLOOKUP.
Denne artikkelen har gitt en rekke eksempler som demonstrerer fordelene med XLOOKUP. XLOOKUP kan brukes på tvers av ark og arbeidsbøker, og også med tabeller. Eksemplene i artikkelen er holdt enkle for å gjøre det lettere å forstå funksjonen.
Med introduksjonen av dynamiske matriser i Excel, vil XLOOKUP snart også kunne returnere en rekke verdier. Dette er definitivt noe som er verdt å utforske videre.
VLOOKUP-funksjonens dager er talte. XLOOKUP er her og vil snart være den foretrukne oppslagsformelen.