In Excel, we can use the INDIRECT function to define arrays that are arguments for other functions, provided that those arrays are referencing the current workbook. Consider the following example: We want to retrieve values from the sheets adjacent to Sheet1, but still in the same workbook. Instead of writing a specific formula for every sheet, we will write a single formula that will link the data from all of the worksheets. We can retrieve our data using …
How to look up the next smaller item or the next larger item
The XLOOKUP function searches a range or an array for a match and returns the corresponding item from a second range or array. The syntax of the XLOOKUP function is as follows: = XLOOKUP ( lookup_value ; lookup_array ; return_array ; if_not_found ; match_mode ; search_mode ) The lookup value can be either a text or a number, inputted directly into the function or as a cell reference. When looking up numbers, other than match type 0, exact match, …
How to use less than or greater than MATCH
We can use the INDEX function to designate a cell range (array) from which we want to retrieve data and, at the same time, use the MATCH function to specify the address of a cell in that cell range (array), i.e., row and column. The MATCH function returns the relative position of the item in the range, either a row or a column. The relative position is returned in the form of an integer number, such as 1, 2, …
Combining FILTER with XLOOKUP
The FILTER function allows us to filter a range of data based on criteria defined for specified corresponding arrays. By combining FILTER with XLOOKUP, that corresponding array and/or filtering criteria can be returned from (un)related tables. We can FILTER, and in the end, SUM, values based on criteria that are not present in the table we are returning values from. In the following example, we will show how to use XLOOKUP in order to return filtering criteria: This table …
Combining FILTER with INDEX MATCH
The FILTER function allows us to filter a range of data based on criteria defined for specified corresponding arrays. By combining FILTER with INDEX MATCH, that corresponding array and/or filtering criteria can be returned from (un)related tables. We can FILTER, and in the end, SUM, values based on criteria that are not present in the table we are returning values from. In the following example, we will show how to use INDEX MATCH in order to return filtering criteria: …
UNIQUE function
The UNIQUE function replicates and extends Excel’s Remove Duplicates feature in formula form. The UNIQUE function allows us to return a list of unique values in a list or range. By default, the UNIQUE function will return multiple values, which will be placed in the neighboring cells (to the bottom and/or to the right). I.e., we are dealing with dynamic array formulas here. The syntax of the UNIQUE function is as follows: = UNIQUE ( array ; [by_col] ; …
SORT & SORTBY functions
Two related sorting functions, SORT and SORTBY, replicate and extend Excel AutoFilter sorting functionality in formula form. The SORT function can be used to sort the contents of a range or array based on the values in that range or array, while the SORTBY function can be used to sort the contents of a range or array based on the values in the corresponding range or array, as illustrated here: By default, these functions will return multiple values, which will …
SUMIFS criteria: INDEX MATCH or XLOOKUP criteria
Using the SUMIFS function, we can sum all of the values in a defined column (or row) that meet one or more criteria. SUMIFS criteria can reference cells, contain values or text, contain logical tests, or contain formulas and functions. By nesting the INDEX MATCH combination or the XLOOKUP function as SUMIFS criteria, we can return values based on criteria that are not present in the table we are returning values from. Consider the following example: This table contains (repeating) …
Excessively large files in Excel: How to remove blank cells
In order to limit file sizes, Excel typically doesn’t store information about the whole spreadsheet (all available 1048476 rows and XFD columns); only the part of the worksheet that contains information is stored. However, “empty” cells may contain leftover formatting or comments, thus forcing Excel to save parts of the spreadsheet that contain no useful information. This can cause excessively or even extremely large file sizes, occasionally combined with printing and calculation time issues. In order to restore proper functionality …
SEQUENCE function
The SEQUENCE function generates a list of sequential numbers in an array. We can generate a list of sequential numbers in rows, columns, or both. By default, the SEQUENCE function will return multiple values, which will be placed in the neighboring cells (to the bottom and/or to the right). I.e., we are dealing with dynamic array formulas here. The syntax of the SEQUENCE function is as follows: =SEQUENCE ( rows ; [columns] ; [start] ; [step] ) Rows specifies …