Suchen Sie ein Feld basierend auf zwei oder mehr Spalten

Option 1:

Um das Problem zu lösen, benötigen Sie eine HELPER-Zelle, um festzulegen, welchen Code Sie verwenden möchten.

Schreiben Sie diese Formel in eine Zelle F10.

=IFERROR(INDEX($A$3:$A$6,IF($C$9=1,MATCH($D$10,$D$3:$D$6,0),IF($C$9=2,MATCH($D$10,$G$3:$G$6,0)))),"Wrong Code") 

Wie es funktioniert:

• C9Schreiben Sie in Helper Cell entweder 1 oder 2.
• Schreiben Sie Sub Codein Zelle D10.
• Formel sucht Sub Codein Column Dwenn C9 has 1, sonst überprüfen Sie die Sub CodeinColumn G.
• Wenn Sie eine falsche Kombination in Zellen C9und D10dann festgelegt haben, gibt die Formel einen falschen Codefehler aus.

Bearbeitet:

Option 2:

Diese Array-Formel kann auch in Cell verwendet werden F10.

{=IFERROR(INDEX($A$3:$A$6,MATCH(1,($D$3:$D$6=$D$10)+($G$3:$G$6=$D$10),0)),"Wrong Code")} 

Hinweis,

• Beenden Sie die oben beschriebene Formel mit Ctrl+Shift+Enter.
• Bei der Arbeit mit OPTION 2 müssen Sie nicht in die Hilfszelle schreiben, sondern C9nur Sub Codein die Zelle D10.

Passen Sie die Zellverweise in der Formel nach Bedarf an.

Für VLOOKUP muss die Spalte ganz links durchsucht werden, damit dies nicht funktioniert. INDEX + MATCH funktioniert, aber MATCH kann nicht mehrere oder zweidimensionale Bereiche durchsuchen. Eine Problemumgehung besteht darin, mehrere MATCHes für die verschiedenen Bereiche zu kombinieren.

Das Ergebnis der Suche in E7 verwendet diese Formel:

=IFERROR(INDEX(A2:A5,IFERROR(MATCH(E8,D2:D5,0),0)+IFERROR(MATCH(E8,G2:G5,0),0)),"Invalid Sub_code") 

Jede Spalte hat einen eigenen MATCH. Die Übereinstimmung wird nur in einer Spalte vorkommen. IFERROR gibt also Null zurück, wenn die Spalte keine Übereinstimmung hat. Durch das Hinzufügen der Übereinstimmungswerte wird ein Ortsindex für die Spalte mit der Übereinstimmung und Null für alle weiteren gesuchten Spalten kombiniert. Der Fragentitel sagt "zwei oder mehr Spalten". Dies kann durch Hinzufügen eines weiteren MATCH-Ausdrucks für jede weitere Spalte erweitert werden.

INDEX wird mit einem eigenen IFERROR umhüllt, falls ein falscher Subcode eingegeben wird. Bei MATCH wird nicht zwischen Groß- und Kleinschreibung unterschieden. Es wird also immer noch eine Übereinstimmung gefunden, wenn nur die Groß- und Kleinschreibung nicht gleich ist.

Natürlich können Sie mehrere Eingabezellen und entsprechende Ergebniszellen haben.

Alternative

@Rajesh S hat festgestellt, dass dies auch umstrukturiert werden kann:

=IFERROR(INDEX(A2:A5,MATCH(E8,D2:D5,0)),IFERROR(INDEX(A2:A5,MATCH(E8,G2:G5,0)),"Invalid Sub_code")) 

Dabei wird jeder MATCH-Ausdruck mit INDEX verwendet. Der IFERROR auf dem INDEX kümmert sich auch um MATCH-Fehler. Die IFERRORS werden dann geschachtelt, sodass ein Fehler beim ersten Lookup beim zweiten Lookup auftritt und ein Fehler bei der fehlerhaften Warnung Sub_code. Es endet dieselbe Länge, da die Anzahl der INERX-Funktionen steigt, während die Anzahl der IFERROR-Funktionen abnimmt. Dies kann durch zusätzliche Verschachtelung für zusätzliche Spalten vergrößert werden.

