LOPSLAG, XOPSLAG eller INDEKS SAMMENLIGN: hvilket opslag skal du bruge?

Før eller siden skal alle regneark bruge et opslag. Du har en liste med ordrelinjer med et varenummer, og priserne står på et andet ark. Du har medarbejdernumre og skal bruge deres afdeling. Du har et salgstal og skal finde det provisionsinterval, det falder i. At skrive værdierne ind i hånden virker til ti rækker og går galt ved tusind.

Excel giver dig tre hovedveje: LOPSLAG, XOPSLAG og INDEKS sammen med SAMMENLIGN. De overlapper meget, og derfor dukker spørgsmålet "hvilken skal jeg bruge?" op så tit. Denne guide forklarer, hvordan hver af dem virker, hvor hver af dem går galt, og giver dig et klart svar til alle de situationer, du sandsynligvis møder.

Hvad et opslag gør

Alle opslag gør de samme tre ting:

1. Tager en værdi, der skal slås op, for eksempel et varenummer eller et medarbejdernummer.
2. Finder den i en kolonne eller række, der søges i.
3. Returnerer værdien fra samme position i en kolonne eller række, der returneres fra.

Forskellene mellem funktionerne ligger i, hvordan du fortæller Excel, hvor de kolonner er, hvad der sker, når værdien ikke findes, og hvad der sker, når værdien ikke er et eksakt match. Hold de tre spørgsmål for øje, så falder resten på plads.

LOPSLAG: den, alle kender

LOPSLAG har været i Excel fra begyndelsen, og den står i næsten alle projektmapper, der er mere end et par år gamle. Syntaksen er:

=LOPSLAG(E1;A2:C5;3;FALSK)

Læs den sådan: Slå E1 op i første kolonne af A2:C5, og returnér værdien fra tabellens tredje kolonne, med et eksakt match.

De fire argumenter er:

• opslagsværdi: det, du leder efter, her E1.
• tabelmatrix: hele tabellen, og LOPSLAG søger altid i dens første kolonne.
• kolonneindeks_nr: hvilken af tabellens kolonner der skal returneres, talt fra tabellens venstre kant og startende ved 1.
• lig_med: FALSK for et eksakt match, SAND (eller ingenting) for et tilnærmet match.

Øv den grundlæggende form i slå en pris op med LOPSLAG og med et tal som opslagsværdi.

Hvor LOPSLAG går galt

Måden, LOPSLAG er bygget på, giver fire problemer, du bør kende, fordi de står bag de fleste opslagsfejl i rigtige regneark.

Den bruger tilnærmet match som standard. Udelader du det fjerde argument, går LOPSLAG ud fra SAND. På en usorteret liste returnerer den så en forkert værdi uden nogen fejl. Det er langt den mest almindelige fejl med LOPSLAG og grunden til, at FALSK skal være en vane.

Den kan kun slå op mod højre. Værdien, du søger efter, skal stå i tabellens første kolonne. Står ID'et i kolonne C og navnet, du vil have, i kolonne A, kan LOPSLAG ikke klare det uden at flytte rundt på data.

Kolonnenummeret er et fast tal. 3 betyder tabellens tredje kolonne. Indsætter du en kolonne inde i tabellen, står der stadig 3 i formlen, og nu peger den på den forkerte kolonne. Intet advarer dig.

Den returnerer #I/T, når værdien mangler. Det er ærligt, men grimt i en rapport. Du skal bruge HVIS.FEJL eller HVISIT omkring den for at vise noget pænere.

Når tilnærmet match er det, du vil have

SAND er ikke altid en fejl. Til intervaller som rabattrin, skattetrin eller karaktergrænser er tilnærmet match præcis det rigtige, så længe første kolonne er sorteret fra mindst til størst.

LOPSLAG med SAND finder den største værdi i første kolonne, der er mindre end eller lig med opslagsværdien. 6.200 står ikke på listen, så den lander på 5.000 og returnerer 10 % rabat. Opgaven LOPSLAG med tilnærmet match træner det. Er listen ikke sorteret, er resultatet uforudsigeligt, så sortér først.

XOPSLAG: den moderne afløser

XOPSLAG kom med Excel til Microsoft 365 og Excel 2021, og Google Sheets understøtter den også. Den er lavet for at løse LOPSLAGs problemer, og til det meste nye arbejde er det den, du skal bruge.

=XOPSLAG(E2;A2:A6;C2:C6)

