shaggy84
Goto Top

Funktion WENN mit SUCHE und SVERWEIS kombinieren ?

Hallo allesamt,


ich habe ein Problem mit einer SUCHE Funktion. Ich habe einfach erzählt zwei Tabellenblätter.

In Tabellenblatt 1 gibt es eine erste Spalte A mit Namen, z.B. "Oracle-xyz" oder "test-SQL" oder "maxdbirgendwas".

Daneben eine Spalte B in die die Formel hineinsoll und einen Ausgabewert gibt.

In Tabellenblatt 2 gibt es ebenfalls zwei Spalten. In der ersten A steht "Oracle", "Sql", "Maxdb" und in der Spalte B daneben ein Code der passend in die Spalte B vom ersten Tabellenblatt hineinsoll. Falls er den Wert nicht findet soll er einen bestimmten anderen ausgeben z.B. A23.

Er soll also nach einem Teil des Namens suchen und wenn er den findet, den Wert der danebenliegenden Spalte ausgeben.

Ich habe jetzt schon mehrere möglichkeiten mit SVERWEIS, SUCHE, WENN und ODER durchgegangen, kam aber nie zu dem gewünschten Ergebnis. Manche Werte werden immer #WERT. Natürlich habe ich $ Zeichen gesetzt face-smile

Gruß, Shaggy

Content-Key: 54210

Url: https://administrator.de/contentid/54210

Ausgedruckt am: 28.03.2024 um 08:03 Uhr

Mitglied: bastla
bastla 16.03.2007 um 12:21:32 Uhr
Goto Top
Hallo Shaggy84!

Versuch es mit folgendem VBA-Code (in ein Modul platzieren):
Function TEILVERWEIS(Suchkriterium As Variant, Matrix As Range, Spaltenindex As Integer) As Variant
Dim Ergebnis As Variant, Key As Variant
Dim Z As Integer, S As Integer
Dim i As Integer, found As Boolean
If Spaltenindex > Matrix.Columns.Count Then
    'Spaltennummer außerhalb des übergebenen Bereichs  
    TeilVerweis = 0 / 0
    Exit Function
End If
Application.Volatile
Ergebnis = ""  
found = False
Z = Matrix.Row
S = Matrix.Column
For i = Z To Z + Matrix.Rows.Count - 1
    Key = Matrix.Parent.Cells(i, S).Value
    If LCase(Left(Suchkriterium, Len(Key))) = LCase(Key) Then
        Ergebnis = Matrix.Parent.Cells(i, S + Spaltenindex - 1).Value
        found = True
        Exit For
    End If
Next
If found Then
    TeilVerweis = Ergebnis
Else
    TeilVerweis = 0 / 0
End If
End Function
Die Fehlerbehandlung ist noch verbesserungswürdig, aber im Prinzip sollte es so funktionieren:
=TEILVERWEIS(A3;Tabelle2!$A$2:$B$30;2)
Es wird immer nur überprüft, ob ein Eintrag in der ersten Spalte der Matrix dem Anfang des Suchkriteriums entspricht (Groß-/Kleinschreibung wird nicht beachtet).

Wird ein zu hoher Spaltenindex übergeben (also im Beispiel oben etwa 4 bei nur 2 markierten Spalten), liefert die Funktion den Fehler "#WERT".

Der gleiche Fehler wird (anstelle von "#NV") zurückgegeben, wenn das Suchkriterium nicht gefunden wurde.

Grüße
bastla
Mitglied: Shaggy84
Shaggy84 16.03.2007 um 13:04:22 Uhr
Goto Top
Hallo bastla,

bist ja unermüdlich face-smile

Also dein Code funktioniert soweit sehr gut, aber löst mein Problem leider noch nicht, da das Namensteilstück ja auch mittendrin oder am Ende stehen kann und nicht nur am Anfang.

Außerdem benötige ich noch einen Wert (aus einer Zelle), der Angezeigt wird, wenn er nichts davon finden kann.

Gruß, Gregor
Mitglied: bastla
bastla 16.03.2007 um 13:19:23 Uhr
Goto Top
Hallo Shaggy84!

Stimmt, Du hattest Teil des Namens geschrieben ...

Na gut, dann so:
Function TeilVerweis(Suchkriterium As Variant, Matrix As Range, Spaltenindex As Integer, Alternative As Range) As Variant
Dim Ergebnis As Variant, Key As Variant
Dim Z As Integer, S As Integer
Dim i As Integer, found As Boolean
If Spaltenindex > Matrix.Columns.Count Then
    'Spaltennummer außerhalb des übergebenen Bereichs  
    TeilVerweis = 0 / 0
    Exit Function
End If
Application.Volatile
Ergebnis = ""  
found = False
Z = Matrix.Row
S = Matrix.Column
For i = Z To Z + Matrix.Rows.Count - 1
    Key = Matrix.Parent.Cells(i, S).Value
    If InStr(LCase(Suchkriterium), LCase(Key)) Then
        Ergebnis = Matrix.Parent.Cells(i, S + Spaltenindex - 1).Value
        found = True
        Exit For
    End If
Next
If found Then
    TeilVerweis = Ergebnis
Else
    TeilVerweis = Alternative.Value
End If
End Function
Aufruf jetzt:
=TEILVERWEIS(A3;Tabelle2!$A$2:$B$30;2;$A$23)
also zusätzlich mit Angabe der "Alternativzelle".

Grüße
bastla
Mitglied: Shaggy84
Shaggy84 16.03.2007 um 14:50:45 Uhr
Goto Top
Hey bastla,

das funktioniert perfekt. Auf Leute wie dich ist Verlass face-smile

Vielen Dank!