Formula

Learn how to use the XLOOKUP function with Boolean expression (OR logic). To do that set the lookup value to 1. XLOOKUP is our favorite dynamic array function, and you can use it in various situations. Generic formula to use a boolean or logic In the example, our goal is to find and list all … Read more

Learn how to extract unique values from a range by using one or more criteria combined with the UNIQUE function and FILTER functions.

To get the name of the nth largest value in a range, use the XLOOKUP functions with the LARGE function together. In the example, we will use the LARGE function to calculate the lookup value for the formula based on the XLOOKUP function. The LARGE function uses simple logic to find the nth largest value … Read more

Learn how to get the last match in a list or a range using the ‘last to first’ search of the XLOOKUP function. XLOOKUP is a multipurpose function that enables you to perform advanced lookups. Today we will show you an easy example. Example: Get the last match using XLOOKUP In a nutshell: If you … Read more

Learn how to use XLOOKUP to return entire rows or columns as a lookup result without INDEX and MATCH functions. XLOOKUP is a real swiss-knife in Excel. One of the advantages of the function is that you can display the return value (lookup result) in entire rows and columns. However, if you are working with … Read more

Learn how to use XLOOKUP with logical criteria to build useful formulas (expressions) based on boolean logic. XLOOKUP has a great advantage: the native arrays support. Generic Formula to use XLOOKUP with logical criteria =XLOOKUP(1, (range1=value1) * (range2>value2), results) In the example, we want to find the first order id which meets the following requirements: … Read more

Use XLOOKUP with multiple criteria through concatenating lookup values and lookup arrays inside the formula. Today’s guide will use the XLOOKUP function to look up values based on multiple criteria. Take a look at the example below! We will use the basic syntax of the function without optional arguments: =XLOOKUP(lookup value, lookup array, return array) … Read more

Learn how to easily perform a left lookup with the XLOOKUP function instead of using the VLOOKUP or INDEX and MATCH functions. We already know that XLOOKUP has opened new horizons in Excel. Today’s guide will be on how to replace the VLOOKUP provides limited functionality in case of left lookup. How the left lookup … Read more

Learn how to calculate probability in Excel using the PROB function with or without the upper limit. What is probability Probability measures how likely an event is to occur or how likely a proposition is true. The higher the likelihood of an event, the more likely it is that the event will occur. How to … Read more

In Excel, the greater than or equal to (>=) logical operator compares two cells that contain the same data types. The greater than equal to operator uses the “>=” symbol and returns the TRUE or FALSE value. We frequently use this operator if we are working with formulas. Today’s tutorial is a part of our … Read more

Learn how to use a mixed reference in Excel to split the references into absolute and relative parts through examples. This article is a part of our definitive guide on Excel Formulas. How to use mixed reference In Excel, all cell references are relative by default. Let us see what will happen if you want to … Read more

A relative reference in Excel points to a cell or range address, and it changes when you copy the formula to another cell. This article is a part of our definitive guide on Excel Formulas. So take a closer look at it. For example, a relative reference to cell B1 looks like this: =B1 Difference between … Read more