Verwenden Sie INDEX mit zwei MATCH-Funktionen für eine exakte Übereinstimmung, um die Menge am Schnittpunkt einer Asset-Zeile mit einer Formatspalte zurückzugeben. Diese Anleitung erstellt ein kleines Arbeitsblatt für Kunstassets, erklärt die Formel und zeigt eine verschachtelte XLOOKUP-Alternative für unterstützte Mac-Versionen.

Welche Suchmethode funktioniert in Excel für Mac?

Für diese Aufgabe haben Sie einen Assetnamen wie Dot Grid und ein Format wie PNG. Sie möchten die einzelne Menge an ihrem Schnittpunkt ermitteln. Dies wird üblicherweise als zweidimensionale Suche bezeichnet.

Microsoft gibt INDEX und MATCH als verfügbar in Excel für Microsoft 365 für Mac, Excel 2024 für Mac und Excel 2021 für Mac an. Beide Methoden in diesem Artikel funktionieren innerhalb dieses dokumentierten Mac-Umfangs. Siehe Microsofts Dokumentation zur INDEX-Funktion und Dokumentation zur MATCH-Funktion.

XLOOKUP ist ebenfalls für diese Mac-Editionen dokumentiert. Microsoft weist ausdrücklich darauf hin, dass die Funktion in Excel 2016 und Excel 2019 nicht verfügbar ist, obwohl diese Editionen in der umfassenderen Liste der unterstützten Versionen auf der Seite aufgeführt sind. Wenn Ihre Edition XLOOKUP nicht unterstützt, verwenden Sie die Methode mit INDEX und MATCH. Microsoft: XLOOKUP-Funktion

Beginnen Sie mit INDEX und MATCH, wenn Sie die Zeilen- und Spaltenpositionen beim Lernen getrennt prüfen möchten. Wählen Sie die verschachtelte XLOOKUP-Funktion, wenn Sie diese bereits verwenden und die standardmäßige exakte Übereinstimmung bevorzugen.

Kleines Raster für Kunstasset-Mengen einrichten

Geben Sie die folgenden Beispieldaten in einem leeren Arbeitsblatt in A1:D5 ein. Die linke Tabellenspalte unten entspricht der Arbeitsblattspalte A; fügen Sie keine zusätzliche Spalte für Zeilennummern hinzu. Die Mengen dienen nur der Veranschaulichung.

Asset SVG PNG JPG
Tile Arc 12 18 6
Dot Grid 8 24 10
Loop Ribbon 15 9 0
Soft Square 7 14 5

Jedes Asset steht in einer eigenen Zeile und jedes Format in einer eigenen Spalte. Verwenden Sie für dieses Beispiel eindeutige Assetnamen und eindeutige Formatüberschriften. Tragen Sie eine bekannte Menge von null ausdrücklich als Zahl ein; ungeklärte Mengen sollten Sie prüfen, bevor Sie das Raster verwenden.

Erstellen Sie auf demselben Arbeitsblatt einen separaten Suchbereich:

Zelle Eingabe
F1 Zu suchendes Asset
F2 Dot Grid
G1 Zu suchendes Format
G2 PNG
H1 Menge

Die drei Quellbereiche haben unterschiedliche Aufgaben: A2:A5 enthält Assetnamen, B1:D1 enthält Formatüberschriften und B2:D5 enthält ausschließlich Mengen. Wenn die Überschriften außerhalb des Mengenbereichs bleiben, stimmen die relativen Positionen korrekt überein.

Menge mit INDEX und zwei MATCH-Funktionen zurückgeben

Wählen Sie H2 aus, geben Sie die vollständige Formel ein und drücken Sie Return:

=INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0))

Das Ergebnis sollte 24 sein: die Menge für Dot Grid in der Spalte PNG. In Microsofts Anleitungen für Mac wird beschrieben, eine Ergebniszelle auszuwählen, eine mit = beginnende Formel einzugeben und Return zu drücken. Microsoft: Formel eingeben

Die beiden Positionen verstehen

Die erste MATCH-Funktion ermittelt die Position des Assets innerhalb von A2:A5; die zweite ermittelt die Position des Formats innerhalb von B1:D1. Das letzte Argument 0 fordert eine exakte Übereinstimmung an. Ohne dieses Argument verwendet MATCH standardmäßig eine ungefähre Übereinstimmung, die eine bestimmte Sortierung voraussetzt. Microsoft: MATCH-Funktion

Für die angegebenen Eingaben lauten die beiden inneren Berechnungen:

=MATCH(F2,$A$2:$A$5,0)

Ergebnis: 2.

=MATCH(G2,$B$1:$D$1,0)

Ergebnis: 2.

Die vollständige Formel wählt daher Zeile 2 und Spalte 2 innerhalb des Mengenblocks aus:

=INDEX($B$2:$D$5,2,2)

Das ist die Arbeitsblattzelle C3, die 24 enthält. INDEX verwendet die Positionen innerhalb des angegebenen Bereichs. Dessen zweite Zeile ist hier also die Arbeitsblattzeile 3. Microsoft dokumentiert, dass die Angabe beider Positionen den Wert an ihrem Schnittpunkt zurückgibt. Microsoft: INDEX-Funktion

Quellbereiche beim Kopieren fixieren

