Excel to SQL: formula equivalents

If you know Excel, you already know most of SQL. 27 spreadsheet functions and features, each with the formula, the SQL that does the same job, how it works, and the places where the results differ. Or skip the query and ask your question in plain English.

Lookups

Looking up a value from another table: VLOOKUP, XLOOKUP and INDEX/MATCH become joins.

VLOOKUP in SQL
XLOOKUP in SQL
INDEX MATCH in SQL

Conditional calculations

SUMIF, COUNTIF and IF: calculations that only include rows meeting a condition.

SUMIF in SQL
SUMIFS in SQL
COUNTIF in SQL
COUNTIFS in SQL
AVERAGEIF in SQL
MAXIFS in SQL
IF in SQL
IFERROR in SQL

Summaries and pivots

Pivot tables, sorting, filtering and ranking: the everyday ways of summarising a sheet.

Pivot Table in SQL
SUMPRODUCT in SQL
UNIQUE in SQL
Remove Duplicates in SQL
SORT in SQL
FILTER in SQL
RANK in SQL

Text functions

Cleaning and splitting text: TRIM, LEFT, TEXTJOIN and Text to Columns.

Text to Columns in SQL
TEXTJOIN in SQL
LEFT, MID and RIGHT in SQL
TRIM in SQL
SUBSTITUTE in SQL

Dates and rounding

DATEDIF, EOMONTH, YEAR and ROUND, and how SQL handles dates and rounding differently.

DATEDIF in SQL
EOMONTH in SQL
YEAR, MONTH and TEXT dates in SQL
ROUND in SQL

For deeper query techniques see the SQL patterns library, or the glossary if a term is unfamiliar.