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:

ROW & COLUMN functions

In Excel, there are specific use cases where an exact row (column) number, or an exact number of rows (columns), are necessary for calculations.   The ROW function returns the row number of a reference. The ROW function syntax is as follows: = ROW ( reference ) If reference is omitted, the number of the row in which the formula appears is returned.   The COLUMN function returns the column number of a reference. The COLUMN function syntax is as

Generate a random date and/or time

Generating random dates and/or timestamps in Excel is the same as generating any other random number. Keep in mind that no matter how dates and/or timestamps are formatted and possibly edited, the underlying values saved in cells are decimal numbers. Number 1, formatted as a date, will represent January 1, 1900. 44.916 will represent December 21, 2022. 44.916,888111 will be representing December 21, 2022, at 9:18 PM.   Date and/or time stamps should be generated between two dates (within a

Cleaning up text in Excel: CLEAN, TRIM, and SUBSTITUTE

In Excel, you will occasionally deal with text fields containing line breaks, multiple spaces, hyphenation, currency and letterlike symbols, etc. This is common when dealing with unrestricted user-inputted data and can cause issues. In particular, these kinds of values will be hard to connect with related values without cleanup. When we MATCH those values with related data tables, errors are bound to occur.   Basic cleanup approaches include manual replacement of all of the unnecessary characters with a desired or