Når du skal velge mellom XLOOKUP og VLOOKUP, bruker du XLOOKUP hvis alle som åpner filen har Excel for Microsoft 365, Excel 2021 eller nyere, eller Excel på nett. Den gjør alt VLOOKUP gjør, med færre feil: eksakt treff er standard, den kan lete mot venstre, og den slutter ikke å fungere når du setter inn en kolonne. Bruk bare VLOOKUP når arbeidsboken må fungere i Excel 2019 eller eldre.
Det med kompatibilitet er det viktigste forbeholdet. XLOOKUP finnes ikke i Excel 2016 eller 2019, så en fil full av XLOOKUP-formler viser #NAME?-feil (#NAVN? i norsk Excel) for alle som fortsatt bruker de versjonene.
Funksjonsnavn i norsk Excel: VLOOKUP heter FINN.RAD, HLOOKUP heter FINN.KOLONNE, XLOOKUP heter XOPPSLAG, INDEX heter INDEKS, MATCH heter SAMMENLIGNE og IFNA heter HVIS.IKKE.TILGJENGELIG. Norsk Excel bruker semikolon (;) som skilletegn mellom argumentene. Formlene i denne artikkelen er vist slik de skrives i engelsk Excel, og eksempeldataene er på engelsk, slik de er i bildene.
| VLOOKUP | XLOOKUP | |
|---|---|---|
| Lete mot venstre (returnere en kolonne til venstre for oppslagskolonnen) | Nei | Ja |
| Standard treff-type | Omtrentlig (med mindre du legger til FALSE) | Eksakt |
| Tåler at kolonner settes inn eller slettes | Nei, kolonnenummeret ligger fast | Ja, områdene justeres |
| Egendefinert «ikke funnet»-melding | Krever IFNA eller IFERROR | Innebygd (if_not_found) |
| Returnere flere kolonner samtidig | Ikke enkelt | Ja, resultatene flyter utover |
| Søke fra siste treff og oppover | Nei | Ja (search_mode -1) |
| Omtrentlig treff | Dataene må være sortert stigende | Neste mindre eller større, ingen sortering nødvendig |
Jokertegn (*, ?) | Ja, med eksakt treff | Ja, med match_mode 2 |
| Vannrette oppslag | Nei, det er HLOOKUP | Ja, samme funksjon |
| Hastighet | Ingen praktisk forskjell i typiske arbeidsbøker | Det samme, pluss valgfritt binærsøk på sorterte data |
| Tilgjengelig i | Alle versjoner av Excel | Microsoft 365, Excel 2021, Excel 2024, Excel på nett, mobilappene |
Syntaksen side om side
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Den store forskjellen er hvordan du forteller Excel hvor svaret finnes. VLOOKUP tar en hel tabell og et kolonnenummer talt fra venstre kant av tabellen. XLOOKUP tar to separate områder: kolonnen det skal søkes i og kolonnen det skal returneres fra.
Det samme oppslaget skrevet på begge måter
Her er en liten produktliste i A1:D6, med en produkt-ID skrevet i G2:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product ID | Product | Category | Price |
| 2 | P-101 | Wireless Mouse | Accessories | $24.99 |
| 3 | P-102 | USB-C Hub | Accessories | $39.50 |
| 4 | P-103 | Laptop Stand | Office | $45.00 |
| 5 | P-104 | Webcam HD | Video | $59.00 |
| 6 | P-105 | Desk Lamp | Office | $32.75 |
For å finne prisen på P-104:
=VLOOKUP(G2, A2:D6, 4, FALSE)
=XLOOKUP(G2, A2:A6, D2:D6)
Begge returnerer $59.00. VLOOKUP søker i den første kolonnen i A2:D6 og returnerer den fjerde kolonnen. FALSE tvinger frem eksakt treff. XLOOKUP søker i A2:A6 og returnerer den tilsvarende cellen fra D2:D6, med eksakt treff som standard.
Hvis du skal kopiere formelen nedover en kolonne, låser du tabellen med absolutte referanser, for eksempel =XLOOKUP(G2, $A$2:$A$6, $D$2:$D$6). Trykk F4 på Windows eller Cmd+T på Mac mens markøren står i en referanse for å legge til $-tegnene.
VLOOKUPs to klassiske fallgruver
1. Å glemme FALSE (eller 0)
Det siste VLOOKUP-argumentet er valgfritt, og når du utelater det, bruker Excel omtrentlig treff. Med omtrentlig treff antar VLOOKUP at den første kolonnen er sortert, og returnerer den nærmeste verdien som er mindre enn eller lik det du søkte etter, uten noen advarsel.
Prøv =VLOOKUP("P-110", A2:D6, 4) på tabellen ovenfor. P-110 finnes ikke, men i stedet for #N/A (#I/T i norsk Excel) får du $32.75, prisen på P-105, fordi «P-105» er den nærmeste mindre verdien. På usorterte data kan resultatene bli enda mer tilfeldige.
Avslutt alltid en VLOOKUP med eksakt treff med FALSE eller 0. XLOOKUP unngår problemet fordi den bruker eksakt treff med mindre du ber om noe annet.
2. Det hardkodede kolonnenummeret
4 i VLOOKUP-formelen er bare et tall. Hvis noen setter inn en Supplier-kolonne mellom Product og Category, blir tabellen A:E, Price flyttes til kolonne E, og formelen returnerer fortsatt den fjerde kolonnen, som nå er Category. Du ville sett «Video» i stedet for $59.00.
XLOOKUP peker på selve priskolonnen (D2:D6), så Excel oppdaterer den automatisk til E2:E6, og svaret forblir riktig.
Hva XLOOKUP kan som VLOOKUP ikke kan
Lete mot venstre
VLOOKUP kan bare returnere kolonner til høyre for oppslagskolonnen. XLOOKUP bryr seg ikke om retning. For å finne produkt-ID-en for et produktnavn i G2:
=XLOOKUP(G2, B2:B6, A2:A6)
Med «Desk Lamp» i G2 returnerer dette P-105, selv om kolonne A ligger til venstre for kolonne B.
Vis en vennlig melding når ingenting blir funnet
=XLOOKUP(G2, A2:A6, D2:D6, "Not found")
VLOOKUP-ekvivalenten trenger en omkapsling: =IFNA(VLOOKUP(G2, A2:D6, 4, FALSE), "Not found"). I et norsk regneark kan du skrive teksten som «Ikke funnet».
Returnere flere kolonner i én formel
=XLOOKUP(G2, A2:A6, B2:D6)
For P-104 flyter dette Webcam HD, Video og $59.00 utover i tre celler side ved side. La disse cellene være tomme, ellers får du en #SPILL!-feil.
Finne siste treff i stedet for det første
I en logg der den samme kunden forekommer mange ganger, setter du search_mode til -1 for å søke nedenfra og opp og få den nyeste oppføringen:
=XLOOKUP(G2, A2:A500, C2:C500, "Not found", 0, -1)
Omtrentlig treff uten sortering
For trinnvise priser eller provisjonssatser fungerer VLOOKUPs omtrentlige treff bare hvis den første kolonnen er sortert stigende. XLOOKUPs match_mode -1 (eksakt treff, eller neste mindre element) og 1 (eksakt treff, eller neste større element) trenger ikke sorterte data. Med minstebeløp for bestilling i J2:J5 og rabatter i K2:K5:
=VLOOKUP(B2, J2:K5, 2, TRUE)
=XLOOKUP(B2, J2:J5, K2:K5, , -1)
Jokertegn
Begge støtter * og ?, men XLOOKUP trenger match_mode 2 for å behandle dem som jokertegn:
=VLOOKUP("Desk*", B2:D6, 3, FALSE)
=XLOOKUP("Desk*", B2:B6, D2:D6, , 2)
Begge returnerer $32.75 for Desk Lamp.
INDEX/MATCH: det klassiske alternativet
Før XLOOKUP var standardløsningen på VLOOKUPs begrensninger INDEX sammen med MATCH (INDEKS og SAMMENLIGNE i norsk Excel). Den leter mot venstre, tåler innsatte kolonner og fungerer i alle versjoner av Excel, noe som gjør den til det beste valget for filer som må kunne åpnes i Excel 2019 eller eldre:
=INDEX(D2:D6, MATCH(G2, A2:A6, 0))
=INDEX(A2:A6, MATCH(G2, B2:B6, 0))
Den første returnerer prisen for ID-en i G2, og den andre er eksempelet med oppslag mot venstre. 0 i MATCH betyr eksakt treff, og akkurat som VLOOKUPs FALSE er det lett å glemme. XLOOKUP er rett og slett den kortere, tryggere versjonen av den samme ideen.
Slik konverterer du VLOOKUP-formler til XLOOKUP
Det finnes ingen innebygd omformer, men oversettelsen er mekanisk:
- Finn VLOOKUP-formlene dine: trykk
Ctrl+F(Cmd+Fpå Mac), skriv VLOOKUP (i norsk Excel FINN.RAD), klikk Alternativer hvis de ekstra innstillingene er skjult, sett Søk i til Formler, og klikk Finn alle. - Behold det første argumentet (oppslagsverdien) som det er.
- Erstatt tabellen med dens første kolonne som
lookup_array. - Erstatt kolonnenummeret med den tilsvarende kolonnen som
return_array. Kolonne 5 i en tabell som starter i kolonne A er kolonne E. - Fjern
FALSE. Hvis originalen endte medTRUEeller ikke hadde noe siste argument, legger du tilmatch_mode-1 for å beholde oppførselen «neste mindre».
For eksempel:
=VLOOKUP(A2, Products!$A$2:$F$500, 5, FALSE)
=XLOOKUP(A2, Products!$A$2:$A$500, Products!$E$2:$E$500)
Test noen resultater mot den gamle formelen før du konverterer et helt ark. VLOOKUP-formler med omtrentlig treff på usorterte data kan ha returnert feil verdier hele tiden, så XLOOKUPs svar kan med rette være annerledes.
Hva skjer når en XLOOKUP-fil åpnes i Excel 2016 eller 2019
Arbeidsboken åpnes, men de versjonene kjenner ikke funksjonen. I formellinjen vises den som =_xlfn.XLOOKUP(...). Cellene kan i starten fortsatt vise de sist lagrede resultatene, men så snart formelen beregnes på nytt, returnerer den #NAME?.
Hvis du deler filer med folk som bruker eldre versjoner:
- Bruk INDEX/MATCH eller VLOOKUP i stedet, eller
- Send en kopi der resultatene er limt inn som verdier (Kopier → Lim inn utvalg → Verdier).
Det samme gjelder andre nyere funksjoner som FILTER, UNIQUE og XMATCH.
XLOOKUP i Google Sheets
Google Sheets har også XLOOKUP, med samme rekkefølge på argumentene: =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]). Formlene i dette innlegget fungerer der slik de er skrevet, så XLOOKUP er et trygt valg for filer som flyttes mellom Excel og Sheets.
Hvorfor oppslaget gir #N/A eller feil verdi
- Ekstra mellomrom. «P-104 » med et mellomrom på slutten stemmer ikke med «P-104». Rens dataene med
TRIM(TRIMMEi norsk Excel). - Tall lagret som tekst. En ID skrevet som tekst stemmer ikke med den samme ID-en lagret som tall. Se etter den grønne trekanten i hjørnet av cellen og konverter kolonnen til én type.
- Manglende FALSE i VLOOKUP. Du får et plausibelt, men feil svar i stedet for
#N/A. - Områder som flyttet seg da du kopierte formelen. Bruk absolutte
$-referanser for oppslagstabellen. #REF!fra VLOOKUP. Kolonnenummeret er større enn antall kolonner i tabellen.#VALUE!fra XLOOKUP (#VERDI!i norsk Excel). Oppslags- og returområdene har ulik størrelse, for eksempel A2:A6 og D2:D60.#NAME?Funksjonsnavnet er stavet feil, eller filen er åpnet i en versjon uten XLOOKUP.
Vanlige spørsmål
Er XLOOKUP bedre enn VLOOKUP? Ja, i nesten alle tilfeller. Den har eksakt treff som standard, leter i alle retninger, slutter ikke å fungere når kolonner flyttes, og håndterer manglende verdier uten omkapsling. VLOOKUPs eneste reelle fordel er at den fungerer i alle versjoner av Excel.
Er XLOOKUP raskere enn VLOOKUP? I vanlige arbeidsbøker merker du ingen forskjell. På svært store sorterte lister kan XLOOKUPs binærsøkmodi (search_mode 2 eller -2) gjøre ting raskere, men de gir feil resultater hvis dataene ikke er sortert.
Hvorfor viser XLOOKUP #NAME? på datamaskinen min? Excel-versjonen din inneholder ikke XLOOKUP. Den finnes i Microsoft 365, Excel 2021 og nyere og Excel på nett, men ikke i Excel 2019 eller 2016. Bruk INDEX/MATCH i stedet.
Erstatter XLOOKUP også HLOOKUP? Ja. Hvis oppslags- og returområdene er rader i stedet for kolonner, søker XLOOKUP vannrett med den samme syntaksen.
Kan XLOOKUP returnere alle treff, ikke bare det første? Nei, den returnerer ett treff (eller én rad med kolonner). For å få alle rader som stemmer, bruker du FILTER, for eksempel =FILTER(A2:D6, C2:C6="Office").
Bør jeg fortsatt lære VLOOKUP? Det er verdt å kunne kjenne den igjen, for du finner den i eldre arbeidsbøker og maler. For nye formler lærer du XLOOKUP, og husker INDEX/MATCH for filer som må åpnes i eldre Excel.
La AI skrive oppslaget for deg
Oppslagsformler er der små detaljer, som en manglende FALSE eller et forskjøvet område, gir flest stille feil. Med CoreGPT for Excel beskriver du hva du trenger, for eksempel «finn prisen for produkt-ID-en i G2» eller «merk hver rad som over eller under målet», og den skriver formelen inn i arket ditt med en kort forklaring. Det er gratis å komme i gang, og det fungerer i Excel for Windows, Mac og på nett.
CoreGPTBruk denne formelen og fyll den nedover:
=IF(C2>=D2,"Over","Under")Prøv selv: CoreGPT for Excel
Les også:
