Tipps und Tricks rund um Excel

A	B	C	D	E	F	G	H	I/J	K
L	M	N	O	P/Q	R	S	T	U-W	X-Z

 

SVERWEIS exakter Wert

Im ersten Beispiel haben wir Excel veranlasst, in einer sortierten Verweistabelle Werte zwischen zwei Angaben zu suchen. Gesucht wurde immer der nächstniedrigere Wert. Das geht nicht, wenn genau nach einer eingegebenen Ziffer gesucht werden soll: eine Artikelnummer, Personalnummer oder Kundennummer.

In der ersten Spalte der Verweistabelle stehen die Personalnummern. Jede Nummer kommt genau einmal vor und ist exakt einem Mitarbeiter zugeordnet. Die Nummern können sortiert sein, müssen es aber nicht. In meinem Fall sind die Nachnamen sortiert, nicht die Personalnummern.

Wenn eine Nummer nicht gefunden wird, darf unter keinen Umständen die nächstliegende verwendet werden, sondern es muss ein Fehler ausgegeben werden. Beispielsweise existiert keine Personalnummer 114, dann dürfen nicht einfach die Daten des Mitarbeiters 113 ausgegeben werden!

In A2 wird die Personalnummer eingetippt, Excel soll den dazugehörigen Namen finden.

In meinem Beispiel befindet sich die Personaltabelle nicht auf dem gleichen Blatt, sondern auf einem separaten Blatt. Die gesamte Personaltabelle ist eine formatierte Tabelle und wurde als "Stammdaten" bezeichnet.

Wenn Sie Ihre Tabellen nicht benennen, müssen Sie den Bereich markieren - in meinem Beispiel wäre das A2:P148.

Die Funktion sieht so aus:

=SVERWEIS(Suchkriterium;Matrix;Spaltennr.;Bereich_Verweis)

=SVERWEIS($A2;Stammdaten;2;FALSCH)

Der absolute Bezug für $A2 sorgt dafür, dass ich die Formel gleich nach rechts kopieren kann. Wenn Sie die Formel eintippen und das Semikolon hinter die Spaltennummer setzen, sehen Sie im Vorschlagfenster die beiden Argumente für Bereich_Verweis.

Bewegen Sie sich auf FALSCH und drücken Sie die TAB-Taste oder tippen Sie FALSCH in die Formel.

Sie lesen die Formel so:

• Suche nach dem Suchkriterium aus der Zelle A2 (das ist die Personalnummer)
• in der ersten Spalte der Matrix "Stammdaten", bis dieser Wert gefunden wurde
• und gib dann von der zweiten Spalte den Wert aus (das ist der Nachname)
• aber nur, wenn der exakte Wert gefunden wurde (das Argument FALSCH)

Ziehen Sie die Formel nach rechts in C2 und D2. Passen Sie dann die SVERWEIS-Spalten an! Für Vorname und Abteilung müssen die SVERWEIS-Formeln so aussehen:

• Vorname (Spalte 3 der Matrix)
  =SVERWEIS($A2;Stammdaten;3;FALSCH)
• Abteilung (Spalte 4 der Matrix)
  =SVERWEIS($A2;Stammdaten;4;FALSCH)
  

Tragen Sie eine Personalnr. ein und bestätigen Sie mit ENTER.
Tippen Sie eine nicht vorhandene Personalnummer ein, gibt Excel den #NV als Fehlerwert aus.

 

19.09.2013