🔎 Lookup & Reference

The bread and butter of data analysis. Learn how to connect datasets, find values, and dynamically reference cells.

The most famous function. Scans the first column vertically to find a match. Essential knowledge.

The modern king. Looks left or right, handles errors, and defaults to exact match.

🧩 Power Pair

03. INDEX & MATCH

Before XLOOKUP, this was the pro way to look left. Separate the "Position" finding from the "Value" getting.

🌊 Dynamic Array

Return multiple results at once. Extract all rows that match a specific criteria (e.g., "Department = Sales").

✨ Dynamic Array

Instantly extract a distinct list of values from a column, removing all duplicates automatically.

🔃 Dynamic Array

06. SORT / SORTBY

Reorder your data dynamically without touching the "Sort" button in the menu.

The GPS navigator. Move a specific number of rows and columns from a starting point.

Convert text string into a valid cell reference. Crucial for dependent dropdowns.

Pick a value from a list based on an index number (1, 2, 3...). Simple but effective.

The original vector lookup. Great for approximate matches on sorted data (like grading scales).

⬅ Back to Excel Menu