This hub covers every major Excel formula topic with step-by-step tutorials, real examples, and honest trade-offs. It spans classic lookup functions like VLOOKUP and INDEX MATCH, the modern dynamic array functions (GROUPBY, PIVOTBY, MAP, SCAN, REDUCE), LAMBDA for reusable logic, and the productivity skills around them — pivot tables, Power Query, Power Pivot, dashboards, and error debugging.
Start with the lookup section if you are comparing VLOOKUP and XLOOKUP. Jump to the dynamic array section if you are on Microsoft 365 and want to replace pivot tables with formulas. Use the debugging section when formulas break in production.
Every linked post is written from hands-on workshop experience — these are the same techniques taught in corporate Excel training sessions delivered to teams across India.
Key takeaways
- XLOOKUP replaces VLOOKUP in most cases but INDEX MATCH still wins on multi-criteria and non-standard layouts.
- Dynamic array functions (GROUPBY, PIVOTBY, MAP, SCAN, REDUCE) let you rebuild pivot-style reports as formulas that auto-update.
- LAMBDA turns repeated formula logic into named, reusable functions — the closest Excel gets to programming.
- Most #SPILL! errors come from obstructed ranges, not formula logic — clearing the spill range fixes them faster than rewriting the formula.
- Excel Tables with structured references are more maintainable than A1:Z100 ranges in every practical workbook.
Lookup Functions
- VLOOKUP vs XLOOKUP: Differences and When to Use Each
The definitive comparison with version requirements.
- How to Use INDEX MATCH in Excel with Multiple Criteria
Multi-criteria lookups that VLOOKUP cannot handle.
- XMATCH in Excel: Smarter Lookups, Reverse Searches, and Binary Search Use Cases
XMATCH's advantages over MATCH.
Dynamic Array Functions
- GROUPBY Function in Excel: Formula-Based Summaries Without a Pivot Table
Replacing pivot tables with GROUPBY formulas.
- PIVOTBY Function in Excel: Build Pivot-Style Reports With a Formula
Cross-tab reports as a single formula.
- GROUPBY vs PivotTable in Excel: When Formula Summaries Beat the Ribbon
When each approach wins.
- MAP, SCAN, and REDUCE in Excel: Modern Array Logic for Power Users
Functional array logic in modern Excel.
- CHOOSECOLS, CHOOSEROWS, TAKE, and DROP in Excel: Slice Data Faster
Data slicing with modern array functions.
Advanced and Reusable Formulas
- LET and LAMBDA in Excel: Turn Repeated Formulas Into Reusable Logic
LET for readability, LAMBDA for reusability.
- 15 Excel Formulas That Save Hours of Manual Work (With Examples)
The essential advanced formula library.
- PY Function in Excel: What It Is, How It Works, and When to Use It
Python formulas inside Excel cells.
- Python in Excel for Beginners: The First 10 Things Worth Learning
Python in Excel starting point.
Pivot Tables and Data Analysis
- How to Create a Pivot Table in Excel Step by Step
Pivot tables from scratch to advanced.
- How to Use Power Query in Excel to Automate Data Cleaning
Data transformation without formulas.
- How to Use Power Pivot in Excel to Analyse Millions of Rows
Large-scale data analysis in Excel.
- How to Build an Interactive Dashboard in Excel (No VBA)
Dashboards with pure formulas and slicers.
- How to Make Professional Charts in Excel (Step-by-Step Guide)
Chart selection and formatting.
Debugging and Error Fixing
- How to Fix #SPILL! Errors in Excel and Prevent Them in Dynamic Array Models
Spill error causes and fixes.
- How to Audit Formulas in Excel: Trace Precedents, Dependents, and Error Sources
Formula debugging and tracing.
- How to Clean Messy Data in Excel: Step-by-Step Guide
Cleaning imported data at scale.
Productivity and Best Practices
- Excel Tables Best Practices: Structured References, Growth, and Cleaner Models
Why every data range should be a Table.
- 30 Excel Keyboard Shortcuts That Save Hours Every Week
The shortcut set that matters.
- Excel vs Google Sheets: Which Is Better for You in 2026?
Platform comparison for decision makers.