wake-up-neo.com

Dreidimensionale Suche mit INDEX/MATCH

Dies wurde aus der gestrichenen Frage etwas genommen und verbessert.

Für diejenigen, die gelöschte Beiträge sehen können, wurde Folgendes übernommen: https://stackoverflow.com/questions/39793322/three-dimensional-lookup-no-concatenate-or-named-ranges-Excel


Ich versuche, eine dreidimensionale Suche ohne benannte Bereiche oder Verkettungen durchzuführen. Vereinfacht sind meine Daten auf dem Formular:

    Column1 Column2 Column3
Scott           
P   1       2       3
M   4       5       6
N   7       8       9
George          
P   10      11      12
M   13      14      15
N   16      17      18

Ich möchte nun nach einem bestimmten Namen suchen und dann nach einem bestimmten Buchstaben in dieser Namenstabelle. Dann möchte ich diese Zeilennummer einer bestimmten Spalte zuordnen. 

Ich habe einen einfachen INDEX/MATCH ausprobiert:

=INDEX(A:D,MATCH("M",A:A,0),MATCH("Column1",1:1,0))

Und das funktioniert für den ersten Namen, aber keine anderen, da es die erste Instanz von M findet.

Wie kann ich es ändern, um nach einem anderen Namen zu suchen?


Ich habe unten geantwortet, möchte aber sehen, ob jemand eine bessere Lösung hat.

21
Scott Craner

Ich habe eine Anweisung IF()array verwendet, um die Zeilennummer P nach der Zeile George zu ermitteln. Außerdem musste ich die Funktion MIN() verwenden, um die erste P Zeilennummer nach dem Namen.

Darüber hinaus ist es eine einfache INDEX()-Funktion, die über eine Stunde lang mein Gehirn zermürbt hat :).

=INDEX($A$1:$D$9,MIN(IF((ROW(A1:A9)>MATCH($F$4,A1:A9,0))*(A1:A9=$F$5),ROW(A1:A9),"")),MATCH($F$6,$A$1:$D$1,0))

Vergiss nicht!
Verwenden Ctrl+Shift+Enter Wenn Sie die Formel beenden, wird sie als array - Formel ausgewertet.

3 Dimensional Array Function

13
CRUTER

Sie können zwei andere INDEX/MATCH-Werte im ersten MATCH verwenden, um den Suchbereich festzulegen. Dann müssen Sie einfach MATCH () hinzufügen, um die absolute Position des Namens zu ermitteln.

