Verweis mit mehreren Suchkriterien in Zeilen und Spalten mit doppelten Einträgen

[Joe Cocker]

Hallo Forumteilnehmer,

ich habe eine Problem mit einer Vergleichformel, die mir als Ergebnis #WERT! liefert.
Basis bildet eine Matrix, in der ich anhand mehrerer Suchkriterien in Zeilen und Spalten den richtigen Eintrag suche. Die richtige Zeilennummer wird ermittelt; der Fehler entsteht bei der Ermittlung der Spaltennummer.

Verweis 1

	S	T	U	V	W	X	Y	Z
1					Suchkriterien2
2					C	B	C	D
3					3	2	3	4
4	Suchkriterien1
5	a	y	z		12	100	1.597	12.457
6	c	c	b		35	124	3.654	15.478
7	x	y	z		50	885	9.547	96.585
8	a	d	p		10	965	8.521	35.428
9	x	y	z		45	986	9.658	12.322
10	a	b	o		66	157	4.214	35.875
11
12	Sverweis mit mehreren Suchkriterien ohne doppelte Einträge
13
14	Suchkriterien1				Ergebnis
15	x	y	z		#WERT!
16	Suchkriterien2
17	C	3

Formeln der Tabelle

Zelle Formel W15 =INDEX(W5:Z10;VERGLEICH(1;MMULT((S5:S10=S15)*(T5:T10=T15)*(U5:U10=U15);1);0);VERGLEICH(1;MMULT((W2:Z2=S17)*(W3:Z3=T17);1);0))

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8



Gruß Joe

[shift-del]

Hallo

Du hast ja mehr als einen Treffer.

Arbeitsblatt mit dem Namen 'Tabelle1'
	W	X	Y	Z
14	Ergebnis
15
16		Spalte	Zeile
17	23,007	1	3	50
18	23,009	1	5	45
19	25,007	3	3	9547
20	25,009	3	5	9658
21	#ZAHL!	#ZAHL!	#ZAHL!	#ZAHL!
22	#ZAHL!	#ZAHL!	#ZAHL!	#ZAHL!

Zelle	Formel
W17	=AGGREGAT(15;6;SPALTE($W$5:$Z$10)/(W$2:$Z$2=$S$17)/($W$3:$Z$3=$T$17)+ZEILE($W$5:$Z$10)/($S$5:$S$10=$S$15)/($T$5:$T$10=$T$15)/($U$5:$U$10=$U$15)/10^3;ZEILEN(W$17:W17))
X17	=QUOTIENT(W17;1)-22
Y17	=REST(W17;1)*10^3-4
Z17	=INDEX($W$5:$Z$10;Y17;X17)

Verwendete Systemkomponenten: [Windows (32-bit) NT 6.01] MS Excel 2016

Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

[neopa]

Hallo,

mir ist auch unklar was Du hier mit: "...ohne doppelte Einträge"  meinst.
Wenn immer nur der oberste und am weitest links gelegene Treffer ausgewiesen werden soll,
dann folgende Formel:

Code:

=WENNFEHLER(INDEX(A:Z;AGGREGAT(15;6;ZEILE(W5:W13)/(S5:S13=S15)/(T5:T13=T15)/(U5:U13=U15);1);
AGGREGAT(15;6;SPALTE(W1:Z1)/(W2:Z2=S17)/(W3:Z3=T17);1));"")

Anderenfalls, um alle relevanten Treffer zu listen, wäre mein Vorschlag, nachfolgende Formel nach unten und rechts kopieren.

	S	T	U	V	W	X	Y	Z
1					Suchkriterien2
2					C	B	C	D
3					3	2	3	4
4	Suchkriterien1
5	a	y	z		12	100	1.597	12.457
6	c	c	b		35	124	3.654	15.478
7	x	y	z		50	885	9.547	96.585
8	a	d	p		10	965	8.521	35.428
9	x	y	z		45	986	9.658	12.322
10	a	b	o		66	157	4.214	35.875
11
12
13
14	Suchkriterien1				Ergebnis
15	x	y	z		50		9547
16	Suchkriterien2				45		9658
17	C	3
18

Formeln der Tabelle

Zelle Formel W15 =WENNFEHLER(INDEX(W:W;AGGREGAT(15;6;ZEILE(W$5:W$10)/($S$5:$S$10=$S$15)/($T$5:$T$10=$T$15)/($U$5:$U$10=$U$15)/(W$2=$S$17)/(W$3=$T$17);ZEILE(A1)));"")

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

[Der Steuerfuzzi]

Hallo,

das liegt an der Zeile. Du musst diese innerhalb von MMULT mittels MTRANS in eine Spalte "umwandeln", dann funktioniert auch die von Dir gepostete Formel ohne Fehler:

Code:

=INDEX(W5:Z10;VERGLEICH(1;MMULT((S5:S10=S15)*(T5:T10=T15)*(U5:U10=U15);1);0);VERGLEICH(1;MMULT(MTRANS((W2:Z2=S17)*(W3:Z3=T17));1);0))

[neopa]

Hallo Michael,

der Einsatz von MTRANS() erfordert zwingend den Abschluss der Formel als Matrixformel, was aber nicht notwendig ist, weil auch bei Anwendung von MMULT() hier kein MTRANS() notwendig ist.

Man muss dazu lediglich die Argumente im entsprechenden MMULT()-Formelteil vertauschen.

Also so:

Code:

=INDEX(W5:Z10;VERGLEICH(1;MMULT((S5:S10=S15)*(T5:T10=T15)*(U5:U10=U15);1);0);VERGLEICH(1;MMULT(1;(W2:Z2=S17)*(W3:Z3=T17));0))

@ Joe,

(sorry, hab erst heute mitbekomme, dass Du der Fragesteller bist)

Wie Du weißt, hab ich ein Faible  für AGGREGAT(), welche ich oft einsetze, wenn man mehrere Ergebnisse ohne klassische Matrixformel ausgegeben muss. Deshalb hatte ich aus dieser mein Formelvorschlag auch für nur ein Ergebnis aus der "mehre Ergebniswerte" abgeleitet.

Der Einsatz von MMULT() ist natürlich hier günstiger.

[Joe Cocker]

Hallo,
die Unterstützung bei den allen Formeln passt. Herzlichen Dank.
Joe
P.S. Hallo Werner, schön von Dir zu hören.