Opslag, hvis flere forekomster

Når man bruger funktionen LOPSLAG,kan man, på grundlag af en opslagsværdi, finde indholdet af en celle, i en kolonne i en matrix, hvis opslagsværdien findes i samme række i matrixens første kolonne. Lød det indviklet? Det kom det vist til. Så et eksempel er nok bedre.

Ved hjælp af en formel som fx =LOPSLAG(D1;A1:C5;3;FALSK) kan man så finde ud af, hvad den person, der indtastes i D1, holder af husdyr. Indtastes fx Jan returneres Køer. Ingen ben i det. Jeg har i andre artikler prøvet at vide andre varianter af opslagstemaet, se fx her.

Problemet opstår, hvis opslagsværdien forekommer flere gange, fx

I så fald vil ovenstående formel returnere værdien fra den første forekomst af Jan, altså Køer. Det er imidlertid ikke sikkert, at det er den værdi, vi er interesseret i. Retter jeg formlen til SAND i stedet for FALSK og sorterer området, bliver det ikke bedre af det. I så fald returneres værdien af den sidste forekomst i stedet.

I tilføjelsesprogrammet Jans Udvidelser, som kan hentes her, har jeg en funktion, MULVLOOKUP, som løser problemet. Her kommer koden til denne funktion, om end jeg ændrer dens navn til noget dansklydende og med en smule mere fejlhåndtering.

Function FLOPSLAG(ops As Variant, num As Single, rn As Range, ofs As Byte)
    Dim Taeller As Long
    Dim i As Long
    i = 0
    For Each c In rn.Columns(1).Cells
        If c.Value = ops Then
            i = i + 1
        End If
    Next c
    If num - CInt(num) <> 0 Or num < 1 Then
        FLOPSLAG = CVErr(xlErrNum)
        Exit Function
    End If
    If i < num Then
        FLOPSLAG = CVErr(xlErrNA)
        Exit Function
    End If
    Taeller = 0
    For Each c In rn.Columns(1).Cells
        If c.Value = ops.Value Then
            Taeller = Taeller + 1
            If Taeller = num Then
                FLOPSLAG = c.Offset(0, ofs - 1).Value
                Exit Function
            End If
        End If
    Next c
End Function

Som det kan ses kalder jeg den FLOPSLAG, (af "Flerværdi-LOPSLAG"). Funktionen har denne syntaks: FLOPSLAG(opslagsværdi;rækkefølge;område;kolonne)

Som det fremgår, har den et ekstra argument i forhold til den normale LOPSLAG, nemlig argumentet rækkefølge.

Funktionen har i et ekstra argument i forhold til LOPSLAG, nemlig Rækkefølge. I dette argument skal man indtaste den forekomst, man vil se, hvis der er flere forekomster. =FLOPSLAG(D1;2;A1:C5;3) vil således returnere værdien fra matricens 3. kolonne ud for den anden forekomst af den værdi, der er specificeret i A1. I eksempeltabellen ovenfor returneres Høns.

Findes der ikke så mange identiske værdier (fx er specificeret 4, men der er kun 3 identiske forekomster), returneres #I/T!. Er den ønskede returværdi mindre end 1 eller ikke et helt tal, returneres #VÆRDI!.

 I modsætning til LOPSLAG skelnes der ikke om matricen er sorteret eller ikke sorteret.

- Tilbage til makroer -
- Tilbage til Excel -