Læs den sådan: Slå E2 op i A2:A6, og returnér værdien fra samme række i C2:C6.

I stedet for en tabel og et kolonnenummer angiver du to områder: hvor der skal søges, og hvad der skal returneres. Den lille ændring løser tre af LOPSLAGs fire problemer på én gang:

• Den bruger eksakt match som standard. Der er ingen FALSK at glemme.
• Den kan slå op i alle retninger. Returområdet kan stå til venstre eller højre for søgeområdet eller på et andet ark.
• Den tåler indsatte kolonner. Returområdet er et område og ikke et tal, så det flytter med data.

Og det fjerde problem har fået sit eget argument:

Det fjerde argument, hvis_ikke_fundet, er det, XOPSLAG returnerer, når der ikke er noget match. Det erstatter den HVIS.FEJL, du skal pakke LOPSLAG ind i. Øv begge former i slå en pris op med XOPSLAG og XOPSLAG med en værdi for ikke fundet.

XOPSLAG har yderligere to valgfrie argumenter. Matchtilstand vælger eksakt match (0, standard), næste mindre (-1), næste større (1) eller jokertegn (2). Søgetilstand vælger, om der søges fra første element (1, standard) eller fra det sidste (-1), hvilket er nyttigt, når du vil finde den seneste post for en kunde. Du får sjældent brug for dem, men matchtilstand -1 er måden at lave intervalopslag på uden LOPSLAGs SAND.

Hvor XOPSLAG kommer til kort

XOPSLAG har én reel begrænsning: udbredelse. Excel 2019 og ældre har den ikke. Sender du en projektmappe med XOPSLAG til en, der har en ældre version, ser vedkommende #NAVN? i alle cellerne. Mange virksomheder kører stadig ældre versioner på nogle maskiner, og nogle onlinetest bruger en simulator uden den. Det er hovedgrunden til at have LOPSLAG og INDEKS SAMMENLIGN med i værktøjskassen.

Den anden ting, du skal holde øje med, er, at søgeområdet og returområdet skal være lige store. =XOPSLAG(E2;A2:A6;C2:C7) giver #VÆRDI!. Det er i virkeligheden en fordel: LOPSLAG ville uden videre returnere en værdi fra den forkerte række.

INDEKS og SAMMENLIGN: det fleksible par

Før XOPSLAG var måden at komme uden om LOPSLAGs begrænsninger at kombinere to funktioner, INDEKS og SAMMENLIGN. Det er stadig det mest fleksible opslag, og det virker i alle versioner af Excel og i alle andre regnearksprogrammer.

De to halvdele har hver sin opgave:

• SAMMENLIGN finder placeringen af en værdi i et område: =SAMMENLIGN(D2;A2:A7;0) returnerer 3, hvis D2 er det tredje element. 0 betyder eksakt match.
• INDEKS returnerer værdien på en bestemt placering i et område: =INDEKS(C2:C7;3) returnerer den tredje værdi i C2:C7.

Sæt SAMMENLIGN ind i INDEKS, så har du et opslag: =INDEKS(C2:C7;SAMMENLIGN(E2;A2:A7;0)). SAMMENLIGN finder rækken, og INDEKS returnerer værdien fra den række. Lær SAMMENLIGN alene først og derefter kombinationen.

Fordi søgeområdet og returområdet er adskilt, kan INDEKS SAMMENLIGN slå op mod venstre:

Det er noget, LOPSLAG simpelthen ikke kan. Opgaven slå op mod venstre beder om præcis det.

Opslag i to retninger

INDEKS kan tage både et rækkenummer og et kolonnenummer. Giv den to SAMMENLIGN, så kan du slå en værdi op, hvor en række og en kolonne krydser hinanden, for eksempel én sælgers tal for én måned:

Det er en af de formler, der i test skiller brugere på mellemniveau fra de avancerede. Med almindelige ord står der: I blokken B2:D4 skal du gå til rækken, hvor Bo står, og kolonnen, hvor Mar står. Du kan gøre det samme med to XOPSLAG inden i hinanden, men INDEKS med to SAMMENLIGN er det klassiske svar og virker overalt.

Prisen for fleksibiliteten

INDEKS SAMMENLIGN har to ulemper. Den er længere at skrive og sværere at læse for en, der ikke har set den før, og SAMMENLIGN skal have sit tredje argument, 0, for et eksakt match. Udelader du det, bruger SAMMENLIGN tilnærmet match, med de samme tavse forkerte svar som LOPSLAGs SAND. Gør 0 til en vane, ligesom FALSK i LOPSLAG.

