Search results

In the example shown, we have a set of order data that includes Date, Product, Name, and Amount. The data is sorted by date in ascending order. The goal is to look up the latest order for a given person by Name. In other words, we want the last match by name. The challenge is that Excel...Read more

In this example, the goal is to look up a number with a certain amount of allowed tolerance, defined as n. In other words, with a given lookup number we are trying to find a number in a set of data that is ± n. In the worksheet shown, the number to find is in...Read more

To do this, LOOKUP is configured as follows:

• Lookup values are ages in column C
• The lookup vector is the named range "age" (F5:F8)
• The result vector is the named range "group" (G5:G8)

With this setup, LOOKUP performs an approximate match on the...Read more

There are certain functions in Excel that return a cell reference as a result rather than a value. Two of these functions are XLOOKUP and INDEX. The presence of the cell reference in the result is...Read more

In this example, the goal is to get the last value in column B, even when data may contain empty cells. A secondary goal is to get the corresponding value in column C. This is useful for analyzing datasets where the most recent or last entry is significant. In the current version of...Read more

The goal is to search through a cell for one of several specified values and return the last match found when one exists. The worksheet includes a list of colors in the range E5:E11 (which is named list) and a series of short sentences in the range B5:B16. The task is to...Read more

In this example, the goal is to look up and retrieve all names for a given team and return them in a single cell as a comma‑separated list. At the core, this is a lookup problem, but the twist is that we want to return multiple matches for each team, not just one. That means...Read more

The goal is to match a row in a set of data based on a given Item, Size, and Color. At a glance, this seems like a difficult problem because XMATCH only has one value for lookup_value and lookup_array. How can we configure XMATCH to consider values from multiple...Read more

By default, Excel is not case-sensitive.  For example, with "APPLE" in  A1, and "apple" in A2, the following formula will return TRUE:

=A1=A2 // returns TRUE

To compare text strings in a case-sensitive way, you can use the...Read more

The gist of this problem is that we want to get the first non-blank cell, but we don't have a direct way to do that in Excel. The easiest way to solve this problem is with the XLOOKUP function.

XLOOKUP function

The XLOOKUP...Read more

Working from the inside out, the MIN function is used to find the lowest bid in the range C5:C9:

MIN(C5:C9) // returns 99500

The result, 99500, is fed into the MATCH function as the lookup value:

MATCH(...Read more

This formula uses the LOOKUP function to find and retrieve the last matching file name. The lookup value is 2, and the lookup_vector is created with this:

1/(ISNUMBER(FIND(G6,files)))

Inside this snippet, the FIND function looks for the...Read more

The general goal is to search through a cell for one of several specified values and return the first match found if one exists. The worksheet includes a list of colors in the range E5:E11 (which is named list) and a series of short sentences in the range B5:B16. The task is...Read more

At the core, this is a normal INDEX and MATCH function:

=INDEX(array,MATCH(value,range,0))

Where the MATCH function is used to find the correct row to return...Read more

This formula relies on the FILTER function to retrieve data based on a logical test. The array argument is provided as B5:D15, which contains all of the data without headers. The include argument is...Read more

This formula uses -1 for match type to allow an approximate match on values sorted in descending order. The MATCH part of the formula looks like this:

MATCH(F4,B5:B9,-1)

Using the lookup value in cell F4, MATCH finds the first value in B5:...Read more