Opslag med flere kriterier: når én nøgle ikke er nok

Du skal bruge budgettet for Salg i marts. Budgetarket har én række pr. afdeling pr. måned, så Salg står der seks gange. Du skriver et opslag på "Salg", og Excel svarer prompte med et tal.

Det er bare januars tal. Der står ingen fejl, formlen ser rigtig ud, og tallet ryger videre ind i rapporten.

Problemet er, at et opslag kun kan holde én nøgle i hånden ad gangen, og her er det kombinationen af afdeling og måned, der gør rækken entydig. I det her indlæg får du tre måder at slå op på to eller flere kolonner på én gang, og hvornår du bruger hvilken. Regn med et kvarter.

Hvorfor det almindelige opslag giver det forkerte tal

XOPSLAG (engelsk: XLOOKUP) og LOPSLAG (engelsk: VLOOKUP) leder oppefra og stopper ved det første match. Med data som det her:

Afdeling	Måned	Beløb
Salg	Januar	410.000
Salg	Februar	395.000
Salg	Marts	450.000
Indkøb	Januar	120.000

finder =XOPSLAG("Salg";A2:A25;C2:C25) den første række med Salg og henter 410.000. Den kigger slet ikke på måneden.

Det farlige er, at der ikke kommer nogen fejl. Et #I/T ville du opdage. Et forkert, men troværdigt tal opdager du først, når nogen spørger, hvorfor marts ser så lav ud.

I eksemplerne nedenfor står afdelingerne i A2:A25, månederne i B2:B25 og budgettet i C2:C25. Den afdeling, du søger, står i G2, og måneden i G3.

1. Gang betingelserne sammen i XOPSLAG

Det er den metode, jeg vil anbefale, hvis du sidder i Excel 2021, 2024 eller Microsoft 365:

=XOPSLAG(1;(A2:A25=G2)*(B2:B25=G3);C2:C25;"Ikke fundet")

Det ser mærkeligt ud første gang, så her er, hvad der sker.

Hvad betyder det: (A2:A25=G2) tjekker hver række og giver en liste med SAND og FALSK: SAND der, hvor afdelingen er Salg. (B2:B25=G3) gør det samme for måneden. Når du ganger de to lister med hinanden, bliver SAND til 1 og FALSK til 0, og kun de rækker, hvor begge dele passer, ender med 1.

Derfor søger du efter 1: XOPSLAG leder efter det første 1-tal i den nye liste, og det er netop rækken med Salg og Marts. Den henter så budgettet fra samme række.

Flere kriterier: Du ganger bare en parentes mere på. Skal sælgeren også passe, bliver det (A2:A25=G2)*(B2:B25=G3)*(D2:D25=G4).

Metoden kan mere end lighedstegn. Vil du have den første ordre fra Aarhus over 5.000 kr., skriver du (A2:A25="Aarhus")*(C2:C25>5000). Det er den store fordel frem for de to næste metoder.

2. Sæt nøglerne sammen med &

Den anden metode limer kriterierne sammen til én tekst og slår op på den:

=XOPSLAG(G2&"|"&G3;A2:A25&"|"&B2:B25;C2:C25;"Ikke fundet")

Hvorfor den lodrette streg: Uden et skilletegn kan to forskellige kombinationer ende som samme tekst. Varenummer 12 med størrelse 3 og varenummer 1 med størrelse 23 bliver begge til "123". Med en streg imellem står der "12|3" og "1|23", og så kan de ikke forveksles.

Metoden er nem at læse, men den kan kun finde præcise match. Skal du bruge større end eller mindre end, er metode 1 vejen.

3. INDEKS og SAMMENLIGN, når en kollega sidder i en ældre version

XOPSLAG findes ikke i Excel 2016 og 2019. Skal filen virke for en kollega med en af dem, bruger du INDEKS (engelsk: INDEX) og SAMMENLIGN (engelsk: MATCH) med samme gangetrick:

=INDEKS(C2:C25;SAMMENLIGN(1;(A2:A25=G2)*(B2:B25=G3);0))

Hvad betyder det: SAMMENLIGN finder positionen på det første 1-tal, altså rækkenummeret i tabellen. INDEKS henter så værdien på den position i budgetkolonnen. Nullet til sidst betyder præcist match.

