-->Excel Wert ausgeben wenn SVerweis (vlookup) nichts findet über IST-Funktionen | PC-Notfallhilfe

PC-Notfallhilfe

Tipps und Tricks aus der IT Welt

Excel Wert ausgeben wenn SVerweis (vlookup) nichts findet über IST-Funktionen

| 26 Kommentare

In verschiedenen Beiträgen habe ich mich generell dem SVerweis (vlookup) gewidmet. Die meisten Beispiele sind dann natürlich so konstruiert, das man etwas mit der SVerweis-Funktion findet.

Aber was tun, wenn man Fehlermeldungen erhält, wie #NV, weil das Suchkriterium nicht in der anderen Tabelle/Matrix vorkommt?

2014-10-08 21_46_54-sverweis_vlookup

 

 

 

 

 

In vielen Fällen möchte man an dieser Stelle entweder gar nichts, einen bestimmten Zahlenwert oder einen Text hinterlegen.
Einen besonderen Fall habe ich weiter unten beschrieben, bei dem zwei unterschiedliche Tabellen gleichzeitig in einer Formel abgefragt werden können (klick)

OK, die Formel:

SVERWEIS(A5;Tabelle2!A5:B13;2;0)

wirft hier einen Fehler aus, weil das Suchkriterium nicht in der Suchmatrix enthalten ist. Excel stellt einige IST-Funktionen zur Verfügung, die wir uns zu Nutze machen können.

2014-10-08 21_57_19-ist-funktionen

 

 

 

 

 

 

 

Für diesen speziellen Fall wählen wir die IST-Funktion –> ISTFEHLER in Kombination mit der WENN-Funktion.

Die WENN-Funktion hat folgende Syntax: WENN – DANN – SONST

Wir können das ausnutzen und nach dem Schema vorgehen:

WENN es einen FEHLER gibt, DANN gib mir einen Text zurück, SONST nutze den SVERWEIS.

