Microsoft Excel

Herbers Excel/VBA-Archiv

verweis

Betrifft: verweis von: Christoph Sattler
Geschrieben am: 03.11.2014 19:17:47

Hallo,

hab ein merkwürdiges Phänomen mit dem Verweis-Befehl:

er funktioniert in meiner Tabelle nur an drei Positionen - an den anderen nicht?!

=VERWEIS("OK";C2:R2;$C$1:$R$1)

ich suche also in der 2. Zeile das Wort "OK"; wird es gefunden, soll der entsprechende Text aus Zeile1 ausgegeben werden.
Eigentlich ganz einfach - bei anderen Beispielen funktioniert das auch - nur bei dieser Tabelle nicht - jmd eine Idee?

https://www.herber.de/bbs/user/93520.xlsx

  

Betrifft: AW: verweis von: Daniel
Geschrieben am: 03.11.2014 19:35:25

Hi

die VERWEIS-Funktion benötigt aufsteigend sortierte Werte im Suchvektor, sonst liefert sie Unsinn als Ergebnis.

Besser ist hier die Kombinantion aus INDEX und VERGLEICH.
bei VERGLEICH kannst du über den 3. Parameter einstellen, ob die Werte unsortiert oder sortiert vorliegen.

da bei unsortierten Werten ein #NV-Fehler angezeigt wird wenn der Suchbegriff nicht vorhanden ist, muss man die Funktion WENNFEHLER vorschalten, wenn man den #NV-Fehler vermeiden und statt dem Fehler was anderes anzeigen will:

=WENNFEHLER(INDEX($C$1:$R$1;1;VERGLEICH("OK";C2:R2;0));"")
Gruß Daniel


  

Betrifft: AW: verweis von: Christoph Sattler
Geschrieben am: 03.11.2014 19:38:53

Vielen Dank!

CS


  

Betrifft: mit VERWEIS() geht es auch ... von: neopa C (paneo)
Geschrieben am: 03.11.2014 19:52:25

Hallo Christoph,

... nur nicht in der von MS-Hilfe angegeben "normalen" Anwendung.

Folgende VERWEIS()-Formel würde auch funktionieren:

=WENNFEHLER(VERWEIS(9;1/(A2:R2="ok");$1:$1);"")


Gruß Werner
.. , - ...


  

Betrifft: AW: mit VERWEIS() geht es auch ... von: Luschi
Geschrieben am: 03.11.2014 20:42:04

Hallo Werner,

Deine Formel findet immer den letzten Namen in der Zeile mit der Kennung 'OK'. In diesem Video:
https://www.video2brain.com/de/videotraining/excel-matrixformeln
wird für den 1. Namen mit 'OK' in der Zeile folgende Lösung vorgeschlagen:
=INDEX($C1:$R1;AGGREGAT(15;6;(1/($C2:$R2<>""))*SPALTE(C1:R1)-SPALTE(C1)+1;1))

Was hälst Du denn davon?

fragt Luschi
aus klein-Paris

PS: Die Aggregat()-Funktion wirkt als Matrixfunktion nur für die Funktionswerte ab 15!
und muß dann deshalb nicht mit Strg+Shift+Enter abgeschlossen werden.


  

Betrifft: AW: auf den ersten Blick... von: Daniel
Geschrieben am: 03.11.2014 21:00:16

...deutlich länger und auch komplizierter als Index(...;...;Vergleich(...))
Worin besteht der Vorteil dieser Formel?
Gruß Daniel


  

Betrifft: AW: auf den ersten Blick... von: Luschi
Geschrieben am: 03.11.2014 21:17:05

Hallo Daniel,

habe gerade Deine Lösung getestet und die bringt auch den 1. Wert mit 'OK' in der Zeile.
Da ich gerade Musterlösungen für Excel-Spezialisten vorbereite, wo kein Vba eine Rolle spielt, teste ich alles, was mir in die Quere kommt.
Ich glaube, daß Deine Formellösung einfacher zu erklären ist als die Lösung vom Video.

Danke sagt Luschi
aus Klein-Paris


  

Betrifft: wenn der Suchwert max. nur einmal vorkommt, .... von: neopa C (paneo)
Geschrieben am: 04.11.2014 08:33:02

Hallo Luschi,

... dann ist es egal, ob man von hinten/unten oder von vorn/oben sucht.
Wenn aber die erste Übereinstimmung gesucht wird, dann verwende ich seit langen meistens INDEX() und VERGLEICH().

Die Dir aufgezeigte Formel ergibt für mich momentan noch keinen wirklichen Sinn, für die Suche nach Rechts. Sie könnte von Dir möglicherweise von einer Formel angepaßt worden sein, wo nach unten gesucht wird und wo Zeilen ausgeblendet werden können. Oder? Ich schau es mir später noch mal an.

Gruß Werner
.. , - ...


 

Beiträge aus den Excel-Beispielen zum Thema "verweis"