Werte Nachschlagen

Suchfunktionen lösen eines der häufigsten Probleme bei der Arbeit mit Tabellenkalkulationen: Daten befinden sich an einem Ort und es müssen dazugehörige Informationen aus einem anderen Bereich anhand eines übereinstimmenden Werts abgerufen werden.

Der Funktion wird ein Suchwert übergeben, der Suchbereich angegeben und festgelegt, was zurückgegeben werden soll, wenn eine Übereinstimmung gefunden wird. Excel übernimmt die Suche automatisch – für jede Zeile, jedes Mal, wenn sich die Daten ändern.

=XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found])

Exakte Übereinstimmung

Eine exakte Übereinstimmung erfordert, dass der Suchwert genau mit einem Wert in der Referenztabelle übereinstimmt — dies ist die Standardeinstellung bei XLOOKUP.

Ungefähre Übereinstimmung

Eine ungefähre Übereinstimmung findet den nächstgelegenen Wert, der den Suchwert nicht überschreitet, und wird für gestufte Systeme wie Steuerklassen oder Rabattgrenzen verwendet.

Im XLOOKUP wird die ungefähre Übereinstimmung durch das Argument match_mode gesteuert – das vierte optionale Argument nach if_not_found. Wenn es auf -1 gesetzt wird, bedeutet das für Excel: "Wenn kein exakter Treffer gefunden wird, gib stattdessen den nächstkleineren Wert zurück."

=XLOOKUP(lookup_value; lookup_array; return_array; [if_not_found]; -1)

VLOOKUP

VLOOKUP ist immer noch in Millionen von Tabellenkalkulationen vorhanden und wird Ihnen regelmäßig begegnen. Die Hauptbeschränkung ist struktureller Natur: Der Suchwert muss immer in der ersten Spalte des Tabellenbereichs stehen, und die Rückgabe erfolgt über die Spaltenindexnummer. Wird eine Spalte zwischen Such- und Rückgabespalte eingefügt, stimmt die Indexnummer nicht mehr — VLOOKUP liefert dann ohne Warnung falsche Daten. XLOOKUP verweist direkt auf die Rückgabespalte und ist daher gegen dieses Problem immun.

=VLOOKUP(search_key; range; index; [is_sorted])

Aufgabe

1. Monatliches Budget nach Kategorie abrufen

Referenztabelle unterhalb des Zusammenfassungsbereichs mit den Spalten Category und Monthly Budget erstellen.

Folgende Werte eintragen:

• Rent — 1,500.00
• Groceries — 400.00
• Utilities — 250.00
• Transport — 200.00
• Dining — 150.00

Diese Werte als Währung formatieren.

Im Ausgabentabelle das Budget für jede Zeile abrufen mit: =XLOOKUP(B9;I12:I20;J12:J20)

Dadurch wird das Budget für jede Kategorie zurückgegeben.

2. Fehlende Kategorien behandeln

Formel aktualisieren, um Fehler zu vermeiden, wenn eine Kategorie nicht gefunden wird: =XLOOKUP(B9;I12:I20;J12:J20;"No budget defined")

Dadurch bleibt das Tabellenblatt übersichtlich und fehlende Zuordnungen werden hervorgehoben.

3. Nachschlagetabelle fixieren

Nachschlagebereiche mit F4 fixieren, damit sie beim Kopieren der Formel nicht verschoben werden.

4. Ungefähre Übereinstimmung für Rabattstufen anwenden

Neue Tabelle mit den Spalten Spending thresholds und Discount values erstellen.

Folgende Werte eintragen:

• 0 — 0
• 500 — 5
• 1000 — 10
• 2000 — 20

Rabattwerte als Prozentsätze formatieren.

Anschließend die Rabattstufe für jede Ausgabe berechnen mit: =XLOOKUP(D9;12:M16;N12:N16;"No tier";-1)