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 …
Author: administrator@dailyexcel.net
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] ) …
Lookup the 2nd, the 3rd, or the nth value
Both the MATCH function and the XLOOKUP function look only for the first (last) available match in the array. This is perfectly reasonable in most cases, as we are expecting to deal with unique identifiers when looking up data. However, sometimes we will have to look up the second, third, fourth, or nth value. Consider the following example: Our table contains a list of contract numbers in column B and contact email addresses in column A. Contract numbers are …
Lookup with multiple criteria
If we have to look up items in tables where we can’t use unique identifiers (there are no names located in a table column that contains data in all of the rows, and that data is non-repeating), we will probably have to resort to matching multiple criteria in multiple columns. Consider the following example: Each year, multiple game tournaments are held in repeating cities, organized by various repeating organizers. Items in the column City are not unique, nor are …
Combining SUMIFS with XLOOKUP
Using the SUMIFS function, we can sum all of the values in a defined column (or row) that meet one or more criteria. When SUMIFS is combined with XLOOKUP, that sum range doesn’t have to be defined anymore; it is now rather specified in the function arguments. By combining SUMIFS with XLOOKUP, we can then sum all of the values that meet multiple criteria in different rows and columns and do this in a simple way, avoiding complex and resource-intensive …
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 …
IFS function
We’ve previously covered how we can nest one or more IF functions inside the IF function. This enables us to perform multiple different actions depending on the tested condition, but it can also make the whole formula longer than necessary and hard to read/understand/correct. The IFS function can take the place of multiple nested IF statements and is, in principle, easier to read. The syntax of the IFS function is as follows: = IFS ( logical_test ; value_if_true ; …
IFERROR and IFNA functions
We’ve previously covered how we can nest the ISERROR function, the ISERR function, or the ISNA function inside the IF function in order to check for errors and perform appropriate actions if errors occur. While effective, this can make the whole formula longer than necessary and hard to read/understand/correct. The specialized IFERROR and IFNA functions with simpler syntax can take the place of the IF ISERROR and IF ISNA combinations. The IFERROR function checks whether the cell or formula …
Rounding Numbers in Excel
Excel supports several similar but different functions that round numbers. Those functions will enable us to perform the following operations: * negative midpoint numbers are rounded away from zero, and not properly half-rounded up (towards positive) **the rounding direction is undefined for midpoint numbers, both rounding up and down can happen The ROUND function rounds a number to a specified number of digits. The ROUND function syntax is as follows: = ROUND ( number ; num_digits ) The ROUND …
TEXT function
In Excel, the TEXT function performs two operations: converts a number to text, at the same time, formats that number in a specified way. The TEXT function syntax is as follows: = TEXT ( value ; “format_text” ) Value can be any number, either inputted into the function or referenced. Format text nested inside quotation marks is based on the number format codes used in Excel. Excel number format codes can be copied from the Format Cells pop-up: …