# Exceljet

## Formulas

### 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

### 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

### 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

### 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

### 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 SUMPRODUCT?

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.