Excel 365 har også XSAMMENLIGN, en moderne SAMMENLIGN, der bruger eksakt match som standard og kan søge bagfra. =INDEKS(C2:C7;XSAMMENLIGN(E2;A2:A7)) er en sikrere INDEKS SAMMENLIGN, hvis alle, der bruger filen, har en nyere Excel.

Side om side

Sådan klarer de tre sig på de punkter, der betyder noget i praksis:

VOPSLAG, LOPSLAGs vandrette tvilling, søger i tabellens første række i stedet for første kolonne. Du møder den i gamle regneark med månederne hen over toppen. XOPSLAG og INDEKS SAMMENLIGN håndterer rækker og kolonner på samme måde, så du har ikke brug for VOPSLAG til nyt arbejde.

Arbejder du i Google Sheets, gælder det samme. Sheets har alle fire funktioner med de samme argumenter som Excel, så det, du lærer her, kan du tage med dig. Om argumenterne adskilles med semikolon eller komma, afhænger af den landeindstilling, regnearket har fået, og med en dansk indstilling bruger du semikolon ligesom i dansk Excel.

Hvilken skal du bruge?

Med alt det sagt er valget ret enkelt:

• Du bygger noget nyt, og alle har Microsoft 365 eller Excel 2021 eller nyere: Brug XOPSLAG. Den er lettest at læse og sværest at lave fejl med.
• Filen bliver åbnet i ældre versioner af Excel: Brug INDEKS SAMMENLIGN, eller LOPSLAG, hvis nøglen står i første kolonne, og tabellen ikke ændrer sig.
• Du retter i en eksisterende projektmappe fuld af LOPSLAG: Vær konsekvent, og brug LOPSLAG, medmindre du alligevel bygger arket om. Et regneark med tre forskellige opslagsstile er sværere at vedligeholde end et med kun én af dem.
• Du slår intervaller eller trin op: LOPSLAG med SAND på en sorteret liste eller XOPSLAG med matchtilstand -1.
• Du slår op, hvor en række og en kolonne krydser: INDEKS med to SAMMENLIGN.
• Du sidder til en test: Brug det, opgaven beder om. Står der ikke noget, så brug den, du er hurtigst og sikrest med, og kend LOPSLAG godt nok til at skrive den uden at tænke, for det er den, de fleste test forventer.

Når et opslag giver #I/T

Alle opslag returnerer #I/T, når de ikke kan finde værdien. Nogle gange er det korrekt: Varenummeret står virkelig ikke på prislisten. Oftere står værdien der, men matcher ikke helt. Tjek disse ting i denne rækkefølge:

1. Ekstra mellemrum. "A-104 " med et mellemrum til sidst matcher ikke "A-104". Kig på cellen med F2; markøren viser mellemrummet. Ret det med FJERN.OVERFLØDIGE.BLANKE i en hjælpekolonne, eller skriv =LOPSLAG(FJERN.OVERFLØDIGE.BLANKE(E1);A2:C5;3;FALSK).
2. Tal gemt som tekst. Et ID, der er importeret fra et andet system, kan være teksten "103" i stedet for tallet 103. De ser ens ud, men matcher ikke. Tekst stilles til venstre som standard og tal til højre, og det er et hurtigt fingerpeg.
3. Det forkerte område. Tabelmatrixen eller søgeområdet indeholder ikke rækken med værdien, ofte fordi formlen er kopieret ned uden $, så området er gledet med ned. Lås området: $A$2:$C$5.
4. Tilnærmet match, hvor du ville have eksakt. LOPSLAG uden FALSK eller SAMMENLIGN uden 0 kan give #I/T på en værdi, der står på listen, hvis listen ikke er sorteret.

Når du ved, at værdien virkelig mangler, så beslut, hvad arket skal vise. I en rapport kan du pakke opslaget ind i HVIS.FEJL: =HVIS.FEJL(LOPSLAG(D2;A2:B6;2;FALSK);"Ukendt"), som i opgaven med HVIS.FEJL. HVISIT gør det samme, men fanger kun #I/T, så en formel med en anden fejl stadig viser den, og det er det sikreste, når du tjekker dit arbejde. Med XOPSLAG bruger du det fjerde argument.

Vaner, der sparer timer

Et par vaner gør opslag mere pålidelige, uanset hvilken funktion du bruger:

