Sådan retter du #I/T i XOPSLAG og LOPSLAG

Du har et kundenummer i den ene fane og en liste med navne i den anden. Du skriver et opslag, trykker Enter, og der står #I/T. Du kigger på listen. Nummeret står der. Du kan se det med dine egne øjne.

Det er nok det mest irriterende sted at stå i Excel, fordi programmet ikke siger hvad der mangler. Det siger bare "fandt ikke noget", og så er du på egen hånd.

Her får du de fem årsager, der ligger bag stort set alle #I/T i opslag, og den konkrete rettelse til hver af dem. Regn med et kvarter, hvis du læser med regnearket åbent.

Bruger du engelsk Excel, hedder fejlen #N/A. Funktionerne hedder XLOOKUP og VLOOKUP, hvor dansk Excel bruger XOPSLAG og LOPSLAG. Jeg bruger de danske navne herunder.

1. Der er mellemrum i den ene af værdierne

Hvad det betyder: For Excel er "12345 " og "12345" to forskellige ting. Et enkelt mellemrum efter værdien er nok til, at opslaget ikke finder noget.

Typisk årsag: Data der er kopieret fra et system, en mail eller et website. Eksporter fra økonomisystemer og webshops slæber næsten altid mellemrum med, og de er usynlige på skærmen.

Sådan retter du: Test det først. Skriv =LÆNGDE(A2) ved siden af værdien. Står der 6, hvor du forventede 5, har du fundet synderen. Ryd derefter op med =FJERN.OVERFLØDIGE.BLANKE(A2), som fjerner mellemrum foran og bagved og reducerer dobbelte mellemrum inde i teksten til ét.

Virker det stadig ikke, er det sandsynligvis et hårdt mellemrum fra en hjemmeside. Det er tegn nummer 160, og FJERN.OVERFLØDIGE.BLANKE rører det ikke. Brug i stedet =FJERN.OVERFLØDIGE.BLANKE(UDSKIFT(A2;TEGN(160);" ")).

2. Tallet er gemt som tekst i den ene liste

Hvad det betyder: Excel skelner mellem tallet 12345 og teksten "12345". De ser ens ud, men de matcher ikke hinanden i et opslag.

Typisk årsag: Du henter data ind fra CSV, eller en kolonne er på et tidspunkt blevet formateret som tekst. Postnumre, varenumre og kontonumre rammes oftest, fordi de gerne må starte med nul.

Sådan retter du: Kig efter justeringen i cellen. Tal stiller sig automatisk til højre, tekst til venstre. Er hele kolonnen venstrestillet, er tallene tekst. Den grønne trekant i cellens hjørne giver dig valget "Konvertér til tal", og det er den hurtigste vej, når det er få celler.

Er det en hel kolonne, så markér den og brug Data, Tekst til kolonner, klik Udfør med det samme. Det lyder forkert, men det tvinger Excel til at læse kolonnen forfra og konvertere den.

Skal du ikke røre kildedataene, kan du regne dig ud af det i selve opslaget med =XOPSLAG(VÆRDI(A2);Ark2!A:A;Ark2!B:B), hvis det er opslagsværdien der er tekst.

3. LOPSLAG søger tilnærmet, fordi du glemte FALSK

Hvad det betyder: LOPSLAG har fire argumenter: =LOPSLAG(opslagsværdi;tabelmatrix;kolonneindeks_nr;[intervalopslag]). Skriver du ikke det sidste, antager Excel tilnærmet match, og det kræver at listen er sorteret.

Typisk årsag: Det fjerde argument står i kantede parenteser i hjælpeteksten, så det ligner noget frivilligt. Det er den klassiske faldgrube ved LOPSLAG, og resultatet er enten #I/T eller, værre, et forkert svar der ser rigtigt ud.

Sådan retter du: Skriv altid FALSK eller 0 som sidste argument: =LOPSLAG(A2;Ark2!A:B;2;FALSK). Skal du bruge tilnærmet match med vilje, for eksempel til en satstabel, så sortér opslagskolonnen stigende først.

XOPSLAG er bygget anderledes: den søger præcist som standard, og det er en af de bedste grunde til at skifte.

4. Opslagsområdet peger forkert, når du trækker formlen ned

Hvad det betyder: De første par rækker virker, og så begynder #I/T. Området er gledet med nedad, så de nederste opslag kigger i en liste, der er blevet kortere.

Typisk årsag: Du har skrevet Ark2!A2:B500 uden dollartegn og trukket formlen 300 rækker ned. Nu står der Ark2!A302:B800 i den sidste formel.

Sådan retter du: Lås området med dollartegn, Ark2!$A$2:$B$500, eller endnu bedre: lav dine data om til en tabel med Ctrl+T og henvis til tabelnavnet. Så følger området automatisk med, når der kommer nye rækker.

Der er også den klassiske LOPSLAG-regel: opslagsværdien skal stå i den første kolonne i det område, du peger på. Skal du kigge til venstre, kan LOPSLAG det ikke. XOPSLAG er ligeglad med retningen, fordi du giver den to separate områder.

5. Værdien findes faktisk ikke, og det skal regnearket sige pænt

Hvad det betyder: Nogle gange er #I/T ikke en fejl. Det er svaret. Kunden er ny, varenummeret er udgået, rækken hører ikke til i den anden liste.

Typisk årsag: Du sammenligner to lister, der ikke er ens, og det er hele pointen med opgaven.

Sådan retter du: Lad ikke fejlen stå og skræmme. Giv den en tekst, der giver mening for den der læser arket. XOPSLAG har et indbygget argument til det, det fjerde: =XOPSLAG(A2;Ark2!$A$2:$A$500;Ark2!$B$2:$B$500;"Ikke oprettet").

Bruger du LOPSLAG, kan du pakke den ind: =HVISIT(LOPSLAG(A2;Ark2!$A$2:$B$500;2;FALSK);"Ikke oprettet"). HVISIT fanger kun #I/T og lader andre fejl stå, så du ikke kommer til at skjule et ægte problem. Det er forskellen på HVISIT og HVIS.FEJL, som skjuler alt.

Tre greb der finder fejlen hurtigere næste gang

Sammenlign de to celler direkte. Skriv =A2=Ark2!A2 et sted. Får du FALSK, selvom de ser ens ud, ved du at forskellen er usynlig, altså mellemrum eller tekst kontra tal.
Brug SAMMENLIGN til at teste alene. =SAMMENLIGN(A2;Ark2!$A$2:$A$500;0) svarer kun på ét spørgsmål: findes værdien overhovedet i listen? Giver den #I/T, ligger problemet i værdien, ikke i resten af opslaget.
Ryd op i kilden i stedet for i formlen. Skal du hente den samme eksport hver måned, så læg oprydningen i Power Query én gang. Så er mellemrum og teksttal væk, allerede før dataene rammer arket.

Prøv det selv

Jeg har lavet en Excel-fil, hvor alle fem årsager er sat op live, side om side med den rettede version. Åbn den, se #I/T opstå, og prøv at rette hver enkelt. Det tager ti minutter, og du genkender mønsteret igen næste gang et opslag driller.

Hent øvelsesfilen (Excel, xlsx)

Table of contents