Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Verweisfunktionen

Beitrag: SVERWEIS mehrfach vorkommendes Suchkriterium ohne Array

Aufgabe
Das Suchkriterium einer Verweisfunktion kann mehrfach vorkommen. Alle Treffer sollen aufgelistet werden. Dafür gibt es die Arrayformel Nr.28. Mit einer Hilfsspalte geht's auch ohne Arrayformel. Das ist schneller und funktioniert für ganze Spalten.

       A               B               C               D               E       
1 x  315    1  315  
2   207    4  549  
3   945    5  742  
4 x  549    7  211  
5 x  742    11  347  
6   259    12  243  
7 x  211    13  616  
8   950    15  253  
9   951    17  878  
10   456       
11 x  347       
12 x  243       
13 x  616       
14   592       
15 x  253       
16   192       
17 x  878       

Lösung
Suchspalte: A
Suchkriterium:"x"
Ergebnisspalte:B

Schreibe in D1
=VERGLEICH("x";A:A;0)

Schreibe in D2
=WENN(ZEILE()>ZÄHLENWENN(A:A;"x");"";D1+VERGLEICH("x";BEREICH.VERSCHIEBEN(A:A;D1;;65536-D1);0))
oder:
=WENN(ZEILE()>ZÄHLENWENN(A:A;"x");"";D1+VERGLEICH("x";INDIREKT("A"&D1+1&":A"&65536-D1);0))
oder:
=WENN(ZEILE()>ZÄHLENWENN(A:A;"x");"";D1+VERGLEICH("x";INDEX(INDEX(A:A;D1+1):A$65536;);0))

Schreibe in E1
=WENN(D1="";"";INDEX(B:B;D1))

D2 und E1 runterkopieren.