Looking for more? Try chatting with the Exceljet Chatbot.
When building advanced formulas that use dynamic ranges, it's often necessary to figure out the last location of data in a list. Depending on the data, this could be the last row with data, the last column with data, or the intersection of both. Note: we want the last *relative position* ...Read more
The goal is to demonstrate how other formulas and functions can be nested inside the IF function. The example is a simple quantity-based discount formula.
The...Read more
In this example the goal is to "unwrap" a column of values into separate fields. The values are spaced evenly apart, and the result should be all related values on one row, where each column corresponds to a field of information. The input data appears in column B. Each "...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 Excel TEXTJOIN function concatenates multiple values together with or without a delimiter. TEXTJOIN can concatenate values provided as cell references, ranges, or constants, and can optionally ignore empty cells...Read more
In this example the goal is to check a cell for several things at once, and return a comma separated list of the things that were found. In other words, we want check for the colors seen in column E and list the colors found in column C. The formula in C5, copied down, is:
...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
In this example, the goal is to extract common values from two text strings that contain comma-delimited values. In the worksheet shown the values for "List1" appear in column B and the values for "List2" appear in column C. The results in column D show the intersection of the two lists, that is...Read more
The goal is to take a specific action when a value begins with "x", "y", or "z". As is often the case in Excel, there are multiple ways to approach this problem. The simplest way is to use the OR function with the LEFT function to create the required logical test. Another option is to...Read more
The Excel HLOOKUP function finds and retrieve a value from data in a horizontal table. The "H" in HLOOKUP stands for "horizontal", and lookup values must appear in the first row of the table, moving horizontally to the right. HLOOKUP supports approximate and exact matching, and...Read more
This example uses the UNIQUE function together with the FILTER function. You can see a more basic example here.
The trick in this case is to apply criteria to the FILTER function to only allow values based on the count of occurrence....Read more
The goal is to do something if a cell contains a given substring. For example, in the worksheet above, a formula returns "x" when a cell contains "abc". If you are familiar with Excel, you will probably think first of the IF function. However, one limitation of IF is that it does...Read more
XLOOKUP can handle arrays natively, which makes it a very useful function when constructing criteria based on multiple logical expressions.
In the example shown, we are looking for the order number of the first order to Chicago over $250. We are constructing a lookup...Read more
In this example, the goal is to look up a price using XLOOKUP with multiple criteria. To be more specific, we want to look up a price based on Item, Size, and Color. At a glance, this seems like a difficult problem because XLOOKUP only has one value for lookup_value and lookup_array...Read more
The goal of this example is to look up the correct astrological or zodiac sign for a given birthdate, using the table shown in B5:F15. These are based on the Western zodiac signs described here. Zodiac signs are used in horoscopes,...Read more
In this example, the goal is to list and count values that are duplicated in a set of data at least n times, where n is provided as a variable in cell D5. All data is in the named range data (B5:B16). In the...Read more
This formula relies on the helper table visible in the range B4:D10. Column B contains the six numbers we want as a final result. Column C contains the probability weight assigned to each number, entered as a percentage. Column D contains the cumulative probability, created with this formula in...Read more
The Excel RANK.AVG function returns the rank of a number against a list of other numeric values. When values contain duplicates, the RANK.AVG function will assign an average rank to each set of duplicates....Read more
This example shows how to use the VLOOKUP function to calculate the total shipping cost for an item in one formula, where the cost per kilogram (kg) varies according to weight. This requires an "approximate match" since in most cases the actual...Read more
The Excel INDEX function returns the value at a given location in a range or array. You can use INDEX to retrieve individual values, or entire rows and columns. The MATCH function is often used together with INDEX to provide row and column numbers....Read more
For many years, INDEX and MATCH have been the go-to solution for difficult lookup problems in Excel. While more complicated to configure, the two-function combination of INDEX + MATCH is flexible and powerful. But now that...Read more
The goal is to do something if a cell contains one substring or another. Most users will think first of the IF function. However, one problem with IF is that it does not support wildcards like "?" and "*". This means we can't use IF by itself...Read more
This formula uses the MATCH function in approximate match mode to locate the last text value in a range. Approximate match enabled by setting by the 3rd argument in MATCH to 1, or omitting this argument, which defaults to 1.
The lookup...Read more
When constructing more advanced formulas, it's often necessary to figure out the last location of data in a list. Depending on the data, this could be the last row with data, the last column with data, or the intersection of both. We want the last *relative position* inside a given range...Read more
The Excel CHOOSE function returns a value from a list using a given position or index. For example, =CHOOSE(2,"red","blue","green") returns "blue", since blue is the 2nd value listed after the index number. The values provided to CHOOSE can include references.Read more
The goal is to assign a grade to each score in column C according to the rules in the table in the range F4:G9. One way to do this in Excel is to use a series of nested IF functions. Generally, nested IFs formulas are used to test more than one condition and return a different result for...Read more
How do you figure out what formula to use? A teardown of 5 good formulas you can use to highlight dates based on a common month and year value using conditional formatting.Read more
In this example, the goal is to randomly assign the names in column B to three groups of equal size. The group names are "A", "B", and "C", and these values appear in the named range groups (F5:F7). The solution should automatically count the number of groups to assign and...Read more
In this example, the goal is to average the Prices in C5:C16 when the Group in D5:D16 is not blank (i.e. not empty). The traditional way to solve this problem is to use the AVERAGEIFS function. However, you can also use the ...Read more
