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 …
Tag: SUM function
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: …
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 …
FILTER function
The FILTER function replicates and extends Excel AutoFilter filtering functionality in formula form. The FILTER function allows us to filter a range of data based on defined criteria. By default, the FILTER 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 FILTER function is as follows: = FILTER ( array ; include ; [if_empty] ) …
XLOOKUP function
We’ve previously covered how we can combine INDEX and MATCH functions in order to “look up” data. The XLOOKUP function works much the same way as the INDEX MATCH combination. It is intended to replace (still wildly used) legacy functions with outdated syntax and limited capabilities, such as VLOOKUP, HLOOKUP, and LOOKUP, while retaining accessibility. The XLOOKUP function searches a range or an array for a match and returns the corresponding item from a second range or array. The …
Excel Tables
What are Excel Tables? Excel Tables, or just Tables, are tables superimposed over regular cells. Excel Tables are assumed to be containing structured data. Both tables and individual table columns are named and intended to be referenced in formulas by those names. As they are typically used to load structured data from an external source into Excel, and that external data is typically accessed from Excel using the Data ribbon and then loaded into Table, they are also sometimes called …
Combining INDIRECT with ADDRESS
We’ve previously covered how we can use any function to generate a text that could be a valid cell reference, both the column and row parts of the address, and then use the INDIRECT function to convert that text to a cell reference. This process can be greatly enhanced by the ADDRESS function. The ADDRESS function returns a text string that represents the address of a particular cell. Row, column, type of reference (locked or absolute), reference style (A1 …
INDIRECT function
The INDIRECT function returns the reference specified by a text string. The INDIRECT function syntax is as follows: = INDIRECT ( reference_text ; reference_style ) Reference text can be any text that the function can “convert” to a cell reference. Reference style represents the style of referencing cells in Excel. If omitted or TRUE, the reference text is interpreted as an A1 style reference, i.e., the default style of referencing cells in Excel. If FALSE, the reference text is interpreted …
Top 10 lists in Excel
When analyzing large amounts of data in Excel, often the best approach is to retrieve the top (10) values. Consider the following example: This table contains all of the invoices from January 29th. There are 904 invoices for that day. The values in the invoice column are unique, as those are invoice numbers. The values in the user column are not unique, as some users have multiple invoices on that day, and the values in the team column are not …