Search results

This formula uses XLOOKUP twice, by nesting one XLOOKUP inside another. The first (inner) XLOOKUP is used to perform an exact match lookup on the value in G5:

XLOOKUP(G5,E5:E15,B5:E15)

• The
...Read more

This formula is based on the XLOOKUP function. Working from the inside out, we use the MAX function to calculate a lookup value:

MAX(values)

MAX is...Read more

Working from the inside out, XLOOKUP is configured to find the value in H4 in the Last name column, and return all fields. In order to support wildcards, match_mode is provided as 2:

XLOOKUP(H4,D5:D15,B5:E15,2) //...Read more

If you need to group times into buckets (i.e. group by 6 hours, group by 3 hours, etc.) you can do so with a rounding function called FLOOR.

In the example shown, we have a number of transactions, each with a timestamp. Let's say you want to group these transactions into buckets of 3...Read more

This formula depends on a TRUE or FALSE result from a logical test, where FALSE represents the value you are looking for. In the example, the logical test is data="red", entered as the lookup_array argument in the MATCH function:

=MATCH(FALSE,data="red",0...Read more

One of the nice benefits of XLOOKUP is it can easily return entire rows or columns as a lookup result. This can be done also with INDEX and MATCH, but the syntax is more complex.

In the example shown, we want to retrieve all values associated with...Read more

The goal is to return the first non-blank value in each row from columns B:E, moving left to right. One way to solve this problem is with a series of nested IF statements. Since all cells are contiguous (connected) another way to get the first value is with the XLOOKUP function. Both approaches...Read more

Note: the lookup_value of 2 is deliberately larger than any values in the lookup_vector, following the concept of bignum.

Working from the inside out, the expression:

(TEXT($B$5:$B$13,"mmyy")=TEXT(E5,"mmyy...Read more

One of XLOOKUP's features is the ability to lookup and return an entire row or column. This feature can be used to nest one XLOOKUP inside another to perform a two-way lookup. The inner XLOOKUP returns a result to the outer XLOOKUP, which returns a final result...Read more

Note: the values in E5:E8 are actual dates, formatted with the custom number format "mmm".

Working from the inside out, the expression:

MATCH(TRUE,TEXT(date,"...Read more

In this example, the goal is to calculate the correct grade for each name in column B using the score in column C and the table to the right. For convenience only, grade (G5:G9) and score (F5:F9) are named ranges. This is...Read more

By default, XLOOKUP will return the first match in a data set. However, XLOOKUP offers an optional argument called search_mode to control the order in which data is searched. Setting search mode to -1 causes XLOOKUP to search "last to first" or, in other words, search backwards through...Read more

In this example, the goal is to count codes that appear as substrings in a case-sensitive way. The functions COUNTIF and COUNTIFS are both good options for counting text values, but these functions are not case-...Read more

In this example, the goal is to look up various information about a random group of popular movies from the 1990s. The information to retrieve includes the year released, the rank against the other movies in the list, and worldwide gross sales. To retrieve this information, we are using an INDEX...Read more

XLOOKUP offers several features that make it exceptionally good for more complicated lookups. In this example, we want the latest price for an item by date. If data were sorted by date in ascending order, this would be very straightforward....Read more

In this example, we have dates in B5:B16 and sales in C5:C16. Both ranges are named ranges. The goal is to create a dynamic range between two specific dates: the start date in cell F5 and the end date in cell F6. We then...Read more

The goal is to retrieve the nth matching record in a table when targeting a specific product. For example, if the value in cell H4 is "A", the formula in H7 should return the name "John", since this is the first name in the table associated with product "A". In the same way, the...Read more

In this example, the goal is to find the longest text string in the range C5:C16 that belongs to the group entered in cell F5. The group is a variable that may be changed at any time. At the core, this is a lookup problem, and the challenge is that the value we need to look up (the string...Read more

The LARGE function is an easy way to get the nth largest value in a range:

 =LARGE(range,1) // 1st largest
 =LARGE(range,2) // 2nd largest
 =LARGE(range,3) // 3rd largest

In this example, we can use the LARGE function to get a highest score...Read more

In this example, the goal is to demonstrate how an INDEX and (X)MATCH formula can be set up so that the columns returned are variable. This approach illustrates one benefit of the 2-step process used by INDEX and MATCH: Because INDEX expects a numeric index for row and column numbers, it is easy...Read more

In this example, the goal is to find the closest match to a target value entered in cell E5. Although it may not look like it, this is essentially a look-up problem. The easiest way to solve this problem is with the XLOOKUP function. However in...Read more

The goal is to find the longest text string in the range B5:B16. At the core, this is a lookup problem that requires creating a value (the string length) that does not exist in the data as part of the formula. The easiest way to solve this problem is with the ...Read more

In this example, the goal is to look up the correct shipping cost for an item based on the shipping service selected and the weight of the item. The challenge is that we also need to filter by service. This means we need to apply criteria in two steps: (1) match based on Service, and (2)...Read more

In this example, the goal is to categorize various expenses using the categories shown in column F and the keywords shown in column E. This is a case where it seems like we should perform a lookup operation of some kind, but the problem is that the keywords appear embedded in the text and the...Read more

In this example, the goal is to look up Width and Length based on inputs for Code (K6) and Size (K7). While finding the correct row based on the Code value is straightforward, the problem of how to best retrieve both columns of data (Width and Length) is more challenging. One good way to...Read more

In this example, the goal is to look up the correct price of the product number entered in cell F4 using the product codes in column B. This problem is trickier than it looks. Each product code begins with 3 uppercase letters and ends with 2 or 3 uppercase letters. In the middle of the...Read more