Typisk faldgrube: I Excel 2019 og ældre skal formlen afsluttes med Ctrl+Skift+Enter i stedet for bare Enter, ellers giver den #I/T eller et forkert tal. Du kan se, at det er lykkedes, når Excel selv sætter krøllede parenteser om formlen. I Microsoft 365 er Enter nok.

Den helt enkle udvej: Lav en hjælpekolonne i tabellen med =A2&"|"&B2, fyld den ned, og slå op i den med et almindeligt opslag. Det er ikke elegant, men det virker i alle versioner, og alle kan se, hvad der sker.

Når flere rækker passer

Alle tre metoder giver det første match. Står Salg og Marts der to gange, fordi nogen har tastet en rettelse ind som en ny række, får du den øverste og hører ikke mere om det.

Vil du have den nyeste: Sæt søgetilstand til -1, så leder XOPSLAG nedefra: =XOPSLAG(1;(A2:A25=G2)*(B2:B25=G3);C2:C25;"Ikke fundet";0;-1)

Vil du have dem alle: Så er det ikke et opslag, du skal bruge, men FILTRER. Den har jeg gennemgået i indlægget om FILTRER og ENTYDIGE.

Er det et tal, du henter: Så kan SUM.HVISER også gøre det, med afdeling og måned som to betingelser. Men husk, at den lægger dubletter sammen i stedet for at vælge én, så et dobbelt budget bliver til et dobbelt så stort tal. Mere om den i indlægget om SUM.HVISER og TÆL.HVISER.

De fejl, du typisk møder

#I/T, selvom rækken står der: Næsten altid et mellemrum for meget eller et tal gemt som tekst i en af kolonnerne. Det gælder for hvert kriterie, så ét forkert mellemrum i måneden er nok. Se indlægget om opslag der giver #I/T.

#VÆRDI! (engelsk: #VALUE!): Områderne er ikke lige lange. Har du A2:A25 i det ene kriterie og B2:B30 i det andet, kan Excel ikke parre rækkerne. Tjek at alle områder starter og slutter i samme række.

Det rigtige tal i én celle, forkerte tal når du trækker ned: Du mangler dollartegn på områderne. Skriv $A$2:$A$25 og så videre, så tabellen ikke rykker med, når formlen kopieres.

Genveje der gør det hurtigere næste gang

Formater som tabel (Ctrl+T). Så kan du skrive =XOPSLAG(1;(Budget[Afdeling]=G2)*(Budget[Måned]=G3);Budget[Beløb]). Formlen læses næsten som en sætning, og nye rækker kommer automatisk med.

F4 for dollartegn. Stil markøren i et område i formlen og tryk F4, så sætter Excel dollartegnene for dig.

Valgliste til kriterierne. Lav G2 og G3 om til rullelister med Data, Datavalidering. Så kan ingen taste "marts " med et mellemrum til sidst.

F9 for at se, hvad der foregår. Markér (A2:A25=G2) inde i formellinjen og tryk F9. Så viser Excel listen med SAND og FALSK. Tryk Esc bagefter, så formlen ikke bliver ændret.

Prøv det selv

Jeg har lavet en øvelsesfil med et budgetark, hvor Salg, Indkøb, Lager og Administration står for hver måned i første halvår. Det almindelige opslag står der allerede og henter det forkerte tal, så du kan se problemet med egne øjne. Bagefter skriver du selv de tre metoder, og der er et løsningsark med det hele udfyldt og de danske formler skrevet ud.

Hent øvelsesfilen (Excel, xlsx)

Til sidst

Opslag på flere kriterier er et af de steder, hvor Excel ikke advarer dig. Formlen virker, tallet ser fornuftigt ud, og fejlen viser sig først i mødet. Gangetricket i XOPSLAG løser det med én formel, og du kan genbruge det i alle dine opslag fremover.

Det er den slags, KontorAkademiet handler om: en times praktisk læring om måneden, så du og dine kollegaer bliver lidt bedre til det, der fylder i hverdagen. Ikke i et bestemt program, men i de opgaver, der går igen.

Table of contents