Search results

Note: in the current version of Excel, the FILTER function is a better way to solve this problem. The INDEX and MATCH formula explained here is meant for legacy versions of Excel that do not provide the FILTER...Read more

In this example, the goal is to create a running count for a specific value that appears in column B. The value to count is entered in cell E5, which is the named range value. The core of the solution explained below is the...Read more

In the example shown, we have a list of amounts by month. The goal is to dynamically sum values through a given number of months using a variable n in cell E5. Since month names are just text, and months are listed in order, the key requirement is to sum amounts...Read more

Although FILTER is commonly used to filter rows, you can also filter columns. The trick is to use an include argument that operates on columns instead of rows. In this example, we use a hard-coded array constant to filter out unwanted columns, but...Read more

In this example, the goal is to retrieve employee information from a table using only a partial match on the last name. In other words, by typing "Aya" into cell H4, the formula should retrieve information about Michael Ayala.

The VLOOKUP function...Read more

At the core, this formula uses the MID function to extract each character of a text string in reverse order. The starting character is given as a list of numbers in descending order hard-coded as array constant:

MID(B5,{10,9,8,7,6,5,4,3,2,1},1)
...Read more

In this example, the goal is to filter data to show rows where dates have expired or will be expiring soon. In the table to the left, we have equipment that needs to be replaced every x months, where x appears in the "Months" column. The "Replaced" column shows the date equipment was...Read more

This formula uses the FILTER function to retrieve data based on a logical test constructed with the LARGE and IF functions. The result is the top 3 scores in group B.

The FILTER function applies criteria with...Read more

In this example, the goal is to generate a list of people who were invited but did not attend an unspecified event. More specifically, we need to compare the names in B5:B16 against the names in D5:D12 and return the missing names. For convenience, list1 (B5:B16) and...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

In this example, the goal is to count test scores in column C that are greater than 90. The simplest way to do this is with the COUNTIF function, which takes two arguments, range and criteria...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

Starting from the inside out, the MID function is used to extract all text after "@":

MID(B5,FIND("@",B5),LEN(B5))

The FIND function provides the starting point, and for total characters to extract, we just use LEN on the original text....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

This example is set up in two parts for clarity: (1) a formula to determine the top 3 amounts for each month and (2) a formula to retrieve the client name for each of the top 3 monthly amounts.

Note there is no actual rank in the source data. Instead, we are using the LARGE function to...Read more

This is a pretty standard use of the SUMIFS function. In this case, we need to sum amounts based on two criteria: type (forecast or actual) and group. To sum by type, the range/criteria pair is:

type,G$4

where type is the named range D5:D14...Read more

The goal is to mark rows where the color is "Red" AND the size is "Small" or "Medium". To perform this task, you can use the IF function in combination with the AND function and the...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

The CELL function can provide a wide range of information about cell properties. One property is called "protect" and indicates whether a cell is unlocked or locked. All cells start out "locked" in a new Excel workbook, but this setting has no effect until a worksheet is protected.

The...Read more

The FILTER function can filter data using a logical expression provided as the include argument. In this example, this argument is created with an expression that uses the ISNUMBER and MATCH functions like this:

...Read more

Excel times are numbers and can be summed like other numeric values. In this example, F4:G7 is a summary table, showing the total time logged in each of three states: Standby, Run, and Offline. These values are hardcoded in the range F5:F7.

To sum...Read more