Das grosse Archiv mit 627
VBA-freien Formellösungen und Tips
 
   
 
 ÜBER UNS   NEWS   FORMELN   TIPPS   KONTAKT   GäSTEBUCH   FORUM   LINKS 
   
Übersicht   Zauberbuch   Rätselbuch   Sparbuch   Bücher-Downloads / Errata  
 
Verweisfunktionen (3) ->

SVERWEIS Suchkriterium kann in mehreren Spalten vorkommen* (35)
 
 
Aufgabe
Es soll eine ganze Tabelle nach einem Suchkriterium durchsucht werden.
Dann soll der rechts benachbarte Wert des Suchkriteriums bzw. der einer bestimmten Spalte (Normalfall) ausgegeben werden.

       A               B               C               D               E               F       
1 47   68   18   31   10   11  
2 24   94   94   18   58   15  
3 47   31   18   x   76   40  
4 64   88   83   47   7   68  
5 83   x   52   100   81   25  
6 74   69   5   38   50   4  
7 39   20   19   64   24   38  
8 41   98   78   83   40   84  
9 17   8   69   40   18   78  
10 83   84   28   7   58   44 

Lösung
Suchmatrix: A1:K20
Suchbegriff: "x"

Wenn das Suchkriterium nur einmal vorkommen kann:

{=INDEX(A1:K20;MIN(WENN(A1:K20="x";ZEILE(1:20)));1+MIN(WENN(A1:K20="x";SPALTE(A:K))))}
funktioniert auch mit ("Perry Pago):
{=MAX(WENN(A1:J20="x";B1:K20))}
Allerdings dürfen dann neben dem "x" nur Zahlen stehen (bei Texten gibt´s aufgrund von MAX ne Null).

Wenn das Suchkriterium mehrfach vorkommt, kann obige Formel zu Fehlern führen. Dann wird mit dieser Formel
{=INDEX(A1:K20;GANZZAHL(MIN(WENN(A1:K20="x";ZEILE(1:20)+SPALTE(A:K)/999)));REST(MIN(WENN(A1:K20="x";ZEILE(1:20)+SPALTE(A:K)/999));1)*999+1)}
auf das "x" zugegriffen, das die kleinste Zeilenzahl hat.


Soll auf eine bestimmte Spalte (hier C) zugegriffen werden:

{=INDEX(C:C;MIN(WENN(A1:K20="x";ZEILE(1:20))))}
Der abzugrasende Bereich darf aber nicht zu groß sein (Rechnerabsturz!)

ohne Array:
=INDEX(C:C;VERGLEICH(1;1/MMULT(1*(A1:K20="x");ZEILE(1:11)^0);0))
Mehrfach vorkommende Suchkriterien in einer Zeile werden aber ignoriert.

Mit mehrfach vorkommenden Suchkriterien in einer Zeile:
=VERWEIS(2;1/MMULT(1*(A1:K20="x");ZEILE(1:11)^0);C:C)
Hier wird aber das letzte vorkommende Suchkriterium (Zeilenzahl) gefunden.



 
    zurück


 
powered by telltarget