Following are the 5 Dynamic Array formulas which replace old Excel formulas:
- FILTER: This function can replace the IF, AND, and OR formulas, which are used to filter data based on multiple criteria. FILTER allows you to specify multiple criteria and returns an array of values that meet all of the criteria, whereas IF, AND, and OR formulas require you to nest multiple formulas to achieve the same result.
- UNIQUE: This function can replace the INDEX, MATCH, and COUNTIF formulas, which are used to extract unique values from a range. UNIQUE returns an array of unique values in a range, whereas INDEX, MATCH, and COUNTIF require you to use multiple formulas to achieve the same result.
- SORT: This function can replace the UI SORT and SORTBY formulas, which are used to sort data in a range. SORT allows you to sort data by multiple columns or criteria, whereas SORT and SORTBY only allow you to sort by one column or criterion.
- SEQUENCE: This function can replace the ROW, ROWS, and INDEX formulas, which are used to generate sequential numbers or to return the number of rows in a range. SEQUENCE allows you to generate a sequence of numbers with a specified step size and start value, whereas ROW, ROWS, and INDEX require you to use multiple formulas to achieve the same result.
- CONCAT: This function can replace the CONCATENATE and “&” operator, which are used to combine text from multiple cells or ranges. CONCAT allows you to combine text from multiple cells or ranges with a single formula, whereas CONCATENATE and the “&” operator require you to use multiple formulas to achieve the same result. Also, refer to TEXTJOIN.