Excel XOPSLAG-funktion: Alt hvad du behøver at vide

Hvis du har brugt LOPSLAG eller INDEKS/SAMMENLIGN, er jeg sikker på, at du vil elske den fleksibilitet, XOPSLAG-funktionen giver.

I denne artikel dækker jeg alt, hvad der er at vide om XOPSLAG-funktionen og nogle eksempler, der hjælper dig med at forstå, hvordan du bedst bruger den.

Så lad os komme i gang!

Hvad er XOPSLAG?

XOPSLAG er en ny funktion i Office 365 og er en ny og forbedret version af LOPSLAG/VOPSLAG-funktionen.

Den gør alt, hvad LOPSLAG plejede at gøre, og meget mere.

XOPSLAG er en funktion, der giver dig mulighed for hurtigt at lede efter en værdi i et datasæt (lodret eller vandret) og returnere den tilsvarende værdi i en anden række/kolonne.

For eksempel, hvis du har fået karaktererne for elever til en eksamen, kan du bruge XOPSLAG til hurtigt at kontrollere, hvor meget en elev har scoret ved hjælp af elevens navn.

Effekten i denne funktion bliver endnu mere tydelig, når jeg dykker ned i nogle XOPSLAG-eksempler senere i denne artikel.

Men inden jeg kommer ind på eksemplerne, er der et stort spørgsmål – hvordan får jeg adgang til XOPSLAG?

Sådan får du adgang til XOPSLAG?

Fra nu af er XOPSLAG kun tilgængelig for brugerne af Office 365.

Så hvis du bruger tidligere versioner af Excel (2010/2013/2016/2019), kan du ikke bruge denne funktion.

Jeg er heller ikke sikker på, om den nogensinde ville blive frigivet til tidligere versioner eller ej (måske vil Microsoft lave et tilføjelsesprogram, som de gjorde for Power Query). Men fra nu af får du kun adgang, hvis du har Office 365.

XOPSLAG Funktionssyntaks

Nedenfor er syntaksen for XOPSLAG -funktionen:

= XOPSLAG (opslagsværdi; opslagsmatrix; returmatrix; [hvis_ikke_fundet]; [matchtilstand]; [søgetilstand])

Hvis du har brugt LOPSLAG, vil du bemærke, at syntaksen er ret ens, med nogle fantastiske ekstra funktioner naturligvis.

Bare rolig, hvis syntaksen og argumentet ser lidt voldsom ud. Jeg gennemgår disse med nogle lette XOPSLAG-eksempler senere i denne artikel, der gør det krystalklart.

XOPSLAG-funktionen kan indeholde 6 argumenter (3 obligatoriske og 3 valgfrie):

1. opslagsværdi – den værdi, du leder efter
2. opslagsmatrix – arrayet, hvor du leder efter opslagsværdien
3. returmatrix – den matrix, du vil hente og returnere værdien fra (svarende til den position, hvor opslagsværdien findes)
4. [hvis_ikke_fundet] – værdien, der skal returneres, hvis opslagsværdien ikke findes. Hvis du ikke angiver dette argument, returneres en #N/A fejl
5. [matchtilstand] – Her kan du angive den matchtype, du ønsker:
   1. 0 – Præcis match, hvor opslagsværdi nøjagtigt skal matche værdien i opslagsmatrix. Dette er standardindstillingen.
   1. -1 -Kigger efter det nøjagtige match, men hvis det er fundet, returnerer det næst minste element/værdi.
   1. 1 – Ser efter det nøjagtige match, men hvis det er fundet, returnerer det næst største element/værdi
   1. 2 – For at foretage delvis match ved hjælp af jokertegn (* eller ~)