Wir gehen folgendermaßen vor:

  1. Wir kopieren die eigentliche SVerweis Funktion SVERWEIS(A5;Tabelle2!A5:B13;2;0) mit STRG+C in die Zwischenablage
  2. Dann schreiben wir direkt hinter dem Gleichheitszeichen und vor der SVerweis-Funktion: WENN(ISTFEHLER(
  3. gehen ans ENDE der SVerweis-Funktion und schließen diese mit einem ) ab. Zwischenstand: =WENN(ISTFEHLER(SVERWEIS(A5;Tabelle2!A5:B13;2;0))
  4. Als nächstes formulieren wir den DANN-Fall, starten mit einem Semikolon und dem Text, der angezeigt werden soll, wenn ein Fehler auftritt:
    Zwischenstand: =WENN(ISTFEHLER(SVERWEIS(A5;Tabelle2!A5:B13;2;0));“Dein Text“
  5. Zum Schluss noch der SONST-Fall und wir starten wieder mit einem Semikolon und fügen dann die vorher kopierte SVerweis Funktion ein und schließen mit einer Klammer ab.
  6. Endstand der Formel: =WENN(ISTFEHLER(SVERWEIS(A5;Tabelle2!A5:B13;2;0));“Dein Text“;SVERWEIS(A5;Tabelle2!A5:B13;2;0))

2014-10-08 22_13_51-Microsoft Excel - sverweis.xls [Kompatibilitätsmodus]

 

 

 

An der Stelle in der Formel

=WENN(ISTFEHLER(SVERWEIS(A5;Tabelle2!A5:B13;2;0));„Dein Text“;SVERWEIS(A5;Tabelle2!A5:B13;2;0))

kann man seinen Wünschen freien Lauf lassen. Ein paar Beispiele:

  • Wenn man NICHTS ausgeben lassen will:
    • =WENN(ISTFEHLER(SVERWEIS(A5;Tabelle2!A5:B13;2;0));„“;SVERWEIS(A5;…..
  • Wenn man eine Null als Zahl ausgeben lassen will:
    • =WENN(ISTFEHLER(SVERWEIS(A5;Tabelle2!A5:B13;2;0));0;SVERWEIS(A5;……
  • usw…..

Einen ganz speziellen Fall möchte ich noch erwähnen:

Wir nehmen an, es sollen ZWEI unterschiedliche Tabellen, entweder:

  • aus dem gleichen Arbeitsblatt
  • aus einem anderen Arbeitsblatt innerhalb der gleichen Datei oder
  • aus einer anderen Tabelle einer anderen Datei abgefragt werden.

Die sprachliche Syntax würde dann lauten:

WENN – ISTFEHLER – SVerweis 1 – DANN – SVERWEIS 2 – SONST – SVerweis 1

Beispiel:

=WENN(ISTFEHLER(SVERWEIS(A5;Tabelle2!A5:B13;2;0));SVERWEIS(A5;Tabelle3!A1:B100;1;0); SVERWEIS(A5;Tabelle2!A5:B13;2;0))

(zurück)

Sollten noch Fragen bei Euch auftauchen oder Anmerkungen, freue ich mich über Eure Kommentare

LG John

26 Kommentare

  1. Hallo, ich versuche gerade eine wenn dann sonst-Funktion mit 2 Sverweisen zum verknüpfen und bekomme ständig Fehlermeldungen.
    Die Zelle soll den Preis für eine Reiserücktrittskostenversicherung ausgeben und unterscheiden ob es sich um eine Einzelreise oder eine Gruppenreise (ab 10 Personen) handelt. Ich habe ein Feld „Teilnehmerzahl“ (B21) ein Feld „Reisepreis pro Person“(B19) und eine extra Tabelle in der die Kosten für die Versicherung dem Reisepreis(tabelle2) gegenübergestellt wird.
    Meine Formel lautet so: =WENN(B2110;SVERWEIS(B19;Tabelle2!D2:E1452;2;FALSCH))

    Also wenn weniger als 10 Personen reisen soll aus der tabelle2 A2:B:1452 ein Wert herausgesucht werden und wenn es mehr als 10 Personen sind soll der Wert aus Tabelle2 D2:E1452 gesucht werden.

    Kannst du mir da weiter helfen? Was mache ich falsch?

    LG Steffi

    • Hallo Steffi,

      versuche doch mal folgendes:
      =WENN(B21<10;SVERWEIS(B19;Tabelle2!A2:B1452;2;0);SVERWEIS(B19;Tabelle2!D2:E1452;2;0))

      1. Wenn B21 < 10 DANN
      2. SVERWEIS(B19;Tabelle2!A2:B1452;2;0) sonst
      3. SVERWEIS(B19;Tabelle2!D2:E1452;2;0)

      LG John

      • Hallo

        Ich möchte gerne das Resultat des S-Verweises nutzen un mit einer zusätzliche wenn dann Funktion ergänzen.

        Also, Wenn der SVerweis einen Wert findet, soll ja erscheinen ansonsten nein.
        Meine Formel sieht im Moment so aus.

        =WENN(ISTFEHLER(SVERWEIS(B2;Anfrage!$A$3:$B$2000;1;FALSCH));““;SVERWEIS(B2;Anfrage!$A$3:$B$2000;1;FALSCH))

        • Hallo Sascha,
          soweit hast du schon alles richtig gemacht. Du musst nur noch zwischen „Gänsefüßchen“ im Falles eines Fehlers „NEIN“ reinschreiben und für den Fall, das es kein Fehler ist, brauchst du den SVerweis nicht, sondern schreibst einfach „JA“. Das sieht dann so aus:
          =WENN(ISTFEHLER(SVERWEIS(B2;Anfrage!$A$3:$B$2000;1;FALSCH));“NEIN“;“JA“)

  2. Hallo John,
    super beschrieben mit der Fehlermeldung, doch hab ich noch ein weiteres Problem. Wenn das Feld mit dem Suchkriterium leer steht kommt trotzdem der Text „gibts nicht“. Wie kann ich das noch verfeinern? Das ist im Moment meine Formel: =WENN(ISTFEHLER(SVERWEIS(B23;Menü;2;0));“gibt’s nicht“;SVERWEIS(B23;Menü;2;0))

    Also wenn B23 nicht beschriftet ist, soll auch nichts angezeigt werden

    Danke schonmal für nen Tipp 🙂

    Lg Tobi

    • Hi Tobi,
      versuche es mal so:
      =WENN(B23=““;““;WENN(ISTFEHLER(SVERWEIS(B23;Menü;2;0));”gibt’s nicht”;SVERWEIS(B23;Menü;2;0)))
      Du fragst die Zelle B23 ab, ob sie einen Inhalt hat, wenn nicht, dann „“, sonst…kommt deine Formel.

      LG John

  3. Hallo John,
    ich arbeitete mit der Formel:
    =WENN(ISTFEHLER(SVERWEIS(K1;Tabelle2!$J$1:$K$50;2;0));0;SVERWEIS(K1;Tabelle2!$J$1:$K$50;2;0))

    Ich vergleiche den Wert in Zelle K1 mit der Martix in Tabelle 2.

    Komischerweise funktioniert es nur, wenn ich die Zelle K1 lösche (Entf) und per Tastatur den Wert darin neu eingebe. Dannach geht es.
    Spalte K besteht aber aus 6000 Zeilen…
    Die Formatierung der Spalte K ist „Standard“, aber auch eine Formatierung der Spalte K in Zahl, Text, usw. funktionierte auch nicht.
    Die Zellen der Matrix haben auch die Formatierung „Standard“.

    Nur das Entf. und neu per Tastatur eingeben funktioniert.
    Jedoch bei 6.000 Werten nicht machbar.

    Hast du vielleicht eine Idee?

    vielen Dank

    • Hallo Christian,
      ich nehme mal an, dass in K1 „eigentlich“ eine Zahl steht, diese aber nicht als Zahl erkannt wird, nur wenn du die „neu“ in die Zelle eingibst, „wandelt“ Excel dies um. In Tabelle2 hast du nur Zahlen in Spalte „K“.
      Da gibt es 2 Lösungsansätze. Die erste verlangt Excel eine Menge ab, die zweite verlangt dir vorab etwas ab.

      1. =WENN(ISTFEHLER(SVERWEIS(K1;TEXT(Tabelle2!$J$1:$K$50;“#“);2;0));0; SVERWEIS(K1;TEXT(Tabelle2!$J$1:$K$50;“#“);2;0))
        –> Hier wird das Suchkriterium als Text behandelt, also muss die Tabelle2 auch als Text behandelt werden. Das macht man mit TEXT(Tabelle2!Bereich;“#“). Dabei muss auf das Formatierungszeichen geachtet werden. Wenn die Ausgabe reiner Text oder Ganzzahlen sind (also das was in Tabelle2 aus Spalte „K“ zurückgegeben werden soll), dann die Raute „#“. Wenn es Kommazahlen sind sind, dann das „@“ Zeichen. Wenn du diese Formel nutzt MUSST du die komplette Formel als Matrixformel umwandeln, mit gedrückter STRG+SHIFT+ENTER, ansonsten erhälst du den gewünschten Wert nicht.SVerweis als Matrixformel
      2. Du formatierst, das Suchkriterium vorher als Zahl um. Bei so vielen Reihen, gibst du in einer freien Spalte (z.B. „M“) folgendes in M1 ein =K1*1 Also die Zelle K1 mit 1 mal nehmen. Das ziehst du dann für alle 6.000 Zeilen runter und erhältst eine duplizierte Spalte „K“ mit reinen Zahlenwerten. Dann kopierst du diese Spalte und fügst nur die Werte und das Format mit einem Rutsch in Spalte „K“ ein.Sverweis

      Ich hoffe ich habe dich nicht voll verwirrt. Zur besseren Anschauung habe ich ein paar Screenies dazugepackt. Solltest du noch nicht klar kommen, schreibe mir, wo es noch klemmt.

      • Hallo John,

        schon mal vielen Dank für deine schnelle Antwort.

        Ich habe beides probiert, klappt leider nicht. Liegt aber bestimmt daran, das das Suchkriterium eine Zahlen/Buchstabenkombination ist, z.B. A2J2Q3 und kein reiner Text aus Buchstaben.

        Bei Variante 2, (=K1*1) bringt er mir #Wert!
        Bei Variante 1 mit der Martixformel bleibt es wie gehabt und er erkennt es nicht.

        Ich muß es nun irgendwie schaffen die Zahlen/Buchstabenkombinationen aus Spalte K in reine Zahlen zu formatieren.
        Hast du ein Idee?

        Vielen Dank
        Christian

        • Hallo Christian…..Nein, dann liegt das Prob woanders. Nur für den Fall, das man mit einer als Text formatierten Zahl in einem Bereich sucht, wo „echte“ Zahlen gefunden werden sollen, greifen meine beiden Ansätze !!!!!! Du hast da ein anderes Problem. Da ich deine Tabelle nicht habe, kann ich nur Vermutungen anstellen. Nächste Vermutung: Du hast in Spalte „K“ (dein Suckriterium) Leerzeichen am Ende, z.B. „A2J2Q3 „. In diesem Fall würde er auch nichts finden, wenn in der Tabelle2! „A2J2Q3“ steht. Wenn du dieses manuell eingibtst, hast du ja kein Leerzeichen am Ende, daher klappt das dann auch. In diesem Fall, probiere bitte folgende Formel:
          =WENN(ISTFEHLER(SVERWEIS(TRIM(K1);Tabelle2!$J$1:$K$50;2;0));0; SVERWEIS(TRIM(K1);Tabelle2!$J$1:$K$50;2;0))
          Hier bitte nicht rauskopieren oder du entfernst das Leerzeichen vor dem zweiten SVerweis. Das musste ich dort eingeben, weil das hier in dem Kommentarbereich nicht umbrochen wird.
          Mit TRIM(K1) entfernst du alle vor- und nachliegenden Leerzeichen !!
          LG John

  4. Perfekt John,

    klappt jetzt, das war es – vielen, vielen Dank!

  5. Hallo,
    ich bekomme mit dieser Formel #NV zurück.

    =WENN(C7=B103;(SVERWEIS(I13;F125:I164;4;FALSCH));WENN(C7=B104;(SVERWEIS(I13;O125:S164;4;FALSCH));WENN(C7=B105;(SVERWEIS(I13;F185:I224;4;FALSCH));WENN(C7=B106;(SVERWEIS(I13;O185:R224;4;FALSCH))))))

    und istfehler habe ich schon ohne Ergebnis versucht.

    Hat jemand eine Idee wieso ?

    • Hallo Michael, deine Formel funktioniert folgendermaßen:
      Wenn C7 gleich B103 ist, nimmt er sich den ersten SVerweis. Und wenn der erfolgreich ist, gibt er das auch brav aus. Wenn nicht, dann steigt Excel aus und gibt #NV zurück.
      Wenn C7 ungleich B103 ist, dann geht er in die zweite WENN Schleife und dort passiert das selbe. Und das ist das Problem. Was soll denn ausgegeben werden, wenn C7 gleich B103 ist aber I13 nicht im Bereich F125 bis F164 gefunden wird oder der SVerweis in der nächsten WENN Schleife nicht erfolgreich ist?
      Versuche mal folgendes:
      Ersetze mal deinen kompletten ersten SVerweis, also den Bereich: (SVERWEIS(I13;F125:I164;4;FALSCH))
      mit:
      WENN(ISTFEHLER((SVERWEIS(I13;F125:I164;4;0)));“B103 nicht gefunden“;
      und am Ende der kompletten Formel eine Klammer setzen, weil ein zusätzliches WENN dazu gekommen ist. Das ist dann aber nur für den ersten WENN Bereich, also C7 gleich B103 setzen und Fheler im SVerweis provozieren.
      Antworte mal hier, ob das das ist was du brauchst. LG John

      • Hallo John, das ist alles richtig und meine Formel funktioniert auch ohne Probleme.
        Sobald aber in I13 kein Wert steht kommt #NV.
        D.h., C7=B103 funktioniert einwandfrei aber bei I13 finde ich den Fehler nicht.
        Wie kann ich das unterdrücken, bzw. hast Du eine Idee ?
        Danke und Gruß
        Michael

  6. Hallo,
    ich habe eine Tabelle mit Namen, Einträgen und dem Geb.-Datum. Ich möchte daraus eine neue Geburtstagsliste (als neuer Excel-Tab) generieren.
    Also:
    Wenn in der Spalte P das Wort „Blau“ steht, dann soll von diesem Datensatz (oder dieser Zeile) das Geburtsdatum (aus Spalte E) und der Name und Vorname (aus Spalte B und C) in die neue Geburtstagsliste geschrieben werden.
    Diese neue Geburtstagsliste soll auch nach Monaten sortiert werden.

    Ich habe mir schonecht einen abgebrochen mit S-Verweis und Wenn-Dann aber ich kriege die Kombi irgendwie nicht hin. Kann mir vielleicht jemand helfen??

    • Hallo, so wie du das beschreibst, geht das einfacher mit VBA und ner kleinen Programmierung. Wenn das über den SVerweis gemacht werden soll, dann muss der SVerweis in der neuen Geburtstagsliste mit dem Wort „Blau“ in der „Namentabelle“ das Wort „Blau“ finden und kann dann Informationen „RECHTS“ von der Spalte, in der „Blau“ gefunden wurde ausgeben. Das ganze Konstrukt müsstest du genauer erklären um helfen zu können. Was hat z.B. „Blau“ mit einer Namens/Geburtstagsliste zu tun usw… LG John

  7. Hallo zusammen,
    ich habe mit dieser Verketteten Sverweis Funktion ein Problem – kann da einer weiterhelfen?
    =IF(ISERROR(VLOOKUP(‚Sales 2015_2016′!D109,’C79_2016‘!$J$2:$BM$126,36,FALSE)),VLOOKUP(‚Sales 2015_2016′!D109,’C68_2016‘!$I$2:$BL$101,36,FALSE),IF(ISERROR(VLOOKUP(‚Sales 2015_2016′!D109,’C68_2016‘!$I$2:$BL$101,36,FALSE)),VLOOKUP(‚Sales 2015_2016′!D109,’C78_2016‘!$I$1:$BK$13,35,FALSE),IF(ISERROR(VLOOKUP(‚Sales 2015_2016′!D109,’C78_2016‘!$I$1:$BK$13,35,FALSE)),VLOOKUP(‚Sales 2015_2016′!D109,’C77_2016‘!$K$2:$BN$36,36,FALSE),IF(ISERROR(VLOOKUP(‚Sales 2015_2016′!D109,’C77_2016‘!$K$2:$BN$36,36,FALSE)),VLOOKUP(‚Sales 2015_2016′!D109,’C79_2016‘!$J$2:$BM$126,36,FALSE),“not found“))))

    • Hallo Hans, es würde helfen wenn du kurz beschreiben könntest, was du für Probleme hast.
      Ich erkenne erstmal folgendes:
      Innerhalb des VLOOKUP (SVERWEIS) musst du die Parameter mit einem Semikolon trennen:
      Aus VLOOKUP(‚Sales 2015_2016′!D109,’C79_2016‘!$J$2:$BM$126,36,FALSE) wird
      VLOOKUP(‚Sales 2015_2016′!D109;’C79_2016‘!$J$2:$BM$126;36;FALSE)
      Dann nimm auch die Hochkommata ‚ und , raus dann sieht da so aus:
      VLOOKUP(Sales 2015_2016!D109;C79_2016!$J$2:$BM$126;36;FALSE)
      Die Trennung der IF Konstrukte musst du auch mit einem Semikolon vollziehen:
      Aus …..,FALSE),IF(ISERR….. musst du …..,FALSE);IF(ISERR….. machen
      LG John

  8. Hallo John,
    kannst du mir die Lösung geben?

    Ich bekomme einfach nicht den Wert zurück bei einem Suchkriterium aus drei verschiedenen Mappen. Den Suchbegriff gibt es nur einmal.

    =WENN(ISTFEHLER(SVERWEIS(A1;[Mappe3.xlsx]Tabelle1!$A$1:$D$10;4;0));SVERWEIS(A1;[Mappe2.xlsx]Tabelle3!$A$1:$D$10;4;0); SVERWEIS(A1;[Mappe3.xlsx]Tabelle1!$A$1:$D$4;2;0))

    • Hallo Klaus, sieht syntaktisch richtig aus, bis auf ein kleines Leerzeichen, direkt vor dem letzten SVerweis. Wenn du die Formel so rauskopiert hast, dann entferne das mal. Feedback wäre toll. LG John

  9. Hallo John,
    manchmal muss man halt eine Nacht darüber schlafen und dann mit deiner Hilfe sieht meine Lösung so aus und funktioniert auch ohne Fehler. Super Dankeschön..

    =WENN(ISTFEHLER(SVERWEIS(C17;’G:\[Fachplaner_1.xlsx]2016′!$C$6:$BX$5000;8;0))=FALSCH;SVERWEIS(C17;’G:\[Fachplaner_1.xlsx]2016′!$C$6:$BR$5000;8;0);WENN(ISTFEHLER(SVERWEIS(C17;’G:\[Fachplaner_2.xlsx]2016′!$C$6:$BX$5000;8;0))=FALSCH;SVERWEIS(C17;’G:\[Fachplaner_2.xlsx]2016′!$C$6:$BZ$5000;8;0);SVERWEIS(C17;’G:\[Fachplaner_3.xlsx]2016′!$C$6:$BS$5000;8;0)))

    • Hallo Klaus, schön das es funktioniert und das ich helfen konnte und lieben Dank für dein Feedback. Eine Anmerkung:
      ISTFEHLER fragt ab, ob der Klammerinhalt einen Fehler aufweist oder nicht. Wenn SVerweis nichts findet, dann ist die Abfrage auf „ISTFEHLER“ WAHR !! Du fragst aber ab, ob ISTFEHLER = Falsch ist, also der SVerweis etwas gefunden hat. =WENN(ISTFEHLER(SVERWEIS(…hat was gefunden…))=FALSCH;“hat was gefunden“;“hat nichts gefunden“). Die weitere Verschachtelung findet dann immer am Ende der Wenn Abfrage statt und nicht im ersten Teil. Auch nicht schlecht die Variante. LG John

  10. Hallo John,
    Ich denke mal wenn man geholfen wird sollte man auch schon ein Feedback geben und auch mal danke sagen können für die Hilfe. Ja die Formel funktioniert super.

    Besten Dank noch mal.
    Diese Seite gehört zu meinen Favoriten !!

Schreibe einen Kommentar

Pflichtfelder sind mit * markiert.


Related Posts