The XLFN prefix in Excel formulas indicates that the current Microsoft Excel version does not support the given function. If you open a workbook that you created an earlier Excel version, some formulas may show an _xlfn prefix at the starting position of the function. The prefix is an alert, and Excel will indicate that … Read more
The Excel XOR function returns a logical “exclusive OR” of all arguments and provides TRUE or FALSE output. If you use two logical tests, XOR will return TRUE if either logical test evaluates to TRUE and return FALSE if both logical values are TRUE. XOR returns FALSE if neither logical is TRUE. Syntax, Arguments, and … Read more
The Excel OR Function returns TRUE if at least one logical test is evaluated to TRUE and returns FALSE if all logical tests are FALSE. This tutorial is a part of our definitive guide about Excel functions. For example, to apply a logical test B2 for either “blue” or “yellow”, use: You can use the … Read more
The TOCOL function flattens an array or range into a single column. By default, keep all values and scan left to right. The function allows for the functionality we were missing from the FLATTEN function and works even more smoothly. Until now, there was no native FLATTEN function support in Microsoft Excel; it was available … Read more
The Excel NOT function returns a reversed boolean value of a given logical or boolean value. NOT converts TRUE to FALSE and vice versa. Syntax and arguments, return value The syntax is simple: Argument: The function uses one argument: “Logical”, the value that the function will convert to TRUE or FALSE. Ensure you use a … Read more
The Excel IFS function performs multiple logical tests and allows you to create shorter, easy-to-read formulas. In this tutorial – from the ground up, we will show you how the function works through practical examples. What is the IFS function in Excel? Excel IFS function can run multiple tests and return the user-defined value when … Read more
The Excel IF function uses a logical test and returns two values, one result for TRUE and another for FALSE. Use the IF function to perform a logical test and the greater than logical operator to check the condition. For example, if the value in cell A1 is greater than 100, the result is TRUE; … Read more
The Excel UNIQUE function extracts unique values from a range and returns a list of unique values in a list or range. In a nutshell, The UNIQUE function can handle various data types. For example, you can use text, numbers, and date values and return an array of unique values. The function is available for … Read more
Learn how to construct a case-sensitive exact match by combining the XLOOKUP and EXACT functions. The XLOOKUP function is not case-sensitive by default. We will use “Bob” as a lookup value in the example. XLOOKUP returns with “BOB,” which does not meet our criteria. So, if you want to construct a case-sensitive lookup, you need … Read more
The Excel FILTER function filters range with given criteria and extracts matching records from an array of filtered values. FILTER is one of the dynamic array functions available in Microsoft 365. Syntax and Arguments Syntax: =FILTER(array, include, [if_empty]) Arguments: How to use the FILTER function Using the FILTER function, you can extract a range of … Read more
The Excel HLOOKUP function searches for a certain value in a row and returns a value from a different row in the same column. Tip: If you are using Excel for Microsoft 365, try the XLOOKUP function, an improved version of Excel lookup functions that work in any direction and returns exact matches by default … Read more
The Excel TRUE function returns the Boolean value TRUE. The function provides compatibility with other spreadsheet programs. In most cases, using the TRUE function is unnecessary if you are working in Excel. The function’s only purpose is to generate the logical value TRUE. Syntax, Arguments The syntax is simple, and the TRUE() function does not … Read more