Die Dollarzeichen fixieren die Quellbereiche. Die Zellbezüge F2 und G2 bleiben relativ. Wenn Sie H2 nach unten in H3 kopieren, liest die nächste Formel daher F3 und G3 und verwendet dabei dasselbe Quellraster. Auf dem Mac können Sie auch einen Bezug in der Formelleiste auswählen und Command+T drücken, um zwischen Bezugstypen zu wechseln. Microsoft: Zwischen relativen und absoluten Bezügen wechseln

Geben Sie zum Beispiel Tile Arc in F3 und JPG in G3 ein. Kopieren Sie anschließend H2 nach H3. Das neue Ergebnis sollte 6 sein. So erhalten Sie eine kleine Anfrageliste, ohne das ursprüngliche Assetraster zu ändern.

Verschachteltes XLOOKUP in unterstützten Mac-Versionen verwenden

Mit demselben Arbeitsblatt und denselben Suchangaben können Sie stattdessen diese Formel in H2 eingeben:

=XLOOKUP(F2,$A$2:$A$5,XLOOKUP(G2,$B$1:$D$1,$B$2:$D$5))

Für Dot Grid und PNG wird 24 zurückgegeben. Microsofts Dokumentation enthält ein verschachteltes Beispiel mit XLOOKUP, das eine vertikale und eine horizontale Suche ausführt, um einen Wert am Schnittpunkt zurückzugeben. Die exakte Übereinstimmung ist standardmäßig eingestellt. Microsoft: XLOOKUP-Funktion

In diesem durchgerechneten Beispiel wählt die innere Suche die Mengenspalte PNG aus: 18, 24, 9 und 14. Die äußere Suche wählt den zu Dot Grid gehörenden Eintrag aus. Durch dieselben drei Quellbereiche lässt sich diese Methode leicht mit der vorherigen Formel vergleichen.

Ergebnis prüfen, bevor Sie ein größeres Raster verwenden

Verwenden Sie die folgenden Fälle als kleine Überprüfung. Ändern Sie für jede Zeile F2 und G2 und vergleichen Sie H2 mit dem erwarteten Ergebnis. Stellen Sie anschließend wieder Dot Grid und PNG ein, wenn Sie das ursprüngliche Beispiel beibehalten möchten.

Asseteingabe Formateingabe Erwartetes Ergebnis Prüfung
Dot Grid PNG 24 Schnittpunkt in der Mitte
Tile Arc JPG 6 Erstes Asset und letztes Format
Soft Square SVG 7 Letztes Asset und erstes Format
Loop Ribbon JPG 0 Explizite Nullmenge
Tile Arcs PNG #N/A Fehlender Assetname
Dot Grid TIFF #N/A Fehlende Formatüberschrift

Wenn eine Zahl falsch ist, prüfen Sie die Bereichsgrenzen, bevor Sie die Suchmethode ändern. Für dieses Raster muss A2:A5 zu den vier Zeilen von B2:D5 passen und B1:D1 zu dessen drei Spalten. Wenn Sie zum Beispiel A1 nur in den Asset-Suchbereich aufnehmen, verschiebt sich die berechnete Position von der vorgesehenen Mengenzeile.

Wenn Sie dieses Raster um ein weiteres Asset in Zeile 6 erweitern, aktualisieren Sie sowohl den Assetbereich zu A2:A6 als auch den Mengenblock zu B2:D6. Wenn Sie in Spalte E ein Format hinzufügen, aktualisieren Sie sowohl den Überschriftenbereich zu B1:E1 als auch den Mengenblock zu B2:E5. Diese paarweisen Änderungen bewahren die von den Formeln vorausgesetzte Anordnung.

Doppelte Namen müssen ebenfalls berücksichtigt werden. Eine exakte MATCH-Suche gibt die erste passende Position zurück und unterscheidet nicht zwischen Groß- und Kleinschreibung. Bei Textsuchen interpretiert sie außerdem * und ? als Platzhalter. Verwenden Sie für diese Einrichtung eindeutige Namen ohne diese Zeichen. Microsoft: MATCH-Funktion

Bei fehlendem Namen eine hilfreiche Meldung anzeigen

Sobald die einfache Suche funktioniert, können Sie eine Fehlermeldung bei fehlendem Treffer durch eine verständliche Meldung ersetzen:

=IFNA(INDEX($B$2:$D$5,MATCH(F2,$A$2:$A$5,0),MATCH(G2,$B$1:$D$1,0)),"Check asset or format")

IFNA gibt Ihre Meldung zurück, wenn die eingeschlossene Formel #N/A ergibt. Andernfalls bleibt das Ergebnis erhalten. Die Funktion ist für Microsoft 365 für Mac, Excel 2024 für Mac und Excel 2021 für Mac dokumentiert. Microsoft: IFNA-Funktion

In diesem Beispiel ergibt Tile Arcs die Meldung Asset oder Format prüfen, während Loop Ribbon mit JPG weiterhin 0 zurückgibt. So bleibt ein fehlender Name von einer erfassten Nullmenge unterscheidbar. Falls eine Quellmenge selbst #N/A enthält, ersetzt dieser Wrapper auch diesen Fehler. Prüfen Sie daher die Quellzelle, wenn die Namen korrekt erscheinen.

Für den Alltag sollten Sie Assetnamen eindeutig halten, die Ausrichtung der drei Quellbereiche beibehalten und nach Änderungen am Raster einige bekannte Schnittpunkte prüfen. Anschließend können Sie jede angefragte Kunstasset-Menge ermitteln, indem Sie die Eingaben für Asset und Format ändern.

Quellen