XLOOKUP match text contains


Learn how to use XLOOKUP to match text values with the help of the 5th argument, wildcard character match mode. How to lookup matching text values One of the powerful features of the XLOOKUP function is the native wildcards support. You enable wildcards, locate the 5th argument (match mode), and set the parameter to 2. … Read more

XLOOKUP across multiple Worksheets

xlookup across multiple Worksheets

Learn how to use XLOOKUP across multiple Worksheets with named ranges instead of the old VLOOKUP function. Tip: If you frequently work with lookup functions and formulas, we recommend using the most effective solution, XLOOKUP. LOOKUP Formula in Excel with multiple Sheets This guide will show you how to summarize information from multiple Worksheets into … Read more

XLOOKUP with boolean or logic


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, which you can use in various situations. Generic formula to use a boolean or logic In the example, our goal is to find and list all records … Read more

Unique values with criteria

unique values with criteria

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

Name of nth largest value

name of the nth largest value

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

XLOOKUP last match

xlookup last match

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

XLOOKUP lookup row or column


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

XLOOKUP with logical criteria

use xlookup with logical criteria

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 that meets the following requirements: … Read more

XLOOKUP with multiple criteria

xlookup with multiple criteria

Learn how to use XLOOKUP with multiple criteria by concatenating lookup values and lookup arrays inside the formula. XLOOKUP multiple criteria: The Basics In Excel, you can modify the XLOOKUP formula to work with multiple criteria in a few ways. By default, XLOOKUP is for a single lookup value. However, you can join multiple criteria … Read more

Left lookup in Excel using XLOOKUP

Left lookup in Excel using XLOOKUP

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

How to calculate probability in Excel

how to calculate probability in Excel

Learn how to calculate probability in Excel using the PROB function with or without the upper limit. Table of contents: 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 … Read more