SVerweis / Index ungefähre Übereinstimmung

Forumthread: SVerweis / Index ungefähre Übereinstimmung

13.07.2017 09:49:19

Thomas

Hallo zusammen,
ich hätte folgendes Problem:
eine Liste von Orten in meiner Liste A und ebenfalls diese Orte in Liste B
Mein Problem ist, das beide Listen aus unterschiedlichen Datenbasen kommen. Daher habe ich in Liste A Orte die als Suchkriterien gelten, z.T. Ortschaften + Ortsteile stehen die dann in Liste B gefunden werden sollen und den zugehörigen Wert in der nebenstehenden Zelle wiedergeben soll...
Mein Problem ist jetzt als...Der Ort nach dem ich Enthält z.B. Berlin-Tempelhof in Liste B steht aber nur Berlin....
ich hätte es mit einem Sverweis versucht, funktioniert aber nicht, da ich das Suchkriterium im Sverweis ja nur mit einem Stern davor und dahinter verlängern kann...aber eigentlich müsste das Kriterium ja gefunden werden wenn es z.B. auch nur Berlin enthält... :-(
Bsp. hier:
Liste A: A1 - Berlin-Tempelhof B1= soll Wert sein der in Liste B / B1 steht
Liste B: A1 - Berlin; B1= Wert der in Liste A / B1 wiedergegeben werden soll.
Jemand eine Idee? :-/ Die Orte auf ein gleiches Format zu bringen ist leider auf Grund der Menge an Datensätzen nicht machbar :-(
Danke schon mal und freundliche Grüße
Thomas

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

AW: SVerweis / Index ungefähre Übereinstimmung

13.07.2017 10:37:11

yummi
AW: SVerweis / Index ungefähre Übereinstimmung

13.07.2017 13:26:49

Daniel

AW: SVerweis / Index ungefähre Übereinstimmung

13.07.2017 10:37:11

yummi

Hallo Thomas,
schau dir mal das an:
http://www.excelformeln.de/formeln.html?welcher=37
Gruß
yummi

AW: SVerweis / Index ungefähre Übereinstimmung

13.07.2017 13:26:49

Daniel

Hi
im Prinzip mit dieser Formel:

=INDEX('Liste B'!B:B;KGRÖSSTE(WENN('Liste B'!A2:A10"";WENN(ISTZAHL(FINDEN('Liste B'!$A$1:$A$99;A2)) ;ZEILE('Liste B'!$A$1:$A$99)));1))

die Formel muss als Matrixformel eingegeben werden, dh. du musst die Eingabe immer mit STRG+SHIFT+ENTER abschließen.
der Zellbereich auf Liste B in Spalte A muss so gut wie möglich an die vorhandenen Daten angepasst werden (ein Bezug auf ganze Spalte ist nicht möglich).
damit auch bei solchen Kombinationen wie "Hof" und "Hoffenheim" korrekte Werte ausgegeben werden, muss die Liste aufsteigend sortiert sein.
wenns wirklich immer so ist, dass in Liste A "Ort-Ortsteil" steht und in Liste B nur "Ort", dann könntest du auch den Ortsteil abtrennen und dann normal mit dem SVerweis suchen:

=SVERWEIS(LINKS(A1;FINDEN("-";A1&"-")-1);'Liste B'!A:B;2;0)

Gruß Daniel

Infobox / Tutorial

SVerweis und Index für ungefähre Übereinstimmung in Excel nutzen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um in Excel eine ungefähre Übereinstimmung mit SVerweis oder Index zu erreichen, kannst Du die folgenden Schritte befolgen:

Daten vorbereiten: Stelle sicher, dass Deine Listen (Liste A und Liste B) in einem Excel-Arbeitsblatt vorliegen. Liste A enthält die Orte, nach denen Du suchst, und Liste B die Orte, die Du finden möchtest.
Formel für SVerweis: Wenn Du nur einen Teil des Ortsnamens in Liste A hast, versuche die folgende Formel:
```
=SVERWEIS("*" & A1 & "*"; 'Liste B'!A:B; 2; FALSCH)
```
Diese Formel sucht nach dem Text in A1 innerhalb der Daten in Liste B.
Alternativ mit Index: Wenn Du eine genauere Übereinstimmung benötigst, kannst Du die INDEX- und VERGLEICH-Funktion verwenden. Nutze die folgende Matrixformel:
```
=INDEX('Liste B'!B:B; KGRÖSSTE(WENN(ISTZAHL(FINDEN('Liste B'!$A$1:$A$99; A2)); ZEILE('Liste B'!$A$1:$A$99)); 1))
```
Vergiss nicht, die Formel mit STRG+SHIFT+ENTER einzugeben, um sie als Matrixformel zu aktivieren.
Daten anpassen: Achte darauf, dass der Zellbereich auf Liste B so gut wie möglich an die vorhandenen Daten angepasst ist. Ein Bezug auf ganze Spalten kann zu Fehlern führen.
Ergebnisse überprüfen: Überprüfe die Ergebnisse, um sicherzustellen, dass die ungefähre Übereinstimmung korrekt funktioniert.

Häufige Fehler und Lösungen

Fehler: #NV
Lösung: Stelle sicher, dass die Suchkriterien korrekt eingegeben sind und dass die Listen in den richtigen Zellbereichen liegen.
Fehler bei der Matrixformel
Lösung: Denke daran, die Matrixformel mit STRG+SHIFT+ENTER abzuschließen. Ansonsten funktioniert die Formel nicht richtig.
Falsche Ergebnisse bei SVerweis
Lösung: Überprüfe, ob die Daten in Liste B aufsteigend sortiert sind, insbesondere wenn Du die INDEX- und VERGLEICH-Funktion verwendest.

Alternative Methoden

Wenn die oben genannten Methoden nicht den gewünschten Erfolg bringen, kannst Du folgende Alternativen ausprobieren:

Text-Funktionen verwenden: Teile den Ortsteil ab, wenn er in Liste A vorhanden ist, und benutze die SVerweis-Funktion mit dem abgeschnittenen Text.
```
=SVERWEIS(LINKS(A1; FINDEN("-"; A1 & "-") - 1); 'Liste B'!A:B; 2; FALSCH)
```
Power Query: Nutze Power Query, um die Daten zu transformieren und eine ungefähre Übereinstimmung zu erzeugen. Diese Methode ist besonders nützlich bei großen Datensätzen.

Praktische Beispiele

Beispiel 1: Liste A enthält „Berlin-Tempelhof“, und Liste B enthält nur „Berlin“. Mit der Formel:
```
=SVERWEIS("*Berlin*"; 'Liste B'!A:B; 2; FALSCH)
```
erhältst Du den Wert, der zu „Berlin“ gehört.
Beispiel 2: Wenn Du eine Liste von Städten hast und Du nur den Hauptort benötigst, kannst Du die INDEX- und VERGLEICH-Kombination verwenden, um den passenden Wert zu finden.

Tipps für Profis

Daten bereinigen: Bereinige Deine Daten vor der Anwendung von Formeln. Entferne überflüssige Leerzeichen und konsolidiere die Formate.
Fehlerprüfung aktivieren: Aktiviere die Fehlerüberprüfung in Excel, um mögliche Probleme frühzeitig zu erkennen.
Verwendung von benannten Bereichen: Nutze benannte Bereiche für Deine Listen, um die Formeln übersichtlicher zu gestalten.

FAQ: Häufige Fragen

1. Was ist der Unterschied zwischen SVerweis und INDEX?
SVerweis sucht einen Wert in der ersten Spalte einer Tabelle und gibt einen Wert aus einer anderen Spalte zurück. INDEX hingegen gibt einen Wert an einem bestimmten Ort in einer Tabelle zurück.

2. Kann ich SVerweis auch für Textwerte verwenden?
Ja, Du kannst SVerweis auch für Textwerte nutzen, solange Du die Formel entsprechend anpasst, um eine ungefähre Übereinstimmung zu ermöglichen.

3. Wie gehe ich mit großen Datenmengen um?
Verwende Power Query oder teile die Daten in kleinere Segmente auf, um die Verarbeitung zu erleichtern.

Beliebteste Forumthreads (12 Monate)

SVerweis / Index ungefähre Übereinstimmung

Forumthread: SVerweis / Index ungefähre Übereinstimmung

2 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

SVerweis und Index für ungefähre Übereinstimmung in Excel nutzen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

2
Beiträge zum Forumthread

Beiträge zu diesem Forumthread