• Lås dine områder med $, før du kopierer et opslag ned. Tryk F4 lige efter, du har markeret området.
• Slå op på et ID, ikke på et navn. Navne har forskellige stavemåder, mellemnavne og dubletter. ID'er, koder og mailadresser er bedre nøgler.
• Hold opslagstabeller samlet ét sted, på deres eget ark og formateret som en Excel-tabel. Formler, der peger på et tabelnavn, vokser med data.
• Tjek nøglekolonnen for dubletter. Alle opslag returnerer det første match. Står et varenummer der to gange med to forskellige priser, får du den første uden nogen advarsel. En hurtig =TÆL.HVIS(A2:A100;A2) i en hjælpekolonne viser de nøgler, der står der mere end én gang.
• Test med en værdi, du kender. Før du stoler på et opslag på 5.000 rækker, så tjek tre af dem i hånden. Vælg gerne en fra toppen, en fra midten og en fra bunden.

Fra LOPSLAG til XOPSLAG i en eksisterende projektmappe

Er jeres team gået over til Microsoft 365, har du måske lyst til at skifte de gamle LOPSLAG ud med XOPSLAG. Det er værd at gøre, når du alligevel bygger et ark om, og sjældent værd at gøre bare for at gøre det. Gør du det, er oversættelsen mekanisk:

• Opslagsværdien er den samme.
• Søgeområdet er første kolonne i den gamle tabelmatrix.
• Returområdet er den kolonne, det gamle kolonneindeks_nr pegede på.
• Drop FALSK, for XOPSLAG bruger eksakt match som standard. Brugte den gamle formel SAND til intervaller, så tilføj -1 som femte argument (matchtilstand, næste mindre element).
• Havde den gamle formel HVIS.FEJL omkring sig, så flyt reserveværdien ind i XOPSLAGs fjerde argument.

Så bliver =LOPSLAG(F2;A2:C5;3;FALSK) til =XOPSLAG(F2;A2:A5;C2:C5). Ret én formel, tjek den mod det gamle resultat på et par rækker, og kopiér den så ned. Gem en kopi af den oprindelige fil, indtil den nye version har været brugt gennem en hel rapporteringsperiode.

Opslag i Excel-test

Opslag er det mest almindelige spørgsmål på mellemniveau i arbejdsgiveres Excel-test og den mest almindelige grund til, at gode kandidater taber point. Opgaverne har som regel en af disse former:

• Et simpelt eksakt opslag: "Returnér afdelingen for medarbejderen i E1." Alle funktioner kan bruges, men testen kan nævne en bestemt.
• Et opslag med en reserveværdi: "Vis Ikke fundet, hvis ID'et ikke står på listen." Det er XOPSLAGs fjerde argument eller HVIS.FEJL omkring LOPSLAG.
• Et intervalopslag: "Find provisionssatsen for hvert salgstal med tabellen i G1:H5." Tilnærmet match på en sorteret tabel.
• Et opslag mod venstre: Nøglen står til højre for den værdi, du skal bruge. Det udelukker LOPSLAG og er med for at se, om du kan INDEKS SAMMENLIGN eller XOPSLAG.
• Et opslag kopieret ned gennem en kolonne: Hver række skal have sit eget opslag, og testen tjekker hver række. Glemmer du $ på tabellen, går de nederste rækker galt.

Under tidspres er de to tjek, der fanger flest fejl: Har jeg tilføjet FALSK (eller 0 i SAMMENLIGN), og har jeg låst tabellen med $? Og husk, at en dansk test kan forvente danske funktionsnavne og semikolon, mens en engelsk forventer VLOOKUP og komma. Vores guide til Excel til jobsamtalen fortæller mere om, hvordan testene fungerer.

Kort fortalt

Alle tre opslag finder en værdi og returnerer noget fra samme række. LOPSLAG er den, du møder oftest, den skal have FALSK for et eksakt match, og den kan kun slå op mod højre. XOPSLAG er enklere, sikrere og kan mere, men kun i nyere versioner af Excel. INDEKS med SAMMENLIGN virker overalt, slår op i alle retninger og klarer opslag i to retninger, til gengæld er den sværere at læse.

Lær LOPSLAG ordentligt først, for den er overalt. Brug derefter XOPSLAG til dit eget arbejde, og gem INDEKS SAMMENLIGN til ældre filer og opslag i to retninger. Opgaverne nedenfor tager dig gennem alle tre i den rækkefølge.