6. [søgetilstand] – Her angiver du, hvordan XOPSLAG-funktionen skal søge i opslagsmatrixen.
   1. 1 – Dette er standardindstillingen, hvor funktionen begynder at lede efter opslagsværdien fra toppen (første element) til bunden (sidste element) i opslagsmatrixen.
   1. -1 – Søgning fra bund til top. Nyttigt, når du vil finde den sidste matchende værdi i opslagsmatrixen.
   1. 2 – Udfører en binær søgning, hvor dataene skal sorteres i stigende rækkefølge. Hvis der ikke sorteres, kan dette give fejl eller forkerte resultater.
   1. -2 -Udfører en binær søgning, hvor dataene skal sorteres i faldende rækkefølge. Hvis det ikke sorteres, kan dette give fejl eller forkerte resultater.

XOPSLAG Funktion Eksempler

Lad os nu komme til den interessante del – nogle praktiske XOPSLAG-eksempler.

Disse eksempler hjælper dig med bedre at forstå, hvordan XOPSLAG fungerer, hvordan den adskiller sig fra LOPSLAG og INDEKS/SAMMENLIGN og nogle forbedringer og begrænsninger af denne funktion.

Klik nedenfor for at downloade eksempelfilen så du kan følge med.

Eksempel 1: Hent en opslagsværdi

Antag, at du har følgende datasæt, og du vil hente danskkarakteren for Hans (opslagsværdien).

Nedenfor er formlen, der gør dette:

=XOPSLAG(F2;A2:A9;B2:B9)

I ovenstående formel har jeg lige brugt de obligatoriske argumenter, hvor der ledes efter navnet (fra top til bund), finder et nøjagtigt match og returnerer den tilsvarende værdi fra B2:B9.

En indlysende forskel mellem XOPSLAG og LOPSLAG-funktionen, er den måde, de håndterer opslagsmatrixen. I LOPSLAG har du hele matrixen, hvor opslagsværdien er i kolonnen længst til venstre, og derefter angiver du kolonnummeret, hvorfra du vil hente resultatet. XOPSLAG giver dig derimod mulighed for at vælge opslagsmatrix og returmatrix separat.

En klar fordel ved at have opslagsmatrix og returmatrix som separate argumenter betyder, at du nu kan se til venstre. LOPSLAG havde denne begrænsning, hvor du kun kan slå op og finde en værdi, der er til højre. Men med XOPSLAG er denne begrænsning væk.

Her er et eksempel. Jeg har det samme datasæt, hvor navnet er til højre og returmatrixen er til venstre.

Nedenfor er formlen, som jeg kan bruge til at få karakteren for Hans i Dansk (hvilket betyder at se til venstre for opslagsværdien).

=XOPSLAG(F2;D2:D9;A2:A9)

XOPSLAG løser et andet stort problem – Hvis du indsætter en ny kolonne eller flytter kolonner rundt, vil de resulterende data stadig være korrekte. LOPSLAG ville sandsynligvis gå i stykker eller give et forkert resultat i sådanne tilfælde, da kolonneindeksværdien oftest er hardkodet.

Eksempel 2: Slå op og hent en hel post

Lad os tage de samme data som et eksempel.

I dette tilfælde vil jeg ikke bare hente Gregs score i matematik. Jeg vil have scoringer i alle fagene.

I dette tilfælde kan jeg bruge nedenstående formel:

=XOPSLAG(F2;A2:A9;B2:D9)

Ovenstående formel bruger et returmatrix-område, der er mere end en kolonne (B2:D9). Så når opslagsværdien findes i A2:A9, returnerer formlen hele rækken fra returmatrixen.

Du kan heller ikke bare slette celler, der er en del af arrayet, der automatisk blev udfyldt. I dette eksempel kan du ikke slette H2 eller I2. Hvis du prøver, ville der ikke ske noget. Hvis du markerer disse celler, vil formlen i formellinjen være nedtonet (hvilket angiver, at den ikke kan ændres).

Du kan slette formlen i celle G2 (hvor vi oprindeligt indtastede den), det vil slette hele resultatet.

Dette er en nyttig forbedring, som tidligere med LOPSLAG, bliver du nødt til at angive kolonnummeret separat for hver formel.

Eksempel 3: Tovejsopslag ved hjælp af XOPSLAG (vandret og lodret opslag)

