📊 Excel 365: Extract Data Between Sheets Based on Criteria
lang:en
🚀 Filter and Transfer Data Between Excel Sheets with Ease
Excel 365 provides powerful functions to extract data from one sheet to another based on specific criteria, eliminating the need for manual copy-paste and saving precious time. Here are the key functions:
⚡ Main Functions
- 🔍 FILTER — select rows that match a specific criterion
- 📦 INDEX + MATCH — combine lookup and return of matching data
- 🧠 VLOOKUP — search for values in a table and return related data
- 🎯 XLOOKUP — modern and more flexible version of VLOOKUP
- 💻 DYNAMIC FILTER — create a live view of filtered data that auto-updates
- 📊 UNION — combine results from multiple ranges or sheets
🐍 How It Works
- 📦 Define the filter criterion (e.g., column = specific value)
- 🔗 Apply the formula
=FILTER(table; criteria; "no data")to select rows - ⚡ The result spills automatically into the destination sheet
- 🔄 Any change in the source data reflects in real time
🏆 Advantages
- 🚀 No copy-paste — data transfers automatically
- ⚡ Live update — change the criterion, change the result
- 🧠 No macros needed — only native Excel formulas
- 📊 Scalable — works with hundreds or thousands of rows
👉 Practical Example
To extract all "Sales" department data from the Data sheet to the Summary sheet:
=FILTER(Data!A2:E100; Data!B2:B100="Sales"; "No data found")
👉 Video tutorial: https://www.youtube.com/shorts/GTixn5IJAlM