Function
NOT Function
The Excel NOT function returns the opposite of a given logical or Boolean value. When given TRUE, NOT returns FALSE. When given FALSE, NOT returns TRUE. Use the NOT function to reverse a logical value.Read more
Search results
Looking for more? Try chatting with the Exceljet Chatbot.
Formula
Lookup latest price
The LOOKUP function assumes data is sorted, and always does an approximate match. If the lookup value is greater than all values in the lookup array, default behavior is to "fall back" to the previous value. This formula exploits this behavior by creating an array that contains only 1s and...Read more
Formula
SUMPRODUCT case-sensitive lookup
The SUMPRODUCT function multiplies arrays together and returns the sum of products. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. For example, if we give SUMPRODUCT one array...Read more
Formula
Count dates in given year
The YEAR function extracts the year from a valid Excel date. For example:
=YEAR("15-Jun-2021") // returns 2021
In this case, we are giving YEAR and array of dates in...Read more
Function
IFNA Function
The Excel IFNA function is a simple way to handle #N/A errors specifically without catching other errors. The IFNA function allows you to specify a custom value or message to display instead of the #N/A error. If no error #N/A error occurs the formula returns a normal result....Read more
Function
LET Function
The Excel LET function lets you define named variables in a formula. There are two primary reasons you might want to do this: (1) to improve performance by eliminating redundant calculations and (2) to make more complex formulas easier to read and write....Read more
Article
How to lookup first and last match
If VLOOKUP finds more than one match, will you get the first match or the last match? It's a trick question. It depends :) This article explains this confusing topic in detail, with lots of examples.Read more
Function
IFERROR Function
The Excel IFERROR function returns a custom result when a formula generates an error, and a standard result when no error is detected. IFERROR is an elegant way to trap and manage errors without using more complicated nested IF statements.Read more
Function
FILTER Function
The Excel FILTER function is used to extract matching values from data based on one or more conditions. The output from FILTER is dynamic. If source data or criteria change, FILTER will return a new set of results. This makes FILTER a flexible way to isolate and inspect data without...Read more
Function
TRANSLATE Function
The Excel TRANSLATE function is designed to translate text from one language to another in Excel. The source and target languages are provided as special codes. For example, "en" for English, "fr" for French, "it" for Italian, and so on.
...Read more
Formula
Count birthdays by year
In this example, the goal is to count birthdays by year. The source data is an Excel Table named data in the range C5:C16. The birthdays we want to count are in the Birthday column. In column E, the years of interest have been previously ...Read more
Formula
Income tax bracket calculation
In this example, the goal is to calculate the total income tax owed in a progressive tax system with multiple tax brackets, as shown in the worksheet. The article below first reviews how taxes are computed in a progressive system. Next, it shows how to accomplish this task in Excel using...Read more
Function
EXACT Function
The Excel EXACT function compares two text strings, taking into account upper and lower case characters, and returns TRUE if they are the same, and FALSE if not. EXACT is case-sensitive.Read more
Function
FIND Function
The Excel FIND function returns the position (as a number) of one text string inside another. When the text is not found, FIND returns a #VALUE error.Read more
Formula
Get last entry by month and year
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
Formula
Get first entry by month and year
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
Formula
INDEX and MATCH approximate match with multiple criteria
In this example, the goal is to look up the correct shipping cost for an item based on the shipping service selected and the item's weight. At the core, this is an approximate match lookup based on weight. The challenge is that we also need to filter by service. This means we must apply criteria...Read more
Function
SEQUENCE Function
The Excel SEQUENCE function generates a list of sequential numbers in an array. The array can be one dimensional, or two-dimensional, determined by rows and columns arguments. ...Read more
Formula
VLOOKUP if blank return blank
In this example, the goal is create a VLOOKUP formula that will return an empty cell when the lookup result is an empty cell.
When VLOOKUP can't find a value in a lookup table, it returns the #N/A error. You can use the IFNA function or...Read more
Function
ISBLANK Function
The Excel ISBLANK function returns TRUE when a cell is empty, and FALSE when a cell is not empty. For example, if A1 contains "apple", ISBLANK(A1) returns FALSE.Read more
Function
IF Function
The Excel IF function performs a logical test and returns one result when the logical test returns TRUE and another when the logical test returns FALSE. For example, to "pass" scores above 70: =IF(A1>70,"Pass","Fail"). More than one condition can be tested by nesting IF functions. The IF...Read more
Formula
Get location of value in 2D array
In this example, the goal is to return a list of the locations for a specific value in a 2D array of values (i.e., a table). The target value is entered in cell N5, and the table being tested is in the range C4:L16. The coordinates are supplied from row 4 and column B, as seen in the...Read more
Term
Dynamic array
Above: Using the FILTER function to return multiple matches on "red".
In fall 2018, Microsoft announced a new feature in Excel called "dynamic array formulas". In a nutshell, this feature allows formulas to return multiple results to a range...Read more
Term
Term
Legacy Excel
For simplicity, we use the term "Legacy Excel" to refer to any version of Excel before dynamic array formulas were introduced. Practically speaking, this means Excel 2019 and older, since Excel 365 and Excel 2021 are the only...Read more
Article
Regular Expressions in Excel
After decades of waiting, Excel finally supports Regular Expressions, aka regex! Learn how three powerful new functions - REGEXTEST, REGEXREPLACE, and REGEXEXTRACT - can transform complex text operations into elegant, maintainable formulas. Whether you're cleaning data, validating...Read more
Formula
Count cells that contain text
In this example, the goal is to count cells in a range that contain text values. This could be hard-coded text like "apple" or "red", numbers entered as text, or formulas that return text values. Empty cells and cells that contain numeric values or errors should not be included in the...Read more
Article
Dynamic array formulas in Excel
Dynamic Arrays are the biggest change to Excel formulas in years. Maybe the biggest change ever. This is because Dynamic Arrays let you easily work with multiple values at the same time in a formula. This article provides an overview with many links and examples.Read more
Term
Formula
Count cells that do not contain
In this example, the goal is to count cells that do not contain a specific substring. This problem can be solved with the COUNTIF function or the SUMPRODUCT function. Both approaches...Read more