Nedenfor er et datasæt, hvor jeg vil kende Hans’ karakter i Matematik (elementet i celle G2).

Dette kan gøres ved hjælp af et tovejsopslag, hvor jeg leder efter navnet i kolonne A og elementets navn i række 1. Fordelen ved dette tovejsopslag er, at resultatet er uafhængigt af elevens navn i forhold til elementets navn. Hvis jeg ændrer elementets navn til Biologi, ville denne tovejs XOPSLAG-formel stadig fungere og give mig det korrekte resultat.

Nedenfor er formlen, der udfører tovejsopslag og giver det korrekte resultat:

=XOPSLAG(G1;B1:D1;XOPSLAG(F2;A2:A9;B2:D9))

Denne formel bruger en indlejret XOPSLAG, hvor jeg først bruger den til at hente alle elevens karakter i celle F2.

Så resultatet af XOPSLAG(F2;A2:A9;B2:D9) er {8,59,86}, hvilket i dette tilfælde er en række med karakterer for Hans.

Dette bruges derefter igen inden til den ydre XOPSLAG-formel som returmatrix. I den ydre XOPSLAG-formel leder jeg efter elementets navn (som er i celle G1), og opslags arrayet er B1:D1.

Hvis elementets navn er Matematik, henter denne ydre XOPSLAG-formel den anden værdi fra returmatrixen – hvilket er {8,59,86} i dette eksempel.

Dette er det samme, som indtil nu blev opnået ved hjælp af INDEKS og SAMMENLIGN-kombinationen.

Eksempel 4: Når opslagsværdi ikke findes (fejlhåndtering)

Fejlhåndtering er nu tilføjet til XOPSLAG-formlen.

Det fjerde argument i XOPSLAG -funktionen er [hvis_ikke_fundet], hvor du kan angive, hvad du vil have, hvis opslaget ikke kan findes.

Antag, at du har datasættet som vist herunder, hvor du vil finde matematikkarakteren, hvis der er et match, og hvis navnet ikke findes, vil du vende tilbage – ‘Ikke fundet’.

Nedenstående formel gør dette:

=XOPSLAG(F2;A2:A9;C2:C9;"Ikke fundet")

I dette tilfælde har jeg hardkodet, hvad jeg vil have, hvis der ikke er nogen match. Du kan også bruge en cellereference til en celle eller en formel.

Eksempel 5: Indlejret XOPSLAG (opslag i flere områder)

Det geniale ved at have [hvis_ikke_fundet]-argumentet er, at det giver dig mulighed for at bruge indlejret XOPSLAG-formel.

Antag f.eks., At du har to separate lister som vist nedenfor. Selvom jeg har disse to tabeller på det samme ark, kan du have disse i separate ark eller endda projektmapper.

Nedenfor er den indlejrede XOPSLAG-formel, der kontrollerer navnet i begge tabeller og returnerer den tilsvarende værdi fra den angivne kolonne.

=XOPSLAG(A13;A2:A9;C2:C9;XOPSLAG(A13;F2:F9;H2:H9))

I ovenstående formel har jeg brugt argumentet [hvis_ikke_fundet] til at bruge en anden XOPSLAG-formel. Dette giver dig mulighed for at tilføje den anden XOPSLAG i den samme formel og scanne to tabeller med en enkelt formel.

Jeg er ikke sikker på, hvor mange indlejrede XOPSLAG du kan bruge i en formel. Jeg har hørt om op til 10, og det fungerede!

Eksempel 6: Find den sidste matchet værdi

Denne var hårdt tiltrængt, og XOPSLAG har gjort det muligt. Nu behøver du ikke finde indviklede måder at få den sidste matchende værdi i et område.

Antag, at du har datasættet som vist herunder, og du vil kontrollere, hvornår den sidste person blev ansat i hver afdeling, og hvad var ansættelsesdatoen.

Nedenstående formel søger efter den sidste værdi for hver afdeling og giver navnet på den sidste ansættelse:

