Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Verweisfunktionen

Beitrag: SVERWEIS nach Teilstring suchen

Aufgabe
SVERWEIS vergleicht den Suchbegriff immer mit ganzen Zellen (ist zumindest ein weit verbreiteter Irrglaube). Wie kann ich nach einem Begriff suchen, der nur einem beliebigen Teil des Zelleninhaltes entspricht?

Beispiel: Der Begriff "bett" soll gefunden und der entsprechende Wert aus Spalte B ausgegeben werden:
       A               B       
1 Tisch  50  
2 Stuhl  40  
3 Wasserbett  70  
4 Schrank  90  
5 Bettkante  80 

Lösung
Suchspalte: A
Ergebnisspalte: B
Suchbegriff: "Bett"

Groß- und Kleinschreibung wird beachtet:
{=INDEX(B:B;VERGLEICH(FALSCH;ISTFEHLER(FINDEN("Bett";A1:A1000));0))}
kürzer:
{=INDEX(B:B;VERGLEICH(WAHR;FINDEN("Bett";A1:A1000)>0;0))}
ohne Array (Ergänzung vom 26.01.2006):
=INDEX(B:B;VERWEIS(2;1/FINDEN("Bett";A1:A1000);ZEILE(1:1000)))

Groß- und Kleinschreibung wird nicht beachtet: 
{=INDEX(B:B;VERGLEICH(FALSCH;ISTFEHLER(SUCHEN("Bett";A1:A1000));0))}

{=INDEX(B:B;VERGLEICH(WAHR;SUCHEN("Bett";A1:A1000)>0;0))}
dies geht auch einfacher mit:
=INDEX(B:B;VERGLEICH("*Bett*";A:A;0))
oder natürlich auch mit dem SVERWEIS:
=SVERWEIS("*Bett*";A:B;2;0)


Erläuterung
Die VERWEIS-Formel liefert das identische Ergebnis wie die Vorgängerformeln, wenn der Suchbegriff nur einmal vorkommt. Bei mehrfachem Vorkommen liefern die Ursprungsformeln das erste; - die VERWEIS-Formel das letzte.