EXCEL - Bereich eingrenzen

[N_O_K_I_A]

Hi,

ich habe folgendes Problem in Excel:

Ich habe eine sehr große Tabelle.
Ungefähr wie folgt aufgebaut:

1. Spalte sind die angesprochenen Berieche
2. Spalte die Daten, die ich mit dem sverweis suche
3. Spalte die Werte, die ich zurückgeben möchte

a x1 1
a x2 2
a x3 3
b y1 6
b y2 7
b y3 8
c z1 4
c z2 5
c x1 6


Wenn ich nun per sverweis nach der mittleren Spalte suche komme ich zu folgendem Problem:

Ich suche nach "x1" und er gibt mir natürlich eine "1" aus. Wenn ich nun aber zu "x1" die 6 haben möchte, klappt dies ja so nicht.
Die Daten aus der 1. Spalte habe ich aber immer gegeben.
Somit kann ich den Bereich "eingrenzen".
Die Logik ist relativ einfach, nur wie ich das am besten in Excel realisiere, da überlege ich noch...

EDIT:

Der Sverweis soll dann natürlich nur in diesem Bereich suchen

 

[s3bbi]

Wenn ich dich richtig verstehe versuchst du für einen Wert mehrere Ergebnisse zurück zu geben?
Das funktioniert mit Sverweis nicht, bzw. nicht so einfach.

Wenn du so etwas machen möchtest würde ich Access benutzen, die Abfragen in Access können das.

Oder du musst dir alternativ mal die Ergebnisse zu "Sverweis mehrere Ergebnisse" in Google anschauen.

Wie diese hier z.B. http://www.office-loesung.de/ftopic314496_0_0_asc.php

 

[N_O_K_I_A]

Es würde ja auch reichen, wenn es einen Befehl gibt, der in einer großen Tabelle einen Bereich eingrenzt in dem der sverweis nur sucht. Wenn der sverweis nur in diesem Bereich sucht, ist sichergestellt, dass er nur EINEN Wert zurückgeben muss.

 

[s3bbi]

So oder was meinst du?

 

[N_O_K_I_A]

Hi,

ich glaube ich habe es schon hinbekommen.

Ich laufe jetzt die Spalte solange durch, bis ich auf den ersten des gesuchten Wertes treffe.
Die Adresse dieser Zelle speicher ich ab.
Dann diesen gespeicherten Wert als neuen Startwert und dann solange bis der erste Wert kommt, der nicht mehr dem gesuchten ist und diese Adresse dann "-1".
So habe ich genau den Bereich, in dem nur der Wert vorkommt.

Ich hoffe, es ist verständlich



Jetzt tritt allerdings ein weiteres Problem auf..


Nun habe ich genau die Grenzen der Matrix, die ich nur durchsuchen will.

Jetzt wollte ich den sverweis bauen:

a = gesuchter Wert
b1 = Anfang Matrix
c1 = Ende Matrix

=sverweis(a, b:c, 1, Falsch)

Problem dabei ist, b1 und c1 müssen aus Zellen ausgelesen werden.
Wenn ich nun schreibe =sverweis(a, b1:c1, 1,Falsch) dann nimmt er die beiden Zellen B1 und C1 als Matrix und nicht deren Inhalt.
In Excel soll das ja mittel Indirekt() gehen.
Wie aber in VBA.
Wenn ich mit einem Makro aufzeichne, bekomme ich ja nur:

Range("G17").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-15]C[1],C[-5]:C[-4],2,FALSE)"

Wie muss ich hier einsetzen, damit er aus den jeweiligen Zellen den Inhalt als Matrix nimmt?

EDIT:

Wenn die Matrix in G6 steht z.B: A4:C6 dann hab ich es so versucht:

Range("G17").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(R[-15]C[1],Indirect(G6),2,FALSE)"

Allerdings kommt dann immer: #NAME?
Und in der Funktionsleiste steht folgendes: =SVERWEIS(H2;INDIREKT('G6');2;FALSCH)
Die beiden ' ' stören.
Wenn man diese entfernt, läuft es.

ALSO: Wie kann ich machen, dass der Sverweis in VBA seine Parameter nicht fest hat, sondern aus Variablen ausliest?

 

[Mirakel]

Wenn du schon in VBA suchst und es in einen SVERWEIS schreiben willst, ginge es z.B. so (Beispiel anhand s3bbi's Tabelle):

Sub test()
With Range("a:a")
    Set c = .Find("b")
    If Not c Is Nothing Then
        firstAddress = c.Row
        'Debug.Print firstAddress
        Do
            'c.Value = 5
            'Debug.Print c.Row
            lastAddress = c.Row
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Row <> firstAddress
        
    End If
    Debug.Print firstAddress
    Debug.Print lastAddress
Range("g5").FormulaLocal = "=sverweis(h2;b" & firstAddress & ":c" & lastAddress & ";2;FALSCH)"

End With
End Sub

Sicherlich nicht elegant, aber es funktioniert.


Habe gerade auch noch eine Lösung ohne VBA zusammengebaut:
Suchkriterium 1: z.B. c in Zelle "E7"
Suchkriterium 2: z.B. a2 in Zelle "F7"

Formel: =SVERWEIS(F7;INDIREKT("B"&VERGLEICH(1;1/(A1:A100=E7);-1)):INDIREKT("C"&VERGLEICH(1;1/(A1:A100=E7);1));2)

Hinweis: Dies ist eine Matrix-Formel. Diese musst du nicht mit "Enter" sondern mit "Strg + Shift + Enter" bestätigen. Wenn du sie richtig bestätigt hast, erscheinen geschweifte Klammern um die Formel ({})

 

[N_O_K_I_A]

Hi,

vielen Dank für die bisherige Hilfe.

Ein kleines Problem habe ich aktuell noch.

Ich bin auf Tabellenblatt 1. Hier stehen meine Grenzen und hier soll auch das Ergebnis vom Sverweis eingetragen werden.
Die Grenzen muss ich ja mit Indirekt auslesen, wie kann ich dann gleichzeitig, die mit Indirekt ausgelesenen Grenzen auf ein anderes Blatt beziehen?

So leider nicht...

=Sverweis(B1;Tabelle3!(INDIRECT(E1):INDIRECT(E2));3;Falsch)

 

[Mirakel]

Im Prinzip ganz einfach:
Du musst nur das "Tabellexy!" direkt vor die Zellbezüge schreiben:

=SVERWEIS(F7;INDIREKT("Tabelle3!B"&VERGLEICH(1;1/(Tabelle3!A1:A100=E7);-1)):INDIREKT("Tabelle3!C"&VERGLEICH(1;1/(Tabelle3!A1:A100=E7);1));2)

bzw. äquivalent in VBA

"=sverweis(h2;Tabelle3!b" & firstAddress & ":c" & lastAddress & ";2;FALSCH)"

Wobei ich mir bei der Benennung in VBA jetzt nicht sicher bin, ob das so funktioniert. An diese Stelle gehört es jedenfalls.