This formula will return TRUE if a cell contains any one of the strings passed into it. Because it is built with the SEARCH function, it automatically performs partial matching and supports wildcards. The first step in creating a custom LAMBDA function is to verify the logic needed with a Excel standard formula. This LAMBDA formula is based on a Excel formula created with three functions: SUMPRODUCT, ISNUMBER, and SEARCH: Read a detailed description here....

The StripTrailingChars function is designed to accept two input parameters: str is the text from which to remove trailing characters, and char is the trailing character to remove. In the example shown, StripTrailingChars is configured to remove the characters seen in column C from the text seen in column B. These characters will only be removed when they are “trailing”, i.e. when they appear at the end of the string....

One of the most powerful ways to extend the functionality of dynamic array formulas is to nest one function inside another. To illustrate, let’s look at an example based on the SORT and FILTER functions. Here we have data that shows over 300 of the largest cities by population in the United States. This data is in an Excel Table called “Table1”. We have rank, city, state, population, and percent change....

With the introduction of dynamic array formulas, Excel includes 6 brand new functions that directly leverage dynamic array behavior. Each of these functions is covered in more detail later in the course, so this is just a brief demonstration. UNIQUE The UNIQUE function is designed to extract unique values from a set of data. This range contains a list of colors, some of which appear more than once. With UNIQUE, I can quickly build a list unique values....

where “rng” is the named range H4:H10 and contains the values to look for. The double negative will force the TRUE and FALSE values to 1 and 0 respectively. Since SUMPRODUCT receives just one array, it simply adds up the items in the array and returns the result. Logically, any result greater than zero means that at least one value exists in the range. So, the final step is to evaluate the SUMPRODUCT result to see if its greater than zero....

Here we have a set of sales data that spans 2 years, broken down by month. Using named ranges, I’m going to add several formulas to fill in the summary information, based on a name in I8. Generally, the fastest way to create a named range is to use the name box. Just make a selection and type a valid name. To see a list of existing names, use Control + F3....

In the range C6:E6, we count numbers greater than or equal to 5. The second requirement is to have at least 15 volunteer hours total. This requirement is tested with a simple logical expression based on the SUM function: If both the the logical conditions return TRUE, the AND function will return TRUE. IF either returns FALSE, AND will return false. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

With XLOOKUP’s default settings for match_mode (exact) and search_mode (first to last) the formula matches the first record where: account begins with “x” AND region is “east”, and month is NOT April. The first match is the fourth record (row 8) in the example shown. In this example, the required criteria is: account begins with “x” AND region is “east”, and month is NOT April. For each of the three separate criteria above, we use a separate logical expression....

where data is the named range B5:D14. To get the first row used, we use the ROW function together with the MIN function like this: Because data contains more than one row, ROW returns an array of row numbers: This array goes directly to the MIN function, which returns the smallest number: To get the first column, we use the COLUMN function in the same way: Since data contains three rows, COLUMN returns an array with three column numbers: and the MIN function again returns the largest number: Both results are returned directly to the ADDRESS function, which constructs a reference to the cell at row 5, column 2: If you want a relative address instead of an absolute reference, you can supply 4 for the third argument like this:...

where “group” (B5:B14), “region” (C5:C14), and “sales” (D5:D14) are named ranges. In the example shown, we have group values in column F and region values in column G. We use these values directly by using cell references for criteria. The first argument holds the range of values to average: To restrict calculation by group we provide: To restrict calculation by region we use: The result in cell J5 is 105:...

In the example shown, the formula in F5 is: Working from the inside out, VLOOKUP looks up and retrieves a link value from column 2 of the named range “link_table” (B5:C8). The lookup value comes from column E, and VLOOKUP is configured for exact match. The result is fed into HYPERLINK as link_location, and the text in column E is used for friendly_name. HYPERLINK returns a working link. Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa....

The result is the number 1021 formatted as text “1021”. All numbers in column D are formatted as text with the formulas seen in column F. Convert with formatting The TEXT function can be used to convert numbers to text using a given number format. In the example shown, the TEXT function is used to convert each number in column B to a text value using the formula and number shown in column F....

where data is the named range B5:B15 and the numbers in column D are entered as text values. COUNTIF function The COUNTIF function returns the count of cells that meet one or more criteria, and supports logical operators (>,<,<>,=) and wildcards (,?) for partial matching. You would think you could use the COUNTIF function with the question mark (?) and asterisk () wildcards to count numbers where the third digit is 1 like this: However, COUNTIF will return zero....

where the coordinates of the two points are given in columns B through E. Distance is the square root of the change in x squared plus the change in y squared, where two points are given in the form (x1, y1) and (x2, y2). The distance formula is an example of the Pythagorean Theorem applied, where the change in x and the change in y correspond to the two sides of a right triangle, and the hypotenuse is the distance being computed....

For example: In the example shown, the formula in D6, copied down, is: Notes: Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

The LARGE function takes two arguments, array and k. Array is an array or range of numeric values. The argument k represents position or rank. For example, to return the largest value in array, provide 1 for k. To return the fifth largest value in array, provide 5 for k. To get nth smallest values, see the SMALL function. Examples In the formula below, the LARGE function returns the third largest value in a list of five numbers provided in an array constant: Note values do not need to be sorted....

Example #1 - not green or red In the example shown, the formula in C5, copied down, is: The literal translation of this formula is “NOT green or red”. At each row, the formula returns TRUE if the color in column B is not green or red, and FALSE if the color is green or red. Example #2 - Not blank A common use case for the NOT function is to reverse the behavior of another function....

REPLACE function takes four separate arguments. The first argument, old_text, is the text string to be processed. The second argument, start_num is the numeric position of the text to replace. The third argument, num_chars, is the number of characters that should be replaced. The last argument, new_text, is the text to use for the replacement. Examples To replace the “C” in the path below with a “D”: To replace 3 characters starting at the 4th character: You can use REPLACE to remove text by specifying an empty string ("") for new_text....

Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Note: In Mac 2016, Control + Shift + : stopped working to insert a time. The current shortcut on a Mac is Command + ; Dave Bruns Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.