Formula

To find the first or last positive value in a list (row or column), use the XLOOKUP, SIGN, INDEX, or MATCH functions.

To sum multiple rows or columns based on a lookup value, you can use the SUM function with the XLOOKUP function. Even if we have a definitive XLOOKUP guide, we learn something special daily. Today’s example will be on how to find the matching value in a table and sum multiple rows or columns. The … Read more

Combine the LET and IF functions with XLOOKUP, and XLOOKUP will return blank if the lookup result is blank.

XLOOKUP can’t return all matches; this feature is currently not supported. Here is the workaround with the Excel FILTER function.

You can use the XLOOKUP function and a sorted list to look up values between two numerical values and return a corresponding result.

Learn how to create a nested XLOOKUP formula if you need two lookup values to find the matching record in Excel. We will show you how to use XLOOKUP to replace the INDEX and MATCH combination when you need a two-way lookup. For example, sometimes, we need two inputs to find the matching record in … Read more

This guide will explain how to find the 2nd match or the nth occurrence in an array using the XLOOKUP function. There is a common problem with all lookup functions in Excel: how to skip the exact match and get the 2nd, 3rd, or nth match. We will use the XLOOKUP function in the example … Read more

Learn how to lookup the first negative value in a range using the XLOOKUP function or INDEX and MATCH functions. Generic Formula Here is the generic formula to lookup the first negative value in a range: In the formula, “rng” is the range where we find the first negative value; enter the formula in cell … Read more

To lookup and get the first text value in a range, use the XLOOKUP function with a wildcard character match mode. From time to time, you want to find and extract the first text value from a range that contains numbers and text values. Use the XLOOKUP function and apply the wildcard character (*) as … Read more

This guide will explain how to count cells that contain numbers in a range using the Excel COUNT function. In the example, we want to count all cells in a range where the cells are numbers. There are two common methods: using the COUNT function or applying the SUMPRODUCT function. If you want to learn … Read more

This tutorial will explain how to highlight every other row in Excel. To improve your spreadsheet’s readability, shade alternate rows! Working with large data tables and organizing ranges in Excel is sometimes not too easy. In this Excel guide, I will cover the following topics: At first, you’ll learn how to highlight every other row … Read more

To count unique values in a list or range, use the UNIQUE function and configure the function’s third argument to TRUE. In Microsoft365 for Excel, you can create powerful formulas. Today’s guide will be on how to count unique and distinct values in a range using COUNT and UNIQUE functions. Differences between unique values and … Read more

To count cells that contain values less than a given number in a range, use the generic formula based on the COUNTIF function. Generic Excel Formula to count the number of cells with values less than a given number: In the picture above, “range” is the range of cells that contains numbers, and “number” is … Read more

This tutorial will explain how to sum cells that contain formulas in Excel using the SUMPRODUCT and ISFORMULA functions. Generic formula to sum results where the cell contains formulas only: How to SUM cells that contain formulas In the example, we have a sales table that contains orders and sales for a given location. Some … Read more

Learn how to convert boolean TRUE or FALSE to numbers (0 or 1) using the Excel N function or the double negative method. When working with complex Excel formulas, you must know how to convert TRUE and FALSE values to the numeric equivalents 1 and 0. If you are unfamiliar with boolean logic, read this … Read more

To count cells that contain more than n characters, use a formula based on three Excel functions: SUMPRODUCT, LEN, and N. The general formula to count cells over n characters: In the example, n=70 in cell F2, we find the cells which contain more than 70 characters. The result is 3 since three cells in … Read more