Tag: FILTER function Excel

🆕 New Functions Introduced in Excel 2021 — Explained with Usefulness

Microsoft Excel 2021 brought a significant upgrade by introducing several dynamic array functions and smarter lookup and filtering tools. These functions were previously exclusive to Microsoft 365 users but are now part of the perpetual Excel 2021 version, helping users automate tasks, reduce formula complexity, and improve data analysis.

---

🔍 1. XLOOKUP

Purpose: To search for a value in a column or row and return a corresponding value from another column or row.

Syntax:

excelCopyEdit=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Usefulness:

• Replaces older functions like VLOOKUP, HLOOKUP, and even INDEX + MATCH.
• No need to worry about column numbers or data being sorted.
• Supports exact, approximate, and wildcard matching.
• Works both vertically and horizontally.

Example:

excelCopyEdit=XLOOKUP("Apple", A2:A100, B2:B100, "Not Found")

This looks for “Apple” in column A and returns the value from column B in the same row.

---

🔢 2. XMATCH

Purpose: Returns the relative position of a value within a range, similar to MATCH but more versatile.

Syntax:

excelCopyEdit=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])

Usefulness:

• Supports reverse search and wildcard matching.
• Useful for locating the position of a value in arrays, which can then be used with INDEX or CHOOSE.

Example:

excelCopyEdit=XMATCH(50, A1:A10)

Returns the position of 50 in the range A1:A10.

---

🧹 3. FILTER

Purpose: Extracts only the data that meets certain criteria from a range.

Syntax:

excelCopyEdit=FILTER(array, include, [if_empty])

Usefulness:

• Dynamically displays filtered data in a separate area.
• Great for dashboards, reporting, or conditional data extraction.
• Automatically expands or contracts based on the filter results.

Example:

excelCopyEdit=FILTER(A2:B10, B2:B10="North")

Returns only rows where the second column has “North” as the value.

---

🔢 4. SORT

Purpose: Sorts a range or array in ascending or descending order.

Syntax:

excelCopyEdit=SORT(array, [sort_index], [sort_order], [by_col])

Usefulness:

• Unlike traditional sort, it doesn’t affect the original data.
• Automatically updates when the source data changes.
• Useful in dynamic dashboards and data tables.

Example:

excelCopyEdit=SORT(A2:B10, 2, -1)

Sorts the range A2:B10 by the second column in descending order.

---

🔢 5. SORTBY

Purpose: Sorts a range or array based on the values in another array.

Syntax:

excelCopyEdit=SORTBY(array, by_array1, [sort_order1], ...)

Usefulness:

• More flexible than SORT, as it lets you sort by related fields not in the output.
• Excellent for sorting one table based on another column or lookup.

Example:

excelCopyEdit=SORTBY(A2:B10, C2:C10, 1)

Sorts A2:B10 based on values in C2:C10 in ascending order.

---

🔄 6. UNIQUE

Purpose: Extracts a list of unique values from a range or array.

Syntax:

excelCopyEdit=UNIQUE(array, [by_col], [exactly_once])

Usefulness:

• Removes duplicates quickly and dynamically.
• Especially useful for creating drop-down lists or summary views.

Example:

excelCopyEdit=UNIQUE(A2:A100)

Returns a list of unique values from column A.

---

🔢 7. SEQUENCE

Purpose: Generates a list of sequential numbers in an array format.

Syntax:

excelCopyEdit=SEQUENCE(rows, [columns], [start], [step])

Usefulness:

• Helpful for creating index numbers, date sequences, or testing data structures.
• Can generate both 1D and 2D arrays.

Example:

excelCopyEdit=SEQUENCE(5,1,10,2)

Generates 5 numbers starting from 10, increasing by 2 (i.e., 10, 12, 14, 16, 18).

---

🎲 8. RANDARRAY

Purpose: Returns an array of random numbers.

Syntax:

excelCopyEdit=RANDARRAY([rows], [columns], [min], [max], [integer])

Usefulness:

• Generate sample data for testing or simulations.
• Can return decimal or whole numbers.
• Recalculates with every workbook change unless frozen with F9.

Example:

excelCopyEdit=RANDARRAY(5, 2, 1, 100, TRUE)

Generates a 5×2 array of random whole numbers between 1 and 100.

---

📦 9. LET

Purpose: Assigns names to calculation results to reuse within a formula, improving performance and readability.

Syntax:

excelCopyEdit=LET(name1, value1, calculation)

Usefulness:

• Makes complex formulas more readable.
• Optimizes performance by computing once and reusing.

Example:

excelCopyEdit=LET(x, A1+10, x*2)

Calculates A1 + 10 once, stores it as x, and then returns x * 2.

---

📘 Bonus: Other Useful Functions from Excel 2019 Now Common in Excel 2021

While not brand-new to Excel 2021, the following were improved and widely adopted:

• TEXTJOIN – Joins multiple text items with a delimiter.
• IFS – Replaces complex nested IF formulas.
• SWITCH – Easier alternative to multiple IF statements for fixed-value cases.

---

⚠️ Not Available in Excel 2021

Some advanced functions are only available in Microsoft 365 (not Excel 2021), such as:

• TEXTSPLIT
• DROP, TAKE, VSTACK, HSTACK
• WRAPROWS, WRAPCOLS
• TOCOL, TOROW
• MAP, REDUCE, SCAN, BYROW, BYCOL

These are part of the Lambda family and advanced array manipulations introduced later in Excel 365.

---