Das grosse Archiv mit 628
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 bei mehrspaltigen Suchkriterien + Mehrfachtreffer (30)
 
 
Aufgabe
Bei der SVERWEIS-Funktion wird nur eine Spalte durchsucht.
Wie lautet die Formel, wenn das Suchkriterium in zwei Spalten A und B steht?

       A               B               C       
1 20  
2 17  
3 30  
4 x  y  60  
5 10  
6 x  y  22  
7 24  
8 62  
9 x  y  35  
10 40 


Lösung
Suchspalte 1 : A, Suchbegriff "x"
Suchspalte 2 : B, Suchbegriff "y"
Ergebnisspalte: C

{=INDEX(C:C;VERGLEICH("x"&"y";A1:A99&B1:B99;0))}
Kann es vorkommen, daß xy in Spalte A und nichts in Spalte B steht, dann:
{=INDEX(C:C;VERGLEICH("x"&"*"&"y";A1:A99&"*"&B1:B99;0))}
bzw. durch Vergleich des Wahrheitswertes 1:
{=INDEX(C:C;VERGLEICH(1;(A1:A99="x")*(B1:B99="y");0))}

ohne Abschluss als {Array}:
=INDEX(C:C;VERGLEICH(1;MMULT((A1:A99="x")*(B1:B99="y");1);0))
sowie:
=INDEX(C:C;SUMMENPRODUKT((A1:A99="x")*(B1:B99="y")*ZEILE(1:99)))
sowie:
=VERWEIS(2;1/(A1:A99&B1:B99="x"&"y");C:C)
und
=VERWEIS(2;1/(A1:A99&"*"&B1:B99="x"&"*"&"y");C:C)

Kommt die Suchbegriffskombination mehrfach vor, liefern die INDEX-Formeln den ersten, die VERWEIS-Formeln den letzten Treffer.
Sollen alle Treffer aufgelistet werden:
{=WENN(ZEILE(Z1)>SUMME((A$1:A$99="x")*(B$1:B$99="y"));"";INDEX(C:C;KKLEINSTE(WENN((A$1:A$99="x")*(B$1:B$99="y");ZEILE($1:$99));ZEILE(Z1))))}
runterkopieren

Erläuterung
"x" und "y" können natürlich auch in einzelnen Zellen stehen (ohne Gänse).
Die GROSS-klein-Schreibung der Suchkriterien wird ignoriert.
"komplette Spalten" funktionieren nicht in Arrayformeln - die Treffer-Spalte betrifft dies nicht.
 
    zurück


 
powered by telltarget