Dynamic array functions in Excel are powerful tools that allow you to work with arrays of data more efficiently. Below are 20 examples of dynamic array functions, each including sample data and explanations. These functions include SORT, FILTER, UNIQUE, SEQUENCE, RANDARRAY, XLOOKUP, and more.
1. SORT Function
Sorts a range or array in ascending or descending order.
Data:
| A | B |
|---|---|
| 10 | X |
| 30 | Y |
| 20 | Z |
Formula:
=SORT(A1:B3, 1, 1)
Result:
| 10 | X |
| 20 | Z |
| 30 | Y |
2. SORTBY Function
Sorts a range based on another range or array.
Data:
| A | B |
|---|---|
| 10 | X |
| 30 | Y |
| 20 | Z |
Formula:
=SORTBY(A1:B3, B1:B3, 1)
Result:
| 10 | X |
| 30 | Y |
| 20 | Z |
3. FILTER Function
Filters a range based on criteria.
Data:
| A | B |
|---|---|
| 10 | X |
| 30 | Y |
| 20 | Z |
Formula:
=FILTER(A1:B3, A1:A3 > 15)
Result:
| 30 | Y |
| 20 | Z |
4. UNIQUE Function
Extracts unique values from a range.
Data:
| A |
|---|
| 10 |
| 20 |
| 10 |
| 30 |
Formula:
=UNIQUE(A1:A4)
Result:
| 10 |
| 20 |
| 30 |
5. SEQUENCE Function
Generates a sequence of numbers.
Formula:
=SEQUENCE(5)
Result:
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
6. RANDARRAY Function
Generates an array of random numbers.
Formula:
=RANDARRAY(3, 2)
Result:
| 0.12 | 0.45 |
| 0.67 | 0.89 |
| 0.34 | 0.56 |
7. XLOOKUP Function
Looks up a value in a range and returns a corresponding value.
Data:
| A | B |
|---|---|
| 10 | X |
| 20 | Y |
| 30 | Z |
Formula:
=XLOOKUP(20, A1:A3, B1:B3)
Result:
| Y |
8. TRANSPOSE Function
Transposes a range (rows to columns or vice versa).
Data:
| A | B |
|---|---|
| 10 | 20 |
| 30 | 40 |
Formula:
=TRANSPOSE(A1:B2)
Result:
| 10 | 30 |
| 20 | 40 |
9. SORT and FILTER Combined
Sorts and filters data simultaneously.
Data:
| A | B |
|---|---|
| 10 | X |
| 30 | Y |
| 20 | Z |
Formula:
=SORT(FILTER(A1:B3, A1:A3 > 15), 1, 1)
Result:
| 20 | Z |
| 30 | Y |
10. UNIQUE and SORT Combined
Extracts unique values and sorts them.
Data:
| A |
|---|
| 10 |
| 20 |
| 10 |
| 30 |
Formula:
=SORT(UNIQUE(A1:A4))
Result:
| 10 |
| 20 |
| 30 |
11. SEQUENCE for Dates
Generates a sequence of dates.
Formula:
=SEQUENCE(5, 1, DATE(2023, 10, 1), 1)
Result:
| 10/1/2023 |
| 10/2/2023 |
| 10/3/2023 |
| 10/4/2023 |
| 10/5/2023 |
12. FILTER with Multiple Criteria
Filters data based on multiple conditions.
Data:
| A | B |
|---|---|
| 10 | X |
| 30 | Y |
| 20 | Z |
Formula:
=FILTER(A1:B3, (A1:A3 > 15) * (B1:B3 = "Y"))
Result:
| 30 | Y |
13. UNIQUE with Text Data
Extracts unique text values.
Data:
| A |
|---|
| X |
| Y |
| X |
| Z |
Formula:
=UNIQUE(A1:A4)
Result:
| X |
| Y |
| Z |
14. RANDARRAY for Random Integers
Generates random integers.
Formula:
=RANDARRAY(3, 2, 1, 100, TRUE)
Result:
| 45 | 67 |
| 12 | 89 |
| 34 | 56 |
15. XLOOKUP with Wildcard
Looks up a value using a wildcard.
Data:
| A | B |
|---|---|
| Apple | 10 |
| Banana | 20 |
| Cherry | 30 |
Formula:
=XLOOKUP("Ban*", A1:A3, B1:B3)Result:
| 20 |
16. TRANSPOSE with SEQUENCE
Transposes a sequence of numbers.
Formula:
=TRANSPOSE(SEQUENCE(1, 5))
Result:
| 1 | 2 | 3 | 4 | 5 |
17. FILTER with OR Logic
Filters data using OR logic.
Data:
| A | B |
|---|---|
| 10 | X |
| 30 | Y |
| 20 | Z |
Formula:
=FILTER(A1:B3, (A1:A3 = 10) + (B1:B3 = "Z"))
Result:
| 10 | X |
| 20 | Z |
18. UNIQUE with SORTBY
Extracts unique values and sorts by another column.
Data:
| A | B |
|---|---|
| 10 | X |
| 20 | Z |
| 10 | Y |
Formula:
=SORTBY(UNIQUE(A1:A3), B1:B3, 1)
Result:
| 10 |
| 20 |
19. SEQUENCE for Matrix
Generates a matrix of numbers.
Formula:
=SEQUENCE(3, 3)
Result:
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 7 | 8 | 9 |
20. XLOOKUP with Multiple Results
Returns multiple results for a lookup.
Data:
| A | B |
|---|---|
| Apple | 10 |
| Banana | 20 |
| Apple | 30 |
Formula:
=XLOOKUP("Apple", A1:A3, B1:B3, "", 0, 1)Result:
| 10 |
| 30 |
These examples demonstrate the versatility of dynamic array functions in Excel. Let me know if you'd like further clarification or additional examples!
No comments:
Post a Comment