Exceljet

Quick, clean, and to the point

Formulas

Excel formula: Two-way lookup VLOOKUP in a Table
At a high level, we using VLOOKUP to extract employee information in 4 columns with ID as the lookup value. The ID value comes from cell I4, and is locked so that it won't change as the formula is copied down the column. The table array is the table named Table1, with data in the range B5:F104. The...Read more
Excel formula: Calculate days remaining
Dates in Excel are just serial numbers that begin on January 1, 1900. If you enter 1/1/1900 in Excel, and format the result with the "General" number format , you'll see the number 1. This means that you can easily calculate the days between two dates by subtracting the earlier date from the later...Read more
Excel 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 errors,...Read more
Excel formula: Rank with ordinal suffix
Ordinal numbers represent position or rank in a sequential order. They are normally written using a number + letter suffix: 1st, 2nd, 3rd, etc. To get an ordinal suffix for a small set of numbers, you can use the CHOOSE function like this: = CHOOSE ( B5 , "st" , "nd" , "rd...Read more
Excel formula: Count unique values with criteria
In this example, the goal is to count unique values that meet one or more specific conditions. In the example shown, the formula used in cell H7 is: = SUM ( -- ( LEN ( UNIQUE ( FILTER ( B6:B15 , C6:C15 = H6 , "" ))) > 0 )) At the core, this formula uses the FILTER function to apply...Read more

Blog

Why does SUMPRODUCT show up in so many formulas?

In this article, I attempt to explain why you see SUMPRODUCT so often in formulas, and when you can use the SUM function instead. The short version: SUMPRODUCT supports array operations natively, which makes it very useful for solving seemingly unrelated Excel problems. In the current version of Excel, you can use the SUM instead, but SUMPRODUCT is better for backwards compatibility.

How to concatenate in Excel

Concatenation is one of the most important operations in more advanced formulas. This article explains how to perform concatenation manually with the ampersand operator (&) and with the three Excel functions designed for concatenation: CONCATENATE, CONCAT, and TEXTJOIN.

What is an array formula?

In the world of Excel formulas, the term "array formula" is probably responsible for more confusion than just about any other concept. This is because the definition of an array formula has become mixed up with the requirement to enter some array formulas in a special way, with control + shift + enter.

Excel's RACON functions

There are eight functions in Excel that work differently than you might realize. The same features that are supposed to make them easier to use, can actually make them harder to use :) Read on for important information about COUNTIF, COUNTIFS, SUMIF, SUMIFS,  AVERAGEIF, AVERAGEIFS, MINIFS, and MAXIFS.

This article provides examples of public Coronavirus data you can download to Excel with Power Query. Each example has a link, a screenshot to show what the data looks like in Excel after being imported, and an Excel workbook.

Feedback

Your program is great and I continue to learn from it every day. You provide such a great service. My job efficiency has really taken off and I am more productive leaving time spent to analyze data. -Kurt
I've used your website as a reference for excel help for a couple years now and I just wanted to take a minute to thank you for all the content available on your website. I am now the excel "guru" at my company but in reality I'm just a hack who knows how to search the info you've made available. -Chris
First and only stop for Excel formula help. You have made the world a better place. -Darin