Gesucht: Formel in Excel zur Überprüfung ob ein Wert in einem Bereich vorhanden ist

[Blendi]

Hallo zusammen

Wahrscheinlich ist mein Problem supereinfach zu lösen aber ich finde die Lösung einfach nicht. Es ist doch bestimmt möglich eine Formel in eine Zelle einzugeben, die kontrolliert ob dieser Wert in einem bestimmten Bereich vorhanden ist.
Konkretes Beispiel: In Zelle A1 habe ich ein Datum stehen. Jetzt möchte ich wissen, ob dieses Datum in einer Zelle im Bereich B10 bis E30 vorhanden ist.

Wenn ich SVERWEIS oder VERGLEICH nehme, dann wird immer nur in einer Spalte oder Zeile gesucht aber nicht in einem Bereich.

Ich bin gespannt auf eure Antworten und werde mir dann wahrscheinlich an den Kopf langen, weil es so einfach gewesen wäre

Liebe Grüsse
Walter

 

[Nebuk]

Ich hab zwei Formeln, eine etwas längere:

=WENN(ISTNV(SVERWEIS($A$1;B1:B10;1;0));WENN(ISTNV(SVERWEIS($A$1;C1:C10;1;0));WENN(ISTNV(SVERWEIS($A$1;D1:D10;1;0));WENN(ISTNV(SVERWEIS($A$1;E1:E10;1;0));"nicht gefunden";"gefunden");"gefunden");"gefunden");"gefunden")

und eine kürzere:

=WENN(ZÄHLENWENN(B1:E10;A1)>0;"gefunden";"nicht gefunden")

 

[Blendi]

Vielen Dank

Danke Nebuk, die Formel mit Zählenwenn funktioniert tiptop. Die lange Version mit SVERWEIS wäre nicht praktikabel, denn der Bereich wäre 28 Spalten breit.

Gibt es denn eine Möglichkeit, die Formel noch so zu erweitern, dass aus der Zeile, in der das Datum gefunden wurde den Inhalt einer bestimmten Zelle zu übernehmen? Also wenn das Datum in Zeile 15 gefunden wurde, den Inhalt von Zelle A15 in die Zelle mit der Formel übernehmen anstatt "gefunden" zu schreiben. Ich denke, das wird schwierig sein, weil Zählenwenn ja nicht die Zeileninformation zurückgibt.

Nochmals danke

Walter

 

[Nebuk]

Auf die Schnelle habe ich nur das hier geschafft. Ziemlich komplizierter Aufbau, geht bestimmt auch einfacher:

=INDIREKT(VERKETTEN("B";RECHTS(ADRESSE(SUMMENPRODUKT((C1:G10=$A$2)*ZEILE(1:10));SUMMENPRODUKT((C1:G10=$A$2)*SPALTE(C:G)));1)))

"B" nach VERKETTEN( musst du wohl durch "A" ersetzen. Denn bei mir ist der Zeilenname in Spalte B der zurückgegeben wird.

 

[Blendi]

bin leider zu blöd

Hallo Nebuk

Und nochmals danke ich dir für deine Mühe. Hab probiert die Formel nachzuvollziehen und auf meine Wünsche anzupassen, bin aber anscheinend zu blöd dafür.

Was mich besonders interessieren würde: der Formelteil C1:G10=$A$2
Was soll das denn bewirken? Wie kann ich einen Bereich mit dem einer einzelnen Zelle vergleichen? Es steht ja auch keine Funktion dabei. Wenn ich diesen Teil allein in eine Zelle eintragen würde gibt es mir schon "kein Array-Wert gefunden" zurück.

Eigentlich dachte ich, dass ich mich recht gut mit Excel auskenne aber im Moment komme ich mir grad vor wie ein Grundschüler

Ich wünsche dir einen schönen Sonntag.

Gruss
Walter

 

[Nebuk]

Hallo Blendi

Ich hab die Formel nochmals leicht angepasst und vereinfacht:

=INDIREKT(VERKETTEN("B";SUMMENPRODUKT((C1:G10=$A2)*ZEILE(C1:G10))))

Die Formel macht folgendes:

Es sucht den Datumswert im grauen Feld A2. Gefunden wird der in D10, merkt sich davon die Zeile 10 und geht von dort aus in Spalte B schauen was sich in der Spalte B10 befindet. Der Wert aus B10 wird anschliessend in die gelbe Zelle, der Zelle in der die Formel steht übernommen.

Erklärung:
Der Teil SUMMENPRODUKT((C1:G10=$A2)*ZEILE(C1:G10)) wird gebraucht um die Zeile zu bestimmen in welcher der gesuchte Wert gefunden wird. In dem angehängten Bild wird der 20.01.2017 gesucht. Gefunden wird dieser Wert in Zelle D10. Die Formel gibt also "10" zurück.
C1:G10=$A2 wird als Suche nach dem Wert gebraucht. Der hintere Teil wird gebraucht um die relative Position der Suche zu bestimmen *ZEILE(C1:G10).
Siehe auch Summenprodukt: https://excelnova.org/2012/01/summe...r-dynamischen-berichterstattung-teil-1-von-2/
Beide angegebenen Bereiche müssen - damit die Formel funktioniert - immer den gleichen Bereich referenzieren. Wenn nicht wird eine Falsche Referenz zurückgegeben.

Mit dem Teil Verketten() bauen wir einfach eine Zellenadressen String. Aus Verketten("B"; 10) wird das Ergebnis "B10".

Zum Schluss noch die Funktion Indirekt(). Diese gibt einfach den Wert der darin referenzierenden Zelle (über die "Adresse" der Zelle B10) zurück. Also mit Indirekt("B10") bekommt man Zeilenname_10 zurück.

Falls nicht klar kannst du auch gerne ein Beispiel von dir hochladen und kurz schreiben was du gerne wo haben möchtest.

Gruss
Nebuk

 

[Blendi]

Es klappt!

Hallo Nebuk

Es klappt und ich hab es (halbwegs) kapiert. Super, ich danke dir vielmals für die Mühe und Geduld.

Liebe Grüsse

Walter