Wie kann ich mit INDEX(VERGLEICH()) alle Ergebnisse für eine Tabelle anzeigen lasse?
Ihr seht ja im Bild dass es 2 mal Magenta als Nachnamen gibt, aber nur die 4 als ergabniss angezeigt wird. Wie löse ich das Problem.
Am liebsten hätte ich die Ausgabe "4 in Zeile 5 und 23 in Zeile 17"
2 Antworten
in P4 : den fraglichen Namen eintippen oder herholen.
In P5: =P4 , das runterziehen bis P13 (für bis zu 10 Gleiche)
in Q4: =KGRÖSSTE(ZEILE(B$1:B$16)*(B$1:B$16=$P$4);ZEILEN(P$4:P4)) Eingabe als {Matrixformel} mit Strg+Shift+Enter. Ergibt Zeile des untersten Auftretens.Alternativ (ohne {Matrix-Eingabe} )
in R4: =SUMMENPRODUKT(KGRÖSSTE(ZEILE(B$1:B$16)*(B$1:B$16=$P$4);ZEILEN(P$4:P4)))
In S4: =INDEX($E$1:$E$28;KGRÖSSTE(ZEILE(B$1:B$16)*(B$1:B$16=$P$4);ZEILEN(P$4:P4))) als {Matrixformel} . Alternativ wieder
in T4: =INDEX($E$1:$E$28;SUMMENPRODUKT(KGRÖSSTE(ZEILE(B$1:B$16)*(B$1:B$16=$P$4);ZEILEN(P$4:P4)))) ohne {Matrix-Eingabe}, zu dieser Fo später nochmal..
Q4:T4 runterziehen bis Zeile 13
S4:T4 ff ergibt die Anrede. In S erscheint 0, wenn keine weitere Nennung, in T dagegen wird auch weiter unten noch irgendeine Anrede gezeigt. Man erkennt die Irrelevanz jedoch an der 0 in den Spalten Q und/oder R und könnte sie bei Weiterverarbeitung entsprechend ausschalten. Beispielhaft mit einer Kontrollformel
in P3: =WENN(R13>0;"evtl. mehr?";ZÄHLENWENN(R4:R13;">0"))
Wenn Du diesen Block weit oben ansiedelst, kannst Du ihn im Titelfenster einfrieren und unterhalb scrollen (wenn Du zB P4 per Bezug füllen willst)
Da habe ich die Frage wieder mal nicht aufmerksam gelesen, die Formel für Deinen Wunsch hätte mir viel Arbeit erspart, sie lautet (zB in Zeile15):
=B15&": "&ZÄHLENWENN(B:B;B15)&" in Zeile "&VERGLEICH(B15;B:B;0)
aber da wird dann natürlich nur die erste Nennungszeile gebracht.
Anstatt B15 kannst du natürlich auch 3 x einen Bezug ausserhalb der Tabelle nehmen (so wie bei meinen Formeln P4)
Du kannst aber zur Findung der Anrede auch den Vornamen mit einbeziehen: zB
in O6: =INDEX(E:E;VERGLEICH(B6&C6;B:B&C:C;0)) als {Matrixformel}
Dann wirds nur bei häufigeren Paarungen wie Meier, Hans oder Huber, Josef kritisch.
Danke erstmal. Momentan funktionierts bei mir nicht. Ich sehe bei dir ein paar Funktionen die ich davor noch nie benutzt habe, deswegen schau ichs mir später nochmal genauer an.
Danke aber dennoch für deine Mühe.
Es wird momentan ziemlich oft der Fehlerbericht #ZAHL! angezeigt in Q5:Q13 und S5:S13 .
Ich versteh nicht, das funktioniert bei mir einfach nicht.
Vielleicht erklär ichs nochmal. In meiner Tabelle gibt es viele Vornamen und Nachnamen usw. Nachnamen sind in Spalte A, Vornamen sind in Spalte B.
Ich hatte vor wenn ich in J4 Nr. auswähle und in J5 den dazu gesuchten Nachnamen eintrage, dass in J6 das erste mögliche Ergebnis angezeigt wird. In J7 das zweite Mögliche Ergebnis usw. und das nach der reihenfolge der Tabelle
Das heißt, dassin diesem Fall zuerst in J6 4 angezeigt werden soll und in J7=23
Ich bin am Verzweifeln...
Sry, ich hab in Deiner Vorlage aus ganz anderen Gründen eine Spalte vor A eingeschoben, so dass sich alles um eine Spalte verschoben hat. Bevor ich jetzt alle Formeln korrigiere, machst du das am besten auch, wendest die Formeln an und löschst dann die leere A.Spalte wieder.
Also erstens ist da kein zweites Magenta.
Wohl kann ich ein "Magneta" sehen, aber kein "Magenta".
Und zum Ermitteln von mehreren Ergebniszeilen: Ich kenne keine Funktion, die aus dem Handgelenk geschüttelt mehrere Treffer behandelt. Aber Du darst auch in Excel oder Calc scripten.In einem Script kannst Du den Suchbefehl wiederholt aufrufen, mit jeweils vom letzten Treffer weiter eingeschränktem Suchbereich.
habs korigiert. aber so oder so in der Tabelle gibt es 5 Magenta's
wiederholt aufrufen: mit Hilfszellen gehts auch ohne, aber du hast recht, mit VBA-Script gehts direkt.
Nachteil: xl suggeriert immer, .xlsm sei eine große Gefahr (fördert aber durchaus die Verbreitung seiner Makros ) .
Ausserdem werden Formeln erstaunlich einwandfrei von OO/LOffice erkannt und übersetzt, VBA-Makros dagegen überhaupt nicht (mein Grund, zähneknirschend bei xl zu bleiben!)
Ach so, das 16 bzw 28 musst Du natürlich ersetzen durch Deine benötigte Zeilenzahl, hab das nur für die Entwicklung klein gehalten.
KGrösste dreht die Reihenfolge um: unterste Nennung zuerst.
KKleinste geht dann genauso, wenn Du in B keine Leerzellen hast.
Sonst wirds wesentlich komplizierter: Willst du unbedingt die Reihenfolge oberste zuerst, dann in KGrösste ZEILEN(P$4:P4) ersetzen durch
KGrösste(....;Zählenwenn(B$1:B16;P$4)-ZEILEN(P$4:P4)+1)
hab das jetzt nicht mehr ausprobiert, könnte sein, dass dann die Nullen nicht mehr entstehen etc.
zu Matrixformeln gugl mal Haserodt Matrixformel , da findest du eine recht gute Einführung.