Formula library
The formulas people look up most, each explained step by step against a real sheet layout, with working versions in Excel, Google Sheets, SQL, and pandas. Every page lists the mistakes that usually break it.
Lookups
- VLOOKUP with an exact matchVLOOKUP with FALSE finds an exact match for a value in the first column of a range and returns the value from a column you choose to its right.
=VLOOKUP(B2,Products!$A$2:$C$500,3,FALSE) - INDEX and MATCH lookup to the leftINDEX/MATCH looks up a value in one column and returns the item on the same row from any other column, including columns to the left.
=INDEX($A$2:$A$500,MATCH(E2,$B$2:$B$500,0)) - XLOOKUP with a default valueXLOOKUP finds a value in one column and returns the matching value from another, with a built-in fallback value when nothing matches.
=XLOOKUP(B2,Products!$A$2:$A$500,Products!$C$2:$C$500,0) - Two-way lookup with INDEX, MATCH, MATCHINDEX with two MATCH functions returns the value where a matching row and a matching column meet in a grid, such as a budget by cost center and month.
=INDEX($B$2:$M$50,MATCH(H2,$A$2:$A$50,0),MATCH(H3,$B$1:$M$1,0)) - VLOOKUP approximate match for tax bracketsVLOOKUP with TRUE finds the largest value less than or equal to the lookup value in a sorted column, which is how you match income to a tax bracket.
=VLOOKUP(B2,Brackets!$A$2:$B$5,2,TRUE)
Conditional math
- SUMIFS with multiple criteria and a date rangeSUMIFS adds up the values in one column for rows that meet every condition you list, such as a single region within a date range.
=SUMIFS($C$2:$C$5000,$B$2:$B$5000,G1,$A$2:$A$5000,">="&G2,$A$2:$A$5000,"<="&G3) - COUNTIFS between two datesCOUNTIFS counts rows that meet every condition you give it, such as tickets created within one month that have a status of Closed.
=COUNTIFS($A$2:$A$3000,">="&F1,$A$2:$A$3000,"<="&EOMONTH(F1,0),$B$2:$B$3000,"Closed") - AVERAGEIFS that skips blanks and zerosAVERAGEIFS averages a column for rows that meet your conditions. Adding "<>0" as a condition drops zeros, and blank cells are skipped automatically.
=AVERAGEIFS($D$2:$D$2000,$B$2:$B$2000,H2,$D$2:$D$2000,"<>0") - Weighted average with SUMPRODUCTSUMPRODUCT multiplies two columns row by row and adds the results. Dividing that by the sum of the weights gives a weighted average.
=SUMPRODUCT($B$2:$B$200,$C$2:$C$200)/SUM($C$2:$C$200) - Running total with an expanding SUM rangeSUM with a range anchored at the top, like $B$2:B2, adds every value from the first row down to the current one, giving a running total.
=SUM($B$2:B2)
Text cleanup
- Extract an email domain with REGEXEXTRACTREGEXEXTRACT returns the part of a text cell that matches a regular expression. With the pattern "@(.+)$" it returns the domain of an email address.
=REGEXEXTRACT(B2,"@(.+)$") - Split a full name into first and last nameTEXTBEFORE and TEXTAFTER split a full name at the space, returning the first name and the last name in separate cells.
=HSTACK(TEXTBEFORE(A2," "),TEXTAFTER(A2," ",-1)) - Clean messy names with TRIM, CLEAN, and PROPERPROPER(TRIM(CLEAN())) cleans messy text by removing non-printing characters and extra spaces, then capitalizing the first letter of each word.
=PROPER(TRIM(CLEAN(A2)))
Dates
- Employee tenure in years and months with DATEDIFDATEDIF counts the complete years, months, or days between two dates, which is the standard way to calculate employee tenure or age in a sheet.
=DATEDIF(B2,TODAY(),"Y")&" yrs "&DATEDIF(B2,TODAY(),"YM")&" mos" - Business days between two dates with NETWORKDAYSNETWORKDAYS counts the working days between two dates, Monday to Friday, minus any holidays you list, and includes both the start and end dates.
=NETWORKDAYS(B2,C2,Holidays!$A$2:$A$15)
Logic
- IFERROR and IFNA for lookups that missIFERROR wraps a formula and returns a fallback value when it errors, such as showing Not found when a VLOOKUP cannot find a SKU.
=IFERROR(VLOOKUP(B2,Products!$A$2:$C$500,3,FALSE),"Not found") - Tiered commission with IFS or nested IFIFS checks conditions in order and returns the value for the first one that is true, which makes it a readable way to assign commission tiers.
=IFS(B2>=100000,10%,B2>=50000,7%,B2>=20000,5%,TRUE,0)
Filtering & arrays
- Google Sheets QUERY with select, where, and group byQUERY runs a SQL-like statement on a range in Google Sheets, so you can filter, group, and sort data with one formula.
=QUERY(Sales!A1:D,"select B, sum(D) where A >= date '2026-01-01' group by B order by sum(D) desc label sum(D) 'Revenue'",1) - FILTER with two conditionsFILTER returns every row from a range that meets your conditions, and the result updates on its own when the source data changes.
=FILTER(A2:D1000,(B2:B1000="West")*(C2:C1000="Open"),"No matches") - Count distinct values with COUNTA and UNIQUECOUNTA wrapped around UNIQUE counts how many distinct values a column holds, such as the number of different customers in an order list.
=COUNTA(UNIQUE(FILTER(B2:B5000,B2:B5000<>"")))
Your formula isn't on the list?
Paste it into the translator and get the same breakdown in about ten seconds.