Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Verweisfunktionen - BEITRAG: 2 Suchkriterien in beliebigen Spalten in einer Zeile


Aufgabe
In A1 und A2 stehen 2 Suchbegriffe.
Diese können irgendwo in B1 bis F99 vorkommen.
Der Inhalt aus Spalte G soll ausgegeben werden, wenn beide Begriffe erstmals zusammen in einer Zeile auftauchen.

       A               B               C               D               E               F               G       
1 WF    Boris  Boris  Jens    Ottawa  
2 Jens    WF    WF  WF  Douala  
3   Jens          Oslo  
4     Jens    WF  WF  Frankfurt  
5       Boris  Jens    Zürich  
6             Sidney  
7   Boris    WF  Jens  Boris  Kapstadt  
8             Lourdes  
9   Jens  Jens  Jens  Jens  Jens  Athen 


Lösung
{=INDEX(G:G;MIN(WENN(MMULT(1*(B1:F99=A1);ZEILE(1:5))*MMULT(1*(B1:F99=A2);ZEILE(1:5))>0;ZEILE(1:99))))}
Wird keine Kombi gefunden, wird G1 ausgegeben.
Kann man sich natürlich zunutze machen, dass die Tabelle erst ab Zeile 2 beginnt und in G1 z.B. "kein Treffer" (unsichtbar formatiert) steht.
Letzter Wert logischerweise mit MAX statt MIN.

Als Variante mit VERGLEICH ohne {} von Josef B:
=INDEX(G:G;VERGLEICH(1;1*(MMULT(1*(B1:F99=A1);ZEILE(1:5))*MMULT(1*(B1:F99=A2);ZEILE(1:5))>0);))
Nur für den ersten Wert und kein Treffer ergibt #NV.

Erläuterung
Will man die betroffenen Spalten nicht auszählen (in obigem Beispiel 5) sondern die entsprechenden Spaltenbuchstaben sehen, dann MTRANS(SPALTE(B:F)^0 statt ZEILE(1:5) eingeben.