=INDEX(A:D,MATCH($H$4,INDEX(A:A,MATCH($H$3,A:A,0)):INDEX(A:A,MATCH($H$3,A:A,0)+4),0)+MATCH($H$3,A:A,0)-1,MATCH($H$5,$1:$1,0))

 ![enter image description here

13
Scott Craner

Sie können dies tun, indem Sie die Ergebnisse von zwei Übereinstimmungen addieren. Eine Übereinstimmung für die Namen plus eine Übereinstimmung für den Buchstaben entspricht der Gesamtzeile.

= Index (A: D, Übereinstimmung (G5, A3: A5,0) + Übereinstimmung (G3, A: A, 0), Übereinstimmung (G4,1: 1,0))

Mit anderen Worten: Index (Alle Daten, Übereinstimmung (Name, Spalte In Name genau)) + Übereinstimmung (Buchstabe, Spalte In Buchstabe), Match (Name der Spalte, Spalte genau).

Bildschirmaufnahme des Arbeitsblattes

6
Reeves

Meine Antwort versucht den allgemeinen Fall mit nur einer Einschränkung:

Dass ein Buchstabe ein Einzelzeichentext und ein Name mehr als ein Zeichen ist. Ansonsten gibt es logischerweise keinen Unterschied zwischen Buchstaben und Namen und es ist dann unmöglich, wirklich zu tun ...

RE-EDIT für besseren Funktionsaufbau:

{=INDEX($A$1:$D$17, MATCH($H$3,$A1:$A17, 0)+MATCH($H$4, INDEX($A1:$A17, MATCH($H$3,$A1:$A17, 0)):INDEX($A:$A, SMALL(IFERROR(MATCH($H$3,$A1:$A17, 0)+POWER(SQRT(IF(LEN($A$1:$A$17)>1, ROW($A$1:$A$17), 0)-MATCH($H$3,$A$1:$A$17, 0)), 2)-1, ROWS($A$1:$A$17)), 2)), 0)-1, MATCH($H$5, $A$1:$D$1, 0))}

Hierbei wird eine Array-Formel entlang der Spalte A verwendet, und es wird geprüft, ob die Länge> 1 ist, und die Zeilennummern werden mit den Buchstaben 0 in ein Array geworfen.

Dann wird von jeder übereinstimmenden Zeile des eindeutigen Namens (z. B. George) abgezogen.

Wir verwenden dann ein min (von allen anderen Namenszeilen, wobei die letzte Datenzeile die endgültige Standardeinstellung ist - SMALL-Funktion mit 2 Parametern), um die nächste Namenszeile zu finden (oder die letzte Datenzeile, wenn es keinen folgenden Namen gibt).

Rest ist Standard Index/Match usw.

Es wird korrekt # N/A zurückgegeben, wenn unter dem gewählten Namen kein solcher Buchstabe vorhanden ist ...

enter image description here

Mein Datensatz ist A1: A17, und die Formel könnte stattdessen jedes Mal A: A verwenden, aber das im IF berechnete Array benötigt A1: A17 für die Geschwindigkeit.

EDIT für besseren Funktionsaufbau:

Wenn wir vermeiden möchten, die Formel zu bearbeiten, wenn sich die Datenlänge ändert, können wir vollständige Spaltenreferenzen von A: A durch die gesamte Konstruktion gehen lassen (und Geschwindigkeit/Effizienz verlieren), wobei die letzte Datenzeile in colA über ROWS berechnet wird (A: EIN):

Erneut bearbeiten:

{=INDEX($A:$D, MATCH($H$3,$A:$A, 0)+MATCH($H$4, INDEX($A:$A, MATCH($H$3,$A:$A, 0)):INDEX($A:$A, SMALL(IFERROR(MATCH($H$3,$A:$A, 0)+POWER(SQRT(IF(LEN($A:$A)>1, ROW($A:$A), 0)-MATCH($H$3,$A:$A, 0)), 2)-1, ROWS($A:$A)), 2)), 0)-1, MATCH($H$5,1:1, 0))}

Es kommt wirklich auf das Setup an ...

Bei einer Version, bei der Leerzeichen als Trennzeichen für Namen verwendet werden, erneut bearbeiten

Wenn Sie Leerzeichen als Trennzeichen für Namen verwenden möchten, in denen in den Datenergebnissen keine Leerzeichen enthalten sind, in den Spalten B bis D jedoch Leerzeichen angezeigt werden, in denen ein Name vorhanden ist, führt dies zu einer geringfügigen Änderung der obigen Formeln:

=INDEX($A$1:$D$17, MATCH($H$3,$A$1:$A$17, 0)+MATCH($H$4, INDEX($A:$A, MATCH($H$3,$A:$A, 0)):INDEX($A:$A, SMALL(IFERROR(MATCH($H$3,$A:$A, 0)+POWER(SQRT(IF($B$1:$B$17="", ROW($A$1:$A$17), 0)-MATCH($H$3,$A$1:$A$17, 0)), 2)-1, ROWS($A$1:$A$17)), 2)), 0)-1, MATCH($H$5, $A$1:$D$1, 0))

Dies bedeutet, dass die Namen und Buchstaben keine festgelegte Länge haben müssen, sondern nur, dass in der Zeile mit dem Namen Leerzeichen erscheinen.

Eine kleine Änderung der Bedingung, um den Endbereich für die Suche nach dem Buchstaben zu finden, indem Folgendes ersetzt wird: SQRT(IF(LEN($A$1:$A$17)>1,:

SQRT(IF($B$1:$B$17="",

4
MacroMarc

Ich denke, das ist vielleicht etwas aufgeräumter und ein bisschen leichter zu modifizieren.

=INDEX(OFFSET(INDIRECT("A"&MATCH($H$3,$A:$A,0),TRUE),0,0,4,4),MATCH($H$4,$A:$A,0),MATCH(H5,$1:$1,0))

Wenn Sie den Bereich zuerst mithilfe von offset erstellen, können wir den Namen aus H3 verwenden, um diesen Wert festzulegen. Danach indizieren wir nur innerhalb dieses neuen Bereichs.

Nun hängt dies noch davon ab, ob die Namen in Spalte A bleiben.

2
Jacob Edmond

Angenommen, das Format der Daten ist immer Name, dann P, M und N diese Formel führt die Arbeit aus:

=INDEX($A:$D,
MATCH($H$3,$A:$A,0)
+LOOKUP($H$4,{"P",1;"M",2;"N",3}),
MATCH($H$5,$1:$1,0))
2
EEM

Ich würde den Bereich (4. Parameter) von Index () verwenden. Unten sehen Sie einen Screenshot der Testdaten. In diesem Beispiel wird davon ausgegangen, dass dieselben Spalten und Schlüssel sortiert und konsistent sind.

Dies funktioniert durch Verwendung von (Range1, Range2) als ersten Indexparameter. Verwenden Sie für den vierten Parameter des Indexes N für den Bereich in (), den Sie zurückgeben möchten.

 enter image description here

2
Kevin

Diese Lösung funktioniert unter fast allen Bedingungen. Eine Einschränkung, die ich gefunden habe, ist, wenn eines der Subjekte (Names) keine Daten für die Details (Buchstaben) hat, aber ab sofort dasselbe bei allen anderen Antworten auftritt.

Die Formel setzt voraus, dass sich die Daten unter B6:F30 befinden (um sicherzustellen, dass sie unabhängig vom Standort des Quellbereichs angewendet werden können).

Die Formel verwendet die Index\Match-Funktionen:

Zuerst ein MATCH, um die Position der Name abzurufen:

MATCH($H8,$B$6:$B$30,0)

Mit diesen Informationen wird mit INDEX ein Bereich erstellt, der verwendet wird, um die Position der Detail (Buchstabe) mithilfe einer zweiten MATCH-Funktion zu ermitteln:

+ MATCH($I8,INDEX($B$6:$B$30, 1 + MATCH($H8,$B$6:$B$30,0))
:INDEX($B$6:$B$30,ROWS($B$6:$B$30)),0),

Durch das Hinzufügen der Ergebnisse der ersten und der zweiten MATCH-Funktion wird die Position der Kombination Name`Detail` abgerufen und in einem Index für die gesamten Daten verwendet. Die Position der erforderlichen Datenspalte wird mit einer Übereinstimmung ermittelt:

INDEX($B$6:$F$30, 1st.MATCH + 2nd.MATCH,
MATCH(J$6,$B$6:$F$6,0))

Wenn sich die Ergebnisse unter G6:L30 befinden, geben Sie diese Formel in J8 ein und kopieren Sie sie in J8:L30:

= INDEX( $B$6:$F$30,
MATCH( $H8, $B$6:$B$30, 0)
+MATCH( $I8, INDEX( $B$6:$B$30 , 1 + MATCH( $H8, $B$6:$B$30 ,0))
: INDEX( $B$6:$B$30, ROWS($B$6:$B$30) ),0),
MATCH( J$6, $B$6:$F$6, 0)),"")

 enter image description here

2
EEM

Diese Lösung funktioniert unter allen bisher diskutierten Bedingungen (lassen Sie mich wissen, dass sie nicht funktioniert, und ich werde versuchen, sie abzudecken) . Ich poste dies als separate Antwort, da die in der vorherigen Antwort angewendeten Formeln zu Recht auf die in ihnen angegebenen Bedingungen zutreffen. Daher sind sie für Benutzer mit diesen spezifischen Szenarien nützlich, sodass sie diese langen Formeln nicht anwenden müssen.

In dieser Formel wird davon ausgegangen, dass sich die Daten unter B6:E30 befinden (um sicherzustellen, dass sie unabhängig vom Quellbereich angewendet werden können) .

Diese Formel verwendet die Funktionen Index\Match und ist ein Formel-Array.

FormulaArrays werden durch Drücken von [Ctrl] + [Shift] eingegeben. + [Enter] gleichzeitig sehen Sie { und } um die Formel, wenn richtig eingegeben

Syntax:

=IFERROR(INDEX(DataRng,
MATCH(Value1,NamesRng,0)
+IFERROR(MATCH(Value2,INDEX(NamesRng,
1+MATCH(Value1,NamesRng,0))
:INDEX(NamesRng, IFERROR(MATCH(Value1,NamesRng,0)
+MATCH("#",IF((INDEX(Col1Rng,1+MATCH(Value1,NamesRng,0))
:INDEX(Col1Rng,ROWS(NamesRng)))="","#","!"),0),
ROWS(NamesRng))),0),NA()),MATCH(ValCol,DataHdr,0)),"")

Argumente: Angenommen, die Daten befinden sich an B6: E30.

Value1 = Name in Data, d. h. George, Scott usw.

Value2 = Detail in Data, d. h. Detail1, Detalle2 usw.

ValCol = Column, zu finden in Data, d. h. Column1, Column2, etc.

DataRng = $B$6:$E$30

DataHdr = $B$6:$E$6

NamesRng = $B$6:$B$30

Col1Rng = $C$6:$C$30

1st MATCH : Ermittelt die Position des Namens:

MATCH(Value1,NamesRng,0)

2nd MATCH : Ruft die Endposition der entsprechenden Details des Namens ab, die durch einen leeren Wert in Spalte C oder das Ende des Datenbereichs bestimmt wird:

MATCH("#",IF((INDEX(Col1Rng, 1 + 1stMATCH)
:INDEX(Col1Rng,ROWS(NamesRng)))="","#","!"),0),

Erstellt einen Bereich (vRange) : Mit den Details der Namen unter Verwendung der 1. und 2. Übereinstimmungsfunktionen. Wenn 2nd Match einen Fehler zurückgibt, wird die letzte Zeile des Datenbereichs verwendet:

INDEX(NamesRng, 1 + 1stMATCH )
:INDEX(NamesRng, IFERROR( 1stMATCH + 2ndMATCH, ROWS(NamesRng)))

3rd MATCH : Ermittelt die Position des Detail innerhalb des vRanges. Es gibt #NA zurück, wenn die Kombination nicht vorhanden ist.

IFERROR(MATCH(Value2, vRange,0), NA())

Durch Addieren der Ergebnisse der 1. und 3. Übereinstimmungsfunktion wird der Zeilenindex des Name`Detail combination or # NA if no found. The Column index is obtained with a Match from the Header of the Data. It then applying the INDEX function to the Data Range returns the value of the Name\Detail\Spalte combination. If the Name\Detail erhalten `Kombination nicht gefunden, es wird leer zurückgegeben.

=IFERROR( INDEX( DataRng, 1stMATCH + 3rdMATCH, MATCH(Column,DataHdr,0)),"")

Wenn sich die Ergebnisse bei H6: L37 befinden, geben Sie dieses Formel-Array in J8 ein und kopieren Sie dann nach K8: L37 und nach J9: L37:

=IFERROR( INDEX($B$6:$E$30,
MATCH($H8,$B$6:$B$30,0)
+IFERROR( MATCH($I8, INDEX($B$6:$B$30,
1+MATCH($H8,$B$6:$B$30,0))
:INDEX($B$6:$B$30, IFERROR(MATCH($H8,$B$6:$B$30,0)
+MATCH("#", IF((INDEX($C$6:$C$30,1+MATCH($H8,$B$6:$B$30,0))
:INDEX($C$6:$C$30,ROWS($B$6:$B$30)))="","#","!"),0),
ROWS($B$6:$B$30))),0),NA()),
MATCH(J$6,$B$6:$E$6,0)), "")

enter image description here

1
EEM

Wow ... So viele Lösungen schon.

Ich denke, eine einfachere Lösung könnte Offset verwenden, um eine allgemeinere Antwort zu erhalten.

=INDEX($A$1:$D$9, MATCH($G$3,OFFSET($A$1,MATCH($G$2,$A$1:$A$9,0),0,3,1),0)+MATCH($G$2,$A$1:$A$9,0), MATCH($G$4,$B$1:$D$1,0)+1)

Die einzige zu suchende Variable ist 3, dh die Anzahl der vorhandenen M/N/P-Optionen, da sich dies auf die Anzahl der Zeilen auswirkt. Ansonsten funktioniert die Lösung in allen möglichen Szenarien und unterschiedlichen Bestellungen.

0
Som Shekhar

Wenn ich mehr als zwei Eingaben für eine Datensuche habe, ziehe ich es vor, die Daten wie in der Abbildung gezeigt zu organisieren, damit ich eine Pivot-Tabelle verwenden und die Daten nach Belieben in Zeilen und Spalten organisieren kann.

Dann benutze ich GETPIVOTDATA, um nach einem Wert zu suchen.

Zelle G9 Enthält diese Formel:

=GETPIVOTDATA("Value";$F$3;"Name";G15;"Letter";G16;"Column";G17)

enter image description here

0
David R