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
The Excel SWITCH function evaluates an expression against a list of values and returns a result corresponding to the first matching value. If there is no match, SWITCH returns an optional, user-defined value. Syntax, Arguments, return value The SWITCH function matches multiple values, returns the first match, and appears in Excel 2019. Syntax: =SWITCH (expression, … Read more
The Excel IFERROR function returns a user-defined, custom value when the embedded formula generates an error. Otherwise, it returns the standard formula result. Using IFERROR is an effective method to identify and handle formula errors. However, you must use more complex statements (for example, the IF function) without error-handling functions. The purpose of using IFERROR … Read more
The Excel TODAY function returns the current date and auto-updates the value when changing or opening the Workbook. Syntax and Arguments The syntax is simple: =TODAY() The function does not use argument. Using the TODAY function in Excel By default, the TODAY function gets the current date and returns the standard Excel date as a … Read more
The Excel SEQUENCE function is a part of dynamic array functions, and it generates a list of sequential numbers in an array. Using the SEQUENCE function arguments, you can create two types of arrays: one-dimensional (single row or single column) and two-dimensional. SEQUENCE function is only available for Microsoft 365 users. The function returns an … Read more
The Excel AREAS function counts the number of areas in a given reference. For example, =AREAS((A1:B1, A2:B2, A3:B3)) returns 3. How to use the Excel AREAS function The purpose of the functions is to return the number of areas in a reference. The function is available in Excel 2003 and above. Adjacent ranges are separate … Read more
The Excel IFNA function returns a user-defined value if a formula results in an #N/A (value not found) error. IFNA is a part of the logical functions and is compatible with Excel 2013 and above. How to use the IFNA Function in Excel? As stated above, the IFNA function’s main goal is to replace the … Read more
A function argument is a comma-separated input inside the function. Learn how to use the Excel Function Dialog Box to edit them. For example, the XLOOKUP function takes six arguments: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) If you look at the function, three arguments are required, and another three are optional. For example, in the … Read more
The Excel FALSE function is a compatibility function and returns with the FALSE output. The FALSE Formula in Excel requires no arguments. The FALSE function’s purpose is to generate the FALSE logical value. How the FALSE function works The syntax is simple: =FALSE() Take a closer look at the function! It uses no arguments, like … Read more
The Excel AND function is one of the logical functions. Learn how to determine if all conditions in a test are TRUE. Using Excel AND Function Here is how the AND function works: The AND function tests multiple conditions with AND and returns TRUE if all arguments evaluate TRUE; FALSE if not. The goal of … Read more
The TRANSPOSE function swaps a vertical range to a horizontal range and switches rows to columns but keeps the data structure.