Frage von MurkelB,

Excel 2003 Zahl aus Liste suchen und nebenstehenden Namen ausgeben

Hallo!

Ich habe eine Excel-Liste mit Postleitzahlen und nebenstehenden Namen. Es geht um Zuständigkeiten in einem Bezirk. Z.B.

27700 - 27729 Max Mustermann

Nun möchte ich ein Feld in dieser Tabelle haben in der ich eine 5-Stellige PLZ eingeben kann und mir automatisch daneben der Name ausgeworfen wird.

Kann mir jemand helfen?

Danke im Voraus!

Hilfreichste Antwort von thommi12,

Mit der Funktion SVERWEIS, vgl. in Excel-Hilfe. Der letzte Parameter muss WAHR sein, damit auch ein Ergebnis bei nicht exakter Übereinstimmung gefunden wird. Die erste Spalte enthält schön sortiert (wichtig) aufsteigend die Postleitzahlen-Grenzen der Bezirke.

Hier ein Beispiel (die Zelle A1 enthält hier den Text 'PLZ') mit den Zellinhalten A1 bis D8, wobei in Zelle c1 der Eingabewert beispielsweise 27705 ist, und als Ergebnis 'Max Mustermann' aus Zelle b3 in Zelle D1 angezeigt wird:

1: PLZ ORT 27705 Max Mustermann

2: 22222 b
3: 27700 Max Mustermann
4: 27730 Nächster
5: 33333 c
6: 44444 keiner
7: 55555 e
8: 66666 d

Die verwendete Formel in Zelle D1 ist:

=SVERWEIS($C$1;A2:B8;2;WAHR)

Dabei:

$C$1 definiert das Eingabefeld (hier mal als absoluter Feldbezug mit den $-Zeichen),

A2:B8 ist die Tabelle (Matrix), deren erste Spalte durchsucht wird,

2 gibt an, dass der korrepondierende Zellinhalt der zweiten Spalte der Matirx zurückgegeben wird.

WAHR gibt an, dass immer der Wert zurückgegeben wird, der kleiner oder gleich dem Suchriterium ist. Dies erfordert eine streng aufsteigende Sortierung der ersten Spalte!

So wird im Beispiel von 27700 bis einschließlich 27729 der Wert Max Mustermann zurückgeliefert.

Kommentar von thommi12,

OK, die Spaltenüberschrift 'Ort' muss natürlich 'Name' heißen.

Der Vollständigkeit halber:

Du kannst statt mit WAHR als letzten Parameter natürlich auch mit dem Parameter FALSCH in der SVERWEIS-Funktion arbeiten, um nur nach einer exakten Übereinstimmung zu suchen., also:

=SVERWEIS($C$1;A2:B8;2;FALSCH)

Dann muss aber für jede Postleitzahl eine eigene Zeile existieren mit dem Namen. Dafür ist dann die Sortierung egal. Wird der Wert nicht gefunden, oder ist das Namensfeld leer, dann wird ein Fehlerwert ausgegeben.

Antwort von MrProcess,

Würde ich mit Vergleich() und Index() lösen, weil der Verweis für was anderes gedacht ist (Bereichszuordnung, nicht exakter Match).

Angenommen A1:A10 enthält Postleitzahlen B1:B10 enthält Ortsnamen in C1 steht eine zu suchende PLZ In D1 dann eine Formel wie

=vergleich(c1 ; $a$1:A4$10 ; 0) In Worten: suche nach der PLZ die in C1 steht, durchsuche den Bereich von a1 bis a10. Das letzte 0 bedeutet suche exakt. Es kommt ein Fehlerwert raus, wenn die PLZ nicht vorhanden wäre.

Als Ergebnis kommt da ein Indexwert raus. Sagen wir 2 wenn die gesuchte PLZ in a2 stünde. Oder 5 wenn die PLZ in a5 wäre.

Eine weitere Formel mit Index holt den passenden Text/Ort, etc. raus =index($b$1: 4b;$10; bezugaufvorherigezelle ; 1)

Beispiel ist mal absichtlich einfach gehalten um das Prinzip nachvollziehbar zu machen.

Kommentar von MurkelB,

Ich muss gestehen, dass ich da nicht ganz mitkomme. Mit kopieren und einfügen der Formel, wie sie dort steht klappt es jedenfalls nicht. Oder ich mache was anderer verkehrt. Ich weiß auch nicht, ob ich das mit den Postleitzahlen so richtig beschrieben habe. Es ist immer ein Postleitzahlenbereich gemeint. Mitarbeiter (Name) bearbeitet PLZ-Bereich 01000 - 01090... Z.B. in Spalte A "01000" Spalte B "01090" Spalte C "Name" (Eventuell Spalte D " Gebietsname)

Dann möchte ich ein Abfragefeld in das ich die Postleitzahl eintrage.

In dem Nebenstehenden Feld dann das Ergebnis.

Excel soll also vergleichen... größer Spalte A und kleiner als Spalte B und dann den Nebenstehenden Wert ausgeben.

Wäre dankbar für weitere Hilfe!

Kommentar von MrProcess,

Aha, dann haben wir also doch eine Bereichssuche. Nun da passt die Verweis-Funktion. Allerdings muss der Suchbereich anders aufgebaut werden. Und er muss nummerisch sein. Angenommen 01000 bis 01090 ist Berlin und 1091 wäre "Berlin 2" (weil mir nix besseres einfällt), dann müsste die Tabelle etwa so aufgebaut sein

a1: 1000  |  Berlin
a2: 1091  |  Berlin 2
a3: 1500  |  woanders

(wichtig sind hier die Grenzwerte. Also 1090 als Grenzwert taucht nicht auf. Erst der nächstfolgende, also in dem Beispiel 1091)

und die Formel lautet dann =verweis(suchkriterium; suchvektor; ergebnisvektor). Oder konkret

=verweis(1050; a1:a3; b1:b3)

Stünde in Spalte C ein Bereichsname kriege ich den mit der Formel raus.

=verweis(1050; a1:a3; c1:c3)

Natürlich ist die 1050 als Suchkriterium nur ein Beispiel. Hier den Bezug auf das Abfragefeld eintragen. Genauso wird man in der Praxis absolute Bezüge für die Bereiche brauchen damit sich die Formel kopieren lässt.

=verweis(1050; $a$1:$a$3; $c$1:$c$3)
Kommentar von MurkelB,

Vielen Dank für die Mühe, aber ich kann den SVerweis einfacher nachvollziehen. Funktioniert in meiner Tabelle. Wahnsinn was mit Excel alles geht.

Danke nochmal!

Keine passende Antwort gefunden?

Fragen Sie die Community