SVERWEIS Suchkriterium kann in mehreren Spalten vorkommen*

Gruppe: Verweisfunktionen

Beitrag: SVERWEIS Suchkriterium kann in mehreren Spalten vorkommen*

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.

Das Portal zum Thema Excel-Formeln