NOTEDenne artikel er opdateret medio december 2019 med det nye fjerde argument, der bruges til at vise en fejlmeddelelse, hvis der ikke findes et match (se eksempel 5).

Excel har (endelig) fået en ny opslagsfunktion, XOPSLAG [XLOOKUP] til afløsning af LOPSLAG [VLOOKUP] eller kombinationen af INDEKS-SAMMENLIGN [INDEX-MATCH].

Den er designet med henblik på at returnere en relateret værdi på samme måde som LOPSLAG [VLOOKUP], men har ikke de samme begrænsninger og giver nogle flere muligheder:

=XOPSLAG(Opslagsværdi; Opslagsmatrix; Returmatrix; [Hvis_ikke_fundet]; [Matchtilstand]; [Søgetilstand])

Opslagsværdi er den værdi, som du ønsker at søge efter.

Opslagsmatrix repræsenterer den matrix, som du ønsker at søge efter opslagsværdien i, og er helt forskellig fra LOPSLAG [VLOOKUP]. Du kan nøjes med at angive en enkelt kolonne (med søgeværdierne i), og den kan både være til venstre for, i midten af eller til højre for de data, som du ønsker at returnere.

Returmatrix er også helt forskellig fra LOPSLAG [VLOOKUP], hvor du i stedet for at definere en returkolonne ved hjælp af et tal (kolonneindeksnr.), nu definerer en returmatrix ved hjælp af celle- og områdereferencer, og ja – du kan angive et område, der dækker flere kolonner, og returnere fx både for- og efternavn fra to forskellige kolonner.

Hvis_ikke_fundet er er et valgfrit argument, der returneres, hvis der ikke findes et gyldigt match – hvis der ikke findes et gyldigt match, og dette argument ikke er angivet, så returneres #I/T

Matchtilstand er et valgfrit argument, der som standard er 0, hvis det udelades, hvilket betyder, at funktionen som standard vil søge efter et eksakt match (i modsætning til LOPSLAG [VLOOKUP]).

Søgetilstand er et valgfrit argument, der som standard er 1, hvis det udelades, hvilket betyder, at funktionen søger efter et match oppefra og ned i opslagsmatrixen (på samme måde som LOPSLAG [VLOOKUP].

NOTEXOPSLAG [XLOOKUP] er i øjeblikket en betafunktion, og den er kun tilgængelig for en del af Office insiders på nuværende tidspunkt.

Udover XOPSLAG [XLOOKUP] kommer også funktionen XSAMMENLIGN [XMATCH], der afløser den gamle SAMMENLIGN-funktion [MATCH].

Nogle få eksempler

Eksempel 1

I ovenstående eksempel bruger jeg XOPSLAG til at finde landekoden ud fra forkortelsen i F3, ganske som du kender det fra LOPSLAG, så her er der egentlig ikke noget nyt under solen – bortset fra, at XOPSLAG som standard leder efter et nøjagtigt match, hvorfor der ikke er nogen grund til at angive mere end de tre første argumenter.

Eksempel 2

I ovenstående eksempel ser du samme eksempel i G3, hvor jeg her bruger en Excel-tabel til landekoderne.

Eksempel 3

I dette eksempel bruger jeg XOPSLAG’s evne til at returnere flere værdier, lidt på samme måde som de nye dynamiske matrixfunktioner, og har indtastet XOPSLAG-funktionen i celle G6, men hvor jeg har angivet en returmatrix med to kolonner. XOPSLAG returnerer automatisk to værdier, som så indsættes i G6 og løber over i H6 (eller flyder ind i), hvilket du kan se af den tynde blå markering af G6:H6 og den tilhørende skyggeeffekt.

I illustrationen herover har jeg markeret H6, hvor du kan se funktionen i G6’s overløb (vist med grå skrift i formellinjen).

Eksempel 4

I dette eksempel ser du, hvordan XOPSLAG i modsætning til LOPSLAG kan returnere en værdi til venstre for opslagskolonnen – jeg leder efter forkortelsen i kolonnen med forkortelser (søgematrixen), og returnerer navnet fra kolonnen med landenavne (returmatrixen).

Eksempel 5

I G12 bruger jeg XOPSLAG til at lede efter landekoden SP, men som du kan se, så findes den (Spanien) ikke i landekodetabellen, hvorfor vi får den nok så kendte #I/T-fejl. I G15 bruger jeg derfor det fjerde argument til at vise en lidt bedre fejlmeddelelse i tilfælde af, at der ikke findes et match.

Eksempel 6

I dette eksempel bruger jeg matchtilstanden -1, der returnerer et præcist match eller næste mindre element, svarende til værdien TRUE i forbindelse med LOPSLAG’s argument Intervalopslag – sælger man for kr. 900 , så får man 2% i rabat, og man skal sælge for mindst kr. 1.000 for at få 5% i rabat.

I det nederste eksempel bruges matchtilstanden 1, der returnerer et præcist match eller næste større element, så hvis man har brug for et gennemløb på mindst 25, så skal man jo have et rør, der mindst er lig med den søgte værdi eller større.

NOTEJeg har i begge tilfælde også angivet søgetilstanden 1, der søger fra første til sidste værdi, men det havde ikke været nødvendigt.

Eksempel 7

I dette sidste eksempel bruger jeg et dobbelt XOPSLAG, idet det yderste XOPSLAG leder lodret efter kontoen fra F2 (navngivet Konto) i B6:B16, og det inderste XOPSLAG leder vandret efter årstallet fra E3 (navngivet År) i C5:F5, og da XOPSLAG i bund og grund returnerer indeksværdierne, returneres den værdi, hvor de to XOPSLAG krydser (X’er), hvilket jo er dækningsbidraget på 250.000 fra 2016.

For at gøre det lidt mere effektivt, så har jeg gjort følgende:

• Jeg har snydt lidt med 2019 i F5, hvor jeg har oprettet et brugerdefineret talformat, der viser teksten “ÅTD” efter årstallet – cellen indeholder altså kun selve årstallet.
• Jeg har brugt en dynamisk matrixfunktion i H6 til at sortere teksterne fra B6:B16 alfabetisk – med overløb til H7:H16.
• Jeg har oprettet en datavalidering i F2, der viser den sorterede liste fra H2 med overløb (det er #-symbolet, der angiver dette).

NOTEJeg skriver pt. på en større artikel om brug af de nye dynamiske matrixfunktioner, så kom snart igen.