Har du Excel til Microsoft 365, Excel 2021 eller nyere, så brug XOPSLAG (engelsk: XLOOKUP). Den finder som standard kun præcise match, kan slå op til venstre og tåler, at nogen indsætter en kolonne i dine data. Skal regnearket også virke i Excel 2016 eller 2019, så brug INDEKS og SAMMENLIGN (INDEX og MATCH) i stedet. LOPSLAG (VLOOKUP) virker overalt, men har to svagheder, som giver forkerte tal uden fejlmeddelelse.
Nedenfor laver vi det samme opslag med alle tre og viser, hvor de opfører sig forskelligt.
Det samme opslag på tre måder
Eksemplet er en lille prisliste. Vi vil finde prisen på varenummeret i E2.
| Række | A | B | C | D | E | F | G | H |
|---|---|---|---|---|---|---|---|---|
| 1 | Varenr | Varenavn | Pris | Varenr | LOPSLAG | XOPSLAG | INDEKS/SAMMENLIGN | |
| 2 | 1001 | Kuglepen, blå | 8,50 | 1004 | 42,95 | 42,95 | 42,95 | |
| 3 | 1002 | Hæftemaskine | 89,00 | 1005 | #I/T | #I/T | #I/T | |
| 4 | 1004 | Kopipapir A4 | 42,95 | |||||
| 5 | 1007 | Notesblok A5 | 24,50 | |||||
| 6 | 1010 | Tavlemarker | 17,95 | |||||
| 7 | 1012 | Brevordner | 32,00 |
Alle tre gav 42,95 for varenr. 1004, og alle tre gav #I/T for 1005, som ikke findes. #I/T er den danske udgave af #N/A. Forskellene viser sig først, når data eller formel ikke er helt, som de plejer.
Beslutningstabel
| Situation | LOPSLAG | XOPSLAG | INDEKS/SAMMENLIGN |
|---|---|---|---|
| Standard, hvis du udelader matchtypen | Omtrentligt match: kan give forkert tal | Præcist match | Omtrentligt match: kan give forkert tal |
| Opslag til venstre (fx varenr ud fra navn) | Kan ikke | Ja | Ja |
| Nogen indsætter en kolonne i data | Returnerer den forkerte kolonne uden fejl | Følger med | Følger med |
| Egen tekst, når værdien ikke findes | Kræver HVISIT eller HVIS.FEJL |
Indbygget 4. argument | Kræver HVISIT eller HVIS.FEJL |
| Flere kolonner på én gang | Nej (én formel pr. kolonne) | Ja, returområdet må være flere kolonner | Ja, med {1\2} som kolonnenummer (overløb kræver Microsoft 365 eller Excel 2021+) |
| Findes i Excel 2016 og 2019 | Ja | Nej, ifølge Microsoft | Ja |
Resten af artiklen viser de afprøvede resultater bag tabellen.
Når nogen indsætter en kolonne
Dette er den vigtigste forskel i praksis, fordi fejlen ikke viser sig som en fejl. Vi lavede de tre formler ovenfor på et ark og prislisten på et andet ark. Derefter indsatte vi en hel kolonne i prislisten mellem Varenavn og Pris.
Excel justerede selv alle områderne i formlerne. Men kolonnenummeret 3 i LOPSLAG er bare et tal, og det bliver ikke ændret:
| Formel efter indsættelsen (som Excel justerede den) | Tom ny kolonne | Ny kolonne udfyldt med Enhed |
|---|---|---|
=LOPSLAG(A2;Varer!$A$2:$D$7;3;FALSK) |
0 | pakke |
=XOPSLAG(A2;Varer!$A$2:$A$7;Varer!$D$2:$D$7) |
42,95 | 42,95 |
=INDEKS(Varer!$D$2:$D$7;SAMMENLIGN(A2;Varer!$A$2:$A$7;0)) |
42,95 | 42,95 |
Så længe den nye kolonne er tom, giver LOPSLAG prisen 0. Når den bliver udfyldt, giver den teksten “pakke”. Ingen af delene er en fejlværdi, så en sum over kolonnen bliver bare forkert. Vi fik det samme resultat med hele kolonner som område (Varer!A:C, der blev til Varer!A:D).
Opslag til venstre
LOPSLAG søger altid i den første kolonne af området og kan kun returnere kolonner til højre for den. Vil du finde varenummeret ud fra varenavnet, skal du lede i kolonne B og returnere kolonne A.
Med LOPSLAG er der ingen direkte løsning. =LOPSLAG(E6;$A$2:$C$7;1;FALSK) gav #I/T, fordi den leder efter teksten “Hæftemaskine” blandt varenumrene. Den gængse løsning er at flytte kolonnerne, så søgekolonnen står længst til venstre.
Hvad der sker, når du udelader matchtypen
Det sidste argument i LOPSLAG og SAMMENLIGN er valgfrit, og standarden er omtrentligt match. XOPSLAG bruger præcist match som standard. Vi slog tre varenumre op uden det sidste argument:
| Række | E | F | G | H |
|---|---|---|---|---|
| 8 | Varenr | LOPSLAG(E9;$A$2:$C$7;3) | XOPSLAG(E9;$A$2:$A$7;$C$2:$C$7) | INDEKS(…;SAMMENLIGN(E9;$A$2:$A$7)) |
| 9 | 1004 | 42,95 | 42,95 | 42,95 |
| 10 | 1005 | 42,95 | #I/T | 42,95 |
| 11 | 1000 | #I/T | #I/T | #I/T |
Varenummer 1005 findes ikke, men LOPSLAG og SAMMENLIGN fandt den nærmeste mindre værdi, 1004, og gav dens pris. Det er præcis, hvad omtrentligt match er beregnet til, fx rabattrin, men i en prisliste er det en fejl, du ikke får øje på. Skriv derfor altid FALSK i LOPSLAG og 0 i SAMMENLIGN, når du vil have et præcist match.
Skal du bruge omtrentligt match med vilje, så læs guiden om intervalopslag med rabattrin og karakterer. Giver dit opslag #I/T, selvom værdien står i listen, finder du årsagen i fejlfinding af LOPSLAG og #I/T.
Når værdien ikke findes: HVISIT, HVIS.FEJL eller XOPSLAG’s eget argument
XOPSLAG har et fjerde argument, hvis_ikke_fundet, der bestemmer, hvad der vises, når værdien ikke findes. Med de to andre metoder pakker du formlen ind i HVISIT (IFNA) eller HVIS.FEJL (IFERROR).
For varenr. 1005 gav alle tre udgaver “Ukendt varenr”, også HVIS.FEJL. Forskellen viser sig, når formlen fejler af en anden grund. Vi ændrede kolonnenummeret til 4, selvom området kun har tre kolonner, og slog det eksisterende varenr. 1004 op:
| Formel | Resultat |
|---|---|
=LOPSLAG(E15;$A$2:$C$7;4;FALSK) |
#REFERENCE! |
=HVISIT(LOPSLAG(E15;$A$2:$C$7;4;FALSK);"Ukendt varenr") |
#REFERENCE! |
=HVIS.FEJL(LOPSLAG(E15;$A$2:$C$7;4;FALSK);"Ukendt varenr") |
Ukendt varenr |
=XOPSLAG(E15;$A$2:$A$7;$C$2:$C$6;"Ukendt varenr") (returområde en række for kort) |
#VÆRDI! |
HVIS.FEJL påstår, at varenummeret er ukendt, selvom det findes, og skjuler dermed en fejl i selve formlen. HVISIT reagerer kun på #I/T og lader andre fejl stå synlige. XOPSLAG’s eget argument virker på samme måde: det dækker kun “ikke fundet”.
Brug derfor HVISIT frem for HVIS.FEJL omkring opslag. Et større eksempel, hvor HVIS.FEJL gør en hel total til 0, står i Excel-fejl på dansk.
Flere kolonner på én gang
XOPSLAG kan returnere flere kolonner fra samme række. Formlen står i én celle og løber over i cellen til højre:
Med INDEKS får du det samme ved at give en matrixkonstant med kolonnenumre. På dansk skilles kolonnerne med omvendt skråstreg:
Formlen løber over i to celler, og det kræver dynamiske matrixer, altså Microsoft 365 eller Excel 2021 og nyere. Bemærk også, at prisen i den overløbne celle stod som 24,5 og ikke 24,50: celleformatet følger ikke med. Er cellen til højre ikke tom, får du #OVERLØB!, som vi forklarer i guiden om FILTRER. Skal du slå op på to betingelser, fx varenummer og dato, så se opslag på to kriterier.
Opslag i et andet ark eller en anden projektmappe
Ligger prislisten på et andet ark, sætter du arkets navn og et udråbstegn foran området. Alt andet i formlen er det samme. I eksempelfilen står prislisten på arket Opslag, og arket Opslag i andet ark slår op i den:
Den nemmeste måde at skrive den slags på er at begynde formlen, skifte til det andet ark og markere området med musen. Så skriver Excel selv arknavnet korrekt.
Husk, at kolonnenummeret i LOPSLAG tælles inde i det område, du peger på. Med området 'Kolonne indsat'!$A$2:$D$7 og kolonnenummer 3 fik vi teksten “pakke” i stedet for prisen, fordi Pris står i kolonne 4 på det ark.
Arknavne med mellemrum skal i anførselstegn
Anførselstegnene er enkelte ('), og de står kun om selve arknavnet. Udråbstegnet og området står udenfor. Vi skrev den samme formel med fem forskellige arknavne:
| Arknavn | Sådan skal der stå | Hvis du udelader anførselstegnene |
|---|---|---|
| Varer | Varer!$A$2:$C$7 |
virker fint, de er ikke nødvendige |
| Årspriser | Årspriser!$A$2:$C$7 |
virker fint. Æ, ø og å kræver ikke anførselstegn |
| Prisliste 2026 | 'Prisliste 2026'!$A$2:$C$7 |
Excel laver det om til Prisliste '2026'!…, og formlen giver #NAVN? |
| Pris-liste | 'Pris-liste'!$A$2:$C$7 |
formlen gav #I/T i stedet for prisen |
| 2026 | '2026'!$A$2:$C$7 |
Excel sætter selv anførselstegnene på |
Skriver du anførselstegn om et navn, der ikke behøver dem, sker der ikke noget: vi skrev 'Årspriser'!$A$2:$C$7, og Excel gemte formlen som Årspriser!$A$2:$C$7.
Når prislisten ligger i en anden fil
Er den anden projektmappe åben, står filnavnet i kantede parenteser foran arknavnet:
Lukker du den anden fil, skriver Excel selv hele stien ind i formlen:
Opslaget virker altså videre. Vi afprøvede LOPSLAG, XOPSLAG, hele kolonner (!A:C) og SUM mod en lukket fil, og alle gav det samme som med filen åben. Tre ting er værd at vide:
- Tallene er gemt i din egen fil. Vi ændrede prisen på Kopipapir A4 til 59,95 i den lukkede fil med et andet program. Formlen blev ved med at vise 42,95, også efter en fuld genberegning. Først da vi bad Excel opdatere henvisningen til filen, kom 59,95 frem. Excel regner altså med den kopi, der blev gemt i din egen fil sidst, indtil du opdaterer.
INDIREKTE(engelsk: INDIRECT) virker ikke mod en lukket fil.=INDIREKTE("'[Prisliste 2026.xlsx]Priser'!C4")gav 42,95, mens filen var åben, og#REFERENCE!i samme sekund, den blev lukket. Almindelige henvisninger har ikke det problem, så brug dem i stedet.- Ligger filen i en OneDrive-mappe, bliver stien til en webadresse. I vores test skrev Excel
'https://…/[Prisliste 2026.xlsx]Priser'!$A$2:$C$7i stedet for en sti med drevbogstav. Formlen virkede lige så godt, men den bliver lang og svær at læse.
Hele stien står i formlen. Skal to personer dele arket, er det derfor som regel nemmere at lægge prislisten på et ark i den samme projektmappe.
Versioner
Deler du projektmapper med nogen på en ældre version, så vælg INDEKS/SAMMENLIGN. Kopierer du formler fra engelske guides, skal funktionsnavne og kommaer oversættes. Det gennemgår vi i formler fra engelske guides i Excel på dansk. Er dine data en Excel-tabel, kan du bruge kolonnenavne i stedet for cellehenvisninger, se strukturerede referencer. Vil du sammenligne to hele lister, så se hvem mangler, og hvem er nye.