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

gestaltet by Biene Maya