Verwenden Sie die Version, die am intuitivsten erscheint.

Um die Struktur und Logik zu vergleichen, werde ich die Formeln aufschlüsseln.

Alternative 1:

=IFERROR( INDEX(A2:A5, IFERROR( MATCH(E8,D2:D5,0) ,0) + IFERROR( MATCH(E8,G2:G5,0) ,0) ) ,"Invalid Sub_code") 

Alternative 2:

=IFERROR(  INDEX(A2:A5, MATCH(E8,D2:D5,0) ) ,IFERROR( INDEX(A2:A5, MATCH(E8,G2:G5,0) ) ,"Invalid Sub_code") ) 

=IFERROR(INDIRECT("A"&IF(SUMPRODUCT(--($D$2:$D$5=$E$8),ROW($A$2:$A$5))=0, SUMPRODUCT(--($G$2:$G$5=$E$8),ROW($A$2:$A$5)), SUMPRODUCT(--($D$2:$D$5=$E$8),ROW($A$2:$A$5)))), IF(E8="","","Invalid code"))

Ich gebe zu, es ist nicht das kürzeste, aber meiner Meinung nach das stabilste. Fixer1234 gibt zu, dass er nicht zu elegant ist, während Rajesh Helfer-Zellen verwendet, was keine bewährte Methode ist, da Excel nicht für sie vorgesehen war und bei komplexeren Projekten zu Zirkelreferenzen führen könnte.

Zurück zu meiner Formel.

Wie es funktioniert

SUMPRODUCT(--($D$2:$D$5=$E$8),ROW($A$2:$A$5): Überprüft, ob Spalte D eine Übereinstimmung enthält. Wenn ja, wird die Zeilennummer der Übereinstimmung zurückgegeben. Wenn keine Übereinstimmung gefunden wird, wird 0 zurückgegeben.

SUMPRODUCT(--($G$2:$G$5=$E$8),ROW($A$2:$A$5): Überprüft, ob Spalte G eine Übereinstimmung enthält. Wenn ja, wird die Zeilennummer der Übereinstimmung zurückgegeben. Wenn keine Übereinstimmung gefunden wird, wird 0 zurückgegeben.

IF(...=0, ..., ...): Überprüft, ob die Suche in Spalte D 0 ergab, dh keine Übereinstimmung. In diesem Fall wird das Ergebnis für Spalte G zurückgegeben. Andernfalls wird nur das Ergebnis von Spalte D zurückgegeben.

INDIRECT("A"&...): Die Zeilennummer der SUMPRODUCTs (oder die 0, falls keine Übereinstimmung besteht) wird mit "A" kombiniert - dem Buchstaben der ersten Spalte. Die INDIRECT Funktion findet dann die Zelle mit dieser Referenz. Es könnte zum Beispiel die Zelle 'A3' oder, wenn keine Übereinstimmung gefunden wurde, 'A0' gefunden werden - eine nicht vorhandene Zelle.

IFERR(..., ...): Überprüft und behandelt Fehler. Wenn Spalte D keine Übereinstimmung enthält, wird die Suche nach Spalte G zurückgegeben. Wenn Spalte G jedoch auch nichts enthält, wird "A0" nur an die INDIRECT Funktion übergeben, die keine echte Zelle ist. Daher wird ein Fehler ausgegeben, und es wird der Fehlerbehandler ausgeführt, anstatt den Fehler anzuzeigen.

IF(E8="","","Invalid code")): Behandelt den Fehler. Wenn die Eingabezelle leer ist, bleibt sie auch leer, da kein Aufwand erforderlich ist. Wenn es jedoch nicht leer ist, aber immer noch ein Fehler auftritt, wird 'Invalid Code' zurückgegeben , da dies nur bedeuten kann, dass etwas eingegeben wurde und dass etwas ungültig war.

Beispiele

Funktionell:

Leere Eingabe:

Ungültige Eingabe: