verweis auf nachbarzelle

Forumthread: verweis auf nachbarzelle

05.12.2003 02:53:31

jet

hi!
koennt ihr mir bitte weiterhelfen: ich braeuchte eine verweisfkt., die mir einen wert aus einer tabelle sucht (nicht in einer fest definierten spalte) und mir dann einfach nur den wert rechts davon wiedergibt.
bsp.: verweis-treffer ist in b2, dann gib mir c2 wieder
treffer in d3, ergebnis e3 ....etc..
gruss
jet

3
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

SVERWEIS??

05.12.2003 06:15:45

Christoph Dümmen
AW: SVERWEIS??

07.12.2003 23:21:44

jet
link auf www.excelformeln.de

05.12.2003 07:00:12

Günther Oppold

SVERWEIS??

05.12.2003 06:15:45

Christoph Dümmen

meinst Du
=sverweis(suchkriterium;BereichWoSuchen;Rückgabespalte)??

Christoph

AW: SVERWEIS??

07.12.2003 23:21:44

jet

Ja, meine ich, wobei es nicht nur eine Rueckgabespalte gibt, sondern einfach nur die Nachbarzelle zurueckgegeben werden soll. Weisst du da eine Loesung?
Gruss
Jun

link auf www.excelformeln.de

05.12.2003 07:00:12

Günther Oppold

Hi jet,

Schau mal unter www.excelformeln.de
vielleicht hilft Dir die Formel weiter. Du müsstest zum Auslesen der ermittelten Zelle (bei der errechneten Spalte +1 hinzufügen) mit indirekt arbeiten

(angenommen die FORMEL ergibt als Lösung: C3, dann =indirekt(FORMEL)

http://www.excelformeln.de/formeln.html?welcher=58
dort steht:

Lösung
Suchbegriff: "x"
Es wird spaltenweise durchsucht, also C5 wird vor D4 gefunden.

In A1 steht die Spalte:
{=MIN(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A2:A65536;;SPALTE(1:1)-1;;);"x")>0;SPALTE(1:1)))}

in B1 steht die Zeile:
=VERGLEICH("x";BEREICH.VERSCHIEBEN(A:A;;A1-1;;);0)

Alternativ:
Die folgende Formel in B1 gibt die Adresse in A1-Schreibweise zurück:

{=ADRESSE(VERGLEICH("x";BEREICH.VERSCHIEBEN(A:A;;MIN(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A2:A65536;;SPALTE(1:1)-1;;);"x")>0;SPALTE(1:1)))-1;;);0);MIN(WENN(ZÄHLENWENN(BEREICH.VERSCHIEBEN(A2:A65536;;SPALTE(1:1)-1;;);"x")>0;SPALTE(1:1))))}

Variante:
Für einen überschaubaren Bereich - hier A2:F100 - langen (es wird jetzt zeilenweise durchsucht):
Zeile in B1:
{=MIN(WENN(A$2:F$100="x";ZEILE(2:100)))}
Spalte:
=VERGLEICH("x";INDIREKT("A"&B1):INDIREKT("F"&B1);0)
Adresse:
{=ADRESSE(MIN(WENN(A$2:F$100="x";ZEILE(2:100)));VERGLEICH("x";INDIREKT("A"&MIN(WENN(A$2:F$100="x";ZEILE(2:100)))):INDIREKT("F"&MIN(WENN(A$2:F$100="x";ZEILE(2:100))));0))}

Ist sicher, daß der Suchbegriff NUR EINMAL vorkommt, langt:
=ADRESSE(MAX((A2:F100="x")*ZEILE(2:100));MAX((A2:F100="x")*SPALTE(A:F)))

Gruß
Günther

Infobox / Tutorial

Schritt-für-Schritt-Anleitung

Um einen Wert aus einer Nachbarzelle zurückzugeben, wenn ein bestimmter Wert in einer Spalte vorhanden ist, kannst Du die folgende Formel verwenden:

Bestimme den Suchbegriff: Angenommen, Du suchst nach dem Wert "x".
Verwende die Kombination aus VERGLEICH und INDIREKT: Hier ist die Formel, die Du nutzen kannst:
```
=INDIREKT("R" & VERGLEICH("x";A:A;0) & "C" & SPALTE() + 1;FALSE)
```
Diese Formel sucht nach "x" in Spalte A und gibt den Wert aus der Nachbarzelle in Spalte B zurück. Achte darauf, dass Du die Spalte entsprechend anpassen kannst.
Eingabe der Formel: Trage die Formel in die Zelle ein, wo Du das Ergebnis angezeigt haben möchtest.

Häufige Fehler und Lösungen

Fehler: #NV
- Lösung: Stelle sicher, dass der Suchbegriff tatsächlich in der angegebenen Spalte vorhanden ist. Ansonsten gibt die Funktion VERGLEICH den Fehler #NV zurück.
Fehler: Falsche Rückgabespalte
- Lösung: Überprüfe die SPALTE()-Funktion in Deiner Formel. Sie muss um 1 erhöht werden, um die richtige Nachbarzelle zu referenzieren.

Alternative Methoden

Eine alternative Methode besteht darin, die SVERWEIS-Funktion zu verwenden. Dabei kann die Rückgabespalte leicht angepasst werden. Hier ein Beispiel:

=SVERWEIS("x";A:B;2;FALSCH)

In diesem Fall wird nach "x" in Spalte A gesucht und der Wert aus Spalte B zurückgegeben. Beachte, dass SVERWEIS eine feste Spalte benötigt, während die vorherige Methode dynamisch ist.

Praktische Beispiele

Beispiel 1: Du hast in Spalte A die Werte {a, b, x, d} und möchtest den Wert in Spalte B zurückgeben. In Spalte B stehen die Werte {1, 2, 3, 4}. Mit der Formel
```
=INDIREKT("R" & VERGLEICH("x";A:A;0) & "C" & SPALTE() + 1;FALSE)
```
erhältst Du den Wert 3.
Beispiel 2: Du kannst auch die SVERWEIS-Formel verwenden, um einen Wert in einer spezifischen Tabelle zu suchen:
```
=SVERWEIS("x";A1:B4;2;FALSCH)
```
Wenn der Wert "x" in A3 gefunden wird, gibt die Formel den Wert aus B3 zurück.

Tipps für Profis

Nutze WENNFEHLER(), um potenzielle Fehler abzufangen. Beispielsweise:

=WENNFEHLER(INDIREKT("R" & VERGLEICH("x";A:A;0) & "C" & SPALTE() + 1;FALSE);"Nicht gefunden")

Wenn Du häufig mit dynamischen Bereichen arbeitest, könnte die Kombination aus INDEX und VERGLEICH eine bessere Performance bieten.

FAQ: Häufige Fragen

1. Wie kann ich einen Wert aus einer Nachbarzelle zurückgeben, wenn er nicht in Spalte A steht? Du kannst die Formel anpassen, indem Du den Bereich in der VERGLEICH-Funktion änderst. Zum Beispiel:

=INDIREKT("R" & VERGLEICH("x";B:B;0) & "C" & SPALTE() + 1;FALSE)

2. Funktioniert das auch in Excel 365? Ja, alle genannten Methoden sind auch in Excel 365 anwendbar.

Beliebteste Forumthreads (12 Monate)