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:

Custom Date Formats in Excel

In addition to the preset options available for formatting dates in Excel, for specific use cases, additional custom date formats can also be created (coded). All rules for the creation of custom number formats apply. Custom date formats are created and stored only in the active workbook, and they affect only the way a number is displayed and do not affect the underlying value of the number.   Pre-defined formats for date and time in Excel are available under the

Format Numbers as Thousands, Millions, or Billions in Excel

You will sometimes need to report some of your figures in thousands, millions, or billions. This is typically accomplished by dividing the original figures by thousands, millions, or billions, and can be time and/or resource consuming process. However, Excel also offers an alternative in custom number formats. In Excel, numbers can be formatted to be shown as thousands, millions, or billions without any sort of calculations or modifications to the underlying numbers.   Custom number formats are created (modified from