=XOPSLAG(F1;$B$2:$B$9;$A$2:$A$9;;;-1)

Og nedenstående formel giver ansættelsesdatoen for den sidste ansættelse for hver afdeling:

=XOPSLAG(F1;$B$2:$B$9;$C$2:$C$9;;;-1)

Da XOPSLAG har en indbygget funktion til at angive opslagets retning (først til sidst eller sidst til først), gøres dette med en simpel formel. Med lodrette data ser LOPSLAG og INDEKS/SAMMENLIGN altid ovenfra og ned, men med XOPSLAG og kan man også angive retningen som bund til top.

Eksempel 7: Omtrentlig match med XOPSLAG (Find provisionssats)

En anden bemærkelsesværdig forbedring med XOPSLAG er, at der nu er fire matchtilstande (LOPSLAG har 2 og SAMMENLIGN har 3).

Du kan angive et af de fire argumenter for at afgøre, hvordan opslagsværdien skal matches:

• 0 – Præcis match, hvor opslagsværdi nøjagtigt skal matche værdien i opslagsmatrix. Dette er standardindstillingen.
• -1 -Kigger efter det nøjagtige match, men hvis det er fundet, returnerer det næst mindste element/værdi.
• 1 – Ser efter det nøjagtige match, men hvis det er fundet, returnerer det næst største element/værdi.
• 2 – For at foretage delvis match ved hjælp af jokertegn (* eller ~)

Men den bedste del er, at du ikke behøver at bekymre dig om, hvorvidt dine data er sorteret i stigende rækkefølge eller faldende rækkefølge. Selvom dataene ikke er sorteret, tager XOPSLAG sig af dem.

Nedenfor har jeg et datasæt, hvor jeg vil finde hver persons provision – og provisionen skal beregnes ved hjælp af tabellen til højre.

Nedenfor er formlen, der gør dette:

=XOPSLAG(B2;$E$2:$E$6;$F$2:$F$6;0;-1)*B2

Dette eksempel bruger værdien salg som opslag og kigger gennem opslagstabellen til højre. I denne formel har jeg brugt -1 som det femte argument ([matchtilstand]), hvilket betyder, at der ledes efter et eksakt match, og når der ikke findes et, returneres værdien mindre end opslagsværdien.

Og som jeg sagde, behøver du ikke bekymre dig om dine data ikke er sorteret.

Eksempel 8: vandret opslag

XOPSLAG kan udføre både lodret og vandret opslag.

Nedenfor har jeg et datasæt, hvor jeg har elevnavne og deres karakterer i rækker, og jeg vil hente karakteren for navnet i celle B7.

Nedenstående formel gør dette:

=XOPSLAG(B7;B1:Q1;B4:Q4)

Dette er intet andet end et simpelt opslag (ligner det, vi så i eksempel 1), men vandret.

Alle de eksempler, som jeg dækker om lodret opslag, kan også udføres med et vandret opslag ved hjælp af XOPSLAG (farvel til LOPSLAG og VOPSLAG).

Eksempel 9: Betinget opslag (Brug af XOPSLAG med andre formler)

Dette er et lidt avanceret eksempel og viser også kraften i XOPSLAG, når du skal lave komplekse opslag.

Nedenfor er et datasæt, hvor jeg har navnene på eleverne og deres karakter, og jeg vil gerne vide navnet på den elev, der har scoret maksimum i hvert fag og antallet af elever, der har scoret mere end 80 i hvert fag.

Nedenfor er formlen, der giver navnet på den elev med de højeste karakterer i hvert emne:

=XOPSLAG(MAKS(XOPSLAG(G1;$B$1:$D$1;$B$2:$D$17));XOPSLAG(G1;$B$1:$D$1;$B$2:$D$17);$A$2:$A$17)

Da XOPSLAG kan bruges til at returnere en hel matrix, har jeg brugt den til først at få alle karakterer for det ønskede fag.

For eksempel for matematik, når jeg bruger XOPSLAG(G1;$B$1:$D$1;$B$2:$D$17), giver det mig alle karaktererne i matematik. Jeg kan derefter bruge MAKS-funktionen til at finde den maksimale karakter i dette område.

Denne maksimale karakter bliver derefter min opslagsværdi, og opslagsområdet ville være matrixen returneret af XOPSLAG(G1;$B$1:$D$1;$B$2:$D$17).

Jeg bruger dette i en anden XOPSLAG-formel til at hente navnet på den elev, der har scoret de maksimale karakterer.

Og for at tælle antallet af elever, der har scoret mere end 80, skal du bruge nedenstående formel:

=TÆL.HVIS(XOPSLAG(G1;$B$1:$D$1;$B$2:$D$17);">80")

Denne bruger XOPSLAG-formlen til at finde området med alle værdierne for det givne emne. Det indelejres derefter i COUNTIF-funktionen for at få det antal karakterer, der er mere end 80.

Eksempel 10: Brug af jokertegn i XOPSLAG

Ligesom du kan bruge jokertegn i LOPSLAG og SAMMENLIGN, kan du også gøre dette med XOPSLAG.

Men der er en forskel.

I XOPSLAG skal du angive, at du bruger jokertegn (i det femte argument). Hvis du ikke angiver dette, giver XOPSLAG dig en fejl.

Nedenfor er et datasæt, hvor jeg har firmanavne og deres markedsværdi.

Jeg vil slå et firmanavn op i kolonne D og hente markedsværdi fra tabellen til venstre. Og da navnene i kolonne D ikke er nøjagtige matches, bliver jeg nødt til at bruge jokertegn.

Nedenfor er formlen, der gør dette:

=XOPSLAG("*"&D2&"*";A2:A7;B2:B7;;2)

I ovenstående formel har jeg brugt stjerne (*) jokertegn før og efter D2 (det skal være inden for dobbelte anførselstegn og være sammenkædet med D2 ved hjælp af ampersand).

Dette fortæller formlen at gennemse alle cellerne, og hvis det indeholder ordet i celle D2 (som er SAS), betragtes det som et nøjagtigt match. Uanset hvor mange og hvilke tegn der er før og efter teksten i celle D2.

Og for at sikre, at XOPSLAG accepterer jokertegn, er det femte argument blevet sat til 2 (jokertegn match).

Eksempel 11: Find den sidste værdi i kolonnen

Da XOPSLAG giver dig mulighed for at søge fra bund til top, kan du let finde den sidste værdi på en liste samt hente den tilsvarende værdi fra en kolonne.

Antag, at du har et datasæt som vist nedenfor, og du vil vide, hvad det sidste firma er, og hvad dette firmas markedsværdi er.

Nedenstående formel giver dig navnet på det sidste firma:

=XOPSLAG("*";A2:A7;A2:A7;;2;-1)

Og nedenstående formel giver værdien for det sidste firma på listen:

=XOPSLAG("*";A2:A7;B2:B7;;2;-1)

Disse formler bruger igen jokertegn. I disse har jeg brugt stjerne (*) som opslagsværdi, hvilket betyder, at dette ville betragte den første celle, den støder på, som en eksakt match (da stjerne kunne være et hvilket som helst tegn og et vilkårligt antal tegn).

Og da retningen er fra bund til top (for de lodret arrangerede data), returnerer den den sidste værdi på listen.

Og den anden formel bruger en separat returmatrix til at få værdien af det sidste navn på listen.

XOPSLAG bagud kompatibilitet

Dette er en ting, du skal være forsigtig med – XOPSLAG er IKKE bagud kompatibel.

Det betyder, at hvis du opretter en fil og bruger XOPSLAG-formlen og derefter åbner den i en version, der ikke har XOPSLAG, viser den fejl.

Så det er 11 XOPSLAG-eksempler, der kan hjælpe dig med at udføre alle opslag og referencer, hurtigere og lettere.

Håber du fandt denne artikel nyttig!

Husk også at læse denne artikel:

Sådan bruges den nye Excel opslagsfunktion XOPSLAG