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. Formula to extract unique values with criteria In the example, you want to extract unique values from the list where the score is greater than 50. The formula is the following: =UNIQUE(FILTER(B3:C11, … Read more
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 a simple logic, and it finds the nth … 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. Example: If value1 is greater than equal to than value2, the result is TRUE If value1 is less … 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
Use logical operators in Excel to compare values. The output is true or false, depending on conditions that match the criteria or not. Today’s guide will be on logical operators (comparison operators). First, you will learn how to compare two or more values and how to analyze the output. Then, we’ll take a closer look … Read more
Assign a descriptive name to a range of cells using a named range in Excel. Use it to create easy-to-understand formulas. What is a named range? Named range enables you to add an easily memorable name to a cell or range of cells. From now, you can write much better formulas, and it is easy … Read more
Learn all about the Excel Formula Bar. Discover how to use, expand, show, and hide quickly through examples. What is the Formula Bar in Excel? The Formula Bar is the place where you can edit data or formulas in the selected cell. For example, if you want to check the formula in the active cell, … Read more
