| Formula | Related Functions |
|---|---|
| Count between dates by age range | COUNTIFS FIND LEFT RIGHT SUMPRODUCT TEXTBEFORE |
| Count birthdays by year | SUMPRODUCT YEAR COUNTIFS BYROW UNIQUE SORT LET |
| Count cells between dates | COUNTIFS SUMPRODUCT |
| Count cells between two numbers | COUNTIFS SUMPRODUCT |
| Count cells equal to | COUNTIF SUMPRODUCT |
| Count cells equal to case sensitive | SUMPRODUCT EXACT |
| Count cells equal to one of many things | COUNTIF SUMPRODUCT ISNUMBER MATCH |
| Count cells equal to this or that | COUNTIF SUM SUMPRODUCT |
| Count cells greater than | COUNTIF COUNTIFS |
| Count cells less than | COUNTIF COUNTIFS |
| Count cells not between two numbers | COUNTIF SUMPRODUCT |
| Count cells not equal to | COUNTIF COUNTIFS |
| Count cells not equal to many things | MATCH ISNA SUMPRODUCT COUNTA COUNTIF |
| Count cells not equal to x or y | COUNTIFS SUMPRODUCT |
| Count cells over n characters | SUMPRODUCT LEN N |
| Count cells that are blank | COUNTBLANK COUNTA COUNTIF SUMPRODUCT |
| Count cells that are not blank | COUNTA COUNTBLANK COUNTIFS SUMPRODUCT |
| Count cells that begin with | COUNTIF SUMPRODUCT LEFT LEN EXACT |
| Count cells that contain case sensitive | SUMPRODUCT ISNUMBER FIND |
| Count cells that contain either x or y | SUMPRODUCT ISNUMBER FIND COUNTIF |
| Count cells that contain errors | SUMPRODUCT ISERROR ISERR ERROR.TYPE IFERROR |
| Count cells that contain n characters | COUNTIF SUMPRODUCT LEN REPT |
| Count cells that contain negative numbers | COUNTIF SUMPRODUCT |
| Count cells that contain numbers | COUNT SUMPRODUCT ISNUMBER |
| Count cells that contain odd numbers | SUMPRODUCT ISODD MOD ISEVEN |
| Count cells that contain positive numbers | COUNTIF SUMPRODUCT |
| Count cells that contain specific text | COUNTIF SUMPRODUCT SEARCH FIND ISNUMBER |
| Count cells that contain text | COUNTIF COUNTIFS SUMPRODUCT ISTEXT NOT |
| Count cells that do not contain | COUNTIF SUMPRODUCT SEARCH FIND NOT |
| Count cells that do not contain errors | ISERROR NOT SUMPRODUCT ISERR COUNTIF |
| Count cells that do not contain many strings | ISNUMBER SEARCH MMULT TRANSPOSE REDUCE |
| Count cells that end with | COUNTIF |
| Count columns that contain specific values | MMULT COLUMN TRANSPOSE BYCOL LAMBDA |
| Count dates by day of week | SUMPRODUCT WEEKDAY MATCH |
| Count dates in given year | YEAR SUMPRODUCT |
| Count if row meets internal criteria | SUMPRODUCT |
| Count if row meets multiple criteria | SUMPRODUCT |
| Count if two criteria match | COUNTIFS SUMPRODUCT |
| Count items in list | COUNTIFS |
| Count long numbers | SUMPRODUCT COUNTIF |
| Count matches between two columns | SUMPRODUCT |
| Count matching values in matching columns | SUMPRODUCT ISNUMBER MATCH SEARCH |
| Count missing values | COUNTIF SUM SUMPRODUCT MATCH ISNA |
| Count non-blank cells by category | COUNTIFS |
| Count not equal to multiple criteria | SUMPRODUCT ISNA MATCH COUNTIFS |
| Count numbers by nth digit | SUMPRODUCT MID |
| Count numbers by range | COUNTIFS FREQUENCY DROP |
| Count numbers that begin with | SUMPRODUCT LEFT LEN COUNTIF |
| Count numbers with leading zeros | SUMPRODUCT COUNTIF SUMIF |
| Count occurrences in entire workbook | COUNTIF SUMPRODUCT VSTACK |
| Count or sum variance | SUMPRODUCT SUM ABS |
| Count or sum whole numbers only | MOD SUMPRODUCT |
| Count paired items in listed combinations | COUNTIFS CONCAT |
| Count rows that contain specific values | MMULT COLUMN TRANSPOSE BYROW LAMBDA |
| Count rows with at least n matching values | MMULT COLUMN TRANSPOSE |
| Count rows with multiple OR criteria | SUMPRODUCT |
| Count rows with OR logic | SUMPRODUCT |
| Count sold and remaining | COUNTA COUNTIF |
| Count total matches in two ranges | SUMPRODUCT COUNTIF MATCH ISNUMBER COUNT |
| Count unique dates | UNIQUE COUNT COUNTIF SUMPRODUCT |
| Count unique numeric values in a range | FREQUENCY SUM COUNTIF |
| Count unique numeric values with criteria | FREQUENCY SUM |
| Count unique text values in a range | FREQUENCY MATCH ROW SUMPRODUCT |
| Count unique text values with criteria | FREQUENCY MATCH ROW SUM |
| Count unique values in a range with COUNTIF | SUMPRODUCT COUNTIF |
| Count visible rows in a filtered list | SUBTOTAL |
| Count visible rows with criteria | SUBTOTAL OFFSET SUMPRODUCT INDEX |
| COUNTIF with non-contiguous range | COUNTIF INDIRECT VSTACK |
| COUNTIFS with multiple criteria and OR logic | COUNTIFS |
| Histogram with FREQUENCY | FREQUENCY |
| Running count of occurrence in list | COUNTIF IF LET SCAN |
| Summary count by month with COUNTIFS | COUNTIFS EDATE |
| Summary count with COUNTIF | COUNTIF |
| Summary count with percentage breakdown | COUNTIF COUNTA |
| SUMPRODUCT count multiple OR criteria | SUMPRODUCT |
| Two-way summary count | COUNTIFS LET UNIQUE HSTACK VSTACK |
| Formula | Related Functions |
|---|---|
| Calculate running total | SUM SCAN LAMBDA |
| Count cells that contain formulas | SUMPRODUCT ISFORMULA NOT |
| Subtotal by color | SUMIF COUNTIF |
| Subtotal by invoice number | SUMIF COUNTIF |
| Subtotal invoices by age | SUMIF COUNTIFS |
| Sum across multiple worksheets | SUM |
| Sum across multiple worksheets with criteria | SUMPRODUCT SUMIF INDIRECT |
| Sum and ignore errors | SUM SUMIF AGGREGATE IFERROR |
| Sum bottom n values | SUMPRODUCT SMALL SUM ROW INDIRECT |
| Sum bottom n values with criteria | SMALL SUM FILTER SEQUENCE |
| Sum by group | SUMIF SUMIFS IF |
| Sum by month | SUMIFS EDATE SUMPRODUCT TEXT |
| Sum by month ignore year | SUMPRODUCT MONTH |
| Sum by month in columns | SUMIFS EOMONTH |
| Sum by quarter | SUMIFS ROUNDUP MONTH SUMPRODUCT LET LAMBDA BYROW |
| Sum by week | SUMIFS LET LAMBDA UNIQUE WEEKDAY BYROW HSTACK VSTACK |
| Sum by week number | SUMIFS WEEKNUM LAMBDA LET BYROW UNIQUE VSTACK HSTACK |
| Sum by weekday | SUMPRODUCT TEXT WEEKDAY |
| Sum by year | SUMPRODUCT YEAR SUMIFS DATE LET BYROW UNIQUE VSTACK HSTACK |
| Sum columns based on adjacent criteria | SUMPRODUCT |
| Sum entire column | SUM |
| Sum entire row | SUM |
| Sum every n rows | SUM OFFSET |
| Sum every nth column | FILTER SEQUENCE SUMPRODUCT COLUMN MOD |
| Sum every nth row | FILTER SEQUENCE MOD SUM SUMPRODUCT ROW |
| Sum first n matching values | FILTER TAKE SUM |
| Sum first n rows | TAKE SUM OFFSET |
| Sum formulas only | SUMPRODUCT ISFORMULA NOT |
| Sum if begins with | SUMIF SUMIFS |
| Sum if between | SUMIFS |
| Sum if case-sensitive | SUMPRODUCT EXACT |
| Sum if cell contains text in another cell | SUMIF SUMIFS SUMPRODUCT FIND ISNUMBER |
| Sum if cells are equal to | SUMIF SUMIFS |
| Sum if cells are not equal to | SUMIF SUMIFS |
| Sum if cells contain an asterisk | SUMIF SUMIFS FIND FILTER |
| Sum if cells contain both x and y | SUMIFS |
| Sum if cells contain either x or y | SUMIFS SUMPRODUCT ISNUMBER FIND SEARCH |
| Sum if cells contain specific text | SUMIF SUMIFS SUMPRODUCT FIND ISNUMBER |
| Sum if date is between | SUMIFS DATE |
| Sum if date is greater than | SUMIFS SUMIF DATE |
| Sum if ends with | SUMIF SUMIFS |
| Sum if greater than | SUMIF SUMIFS |
| Sum if less than | SUMIF SUMIFS |
| Sum if multiple columns | SUMPRODUCT SUMIFS FILTER SUM GROUPBY BYROW |
| Sum if multiple criteria | SUMIFS |
| Sum if not blank | SUMIFS SUMPRODUCT FILTER SUM |
| Sum if one of many things | SUMIFS SUMPRODUCT ISNUMBER MATCH FILTER |
| Sum if with multiple ranges | SUMIFS LET VSTACK GROUPBY CHOOSECOLS |
| Sum if x or y | SUMIF SUMPRODUCT ISNUMBER MATCH |
| Sum last 30 days | SUMIFS TODAY SUMPRODUCT FILTER |
| Sum last n columns | TAKE OFFSET COLUMNS INDEX SUM TRIMRANGE |
| Sum last n rows | TAKE SUM TRIMRANGE OFFSET INDEX |
| Sum matching columns | SUMPRODUCT LEFT FILTER SUM |
| Sum matching columns and rows | SUMPRODUCT FILTER SUM |
| Sum numbers in single cell | TEXTSPLIT SUM FILTERXML SUBSTITUTE |
| Sum top n values | SUM LARGE SEQUENCE SUMPRODUCT ROW INDIRECT |
| Sum top n values with criteria | SUM LARGE FILTER SEQUENCE IF |
| Sum visible rows in a filtered list | SUBTOTAL AGGREGATE |
| SUMIFS with horizontal range | SUMIFS |
| SUMIFS with multiple criteria and OR logic | SUMIFS SUM |
| SUMPRODUCT with IF | SUMPRODUCT IF |
| Formula | Related Functions |
|---|---|
| Average and ignore errors | AVERAGEIF AGGREGATE AVERAGE IFERROR FILTER |
| Average by group | UNIQUE AVERAGEIFS |
| Average by month | AVERAGEIFS EDATE FILTER AVERAGE |
| Average call time per month | AVERAGEIFS EDATE FILTER AVERAGE TEXT |
| Average hourly pay per day | SUMPRODUCT SUMIFS |
| Average if not blank | AVERAGEIFS FILTER AVERAGE |
| Average if with filter | AVERAGE FILTER SUMPRODUCT IF AVERAGEIFS |
| Average last 3 numeric values | AVERAGE FILTER TAKE LOOKUP LARGE ROW |
| Average last n columns | TAKE AVERAGE OFFSET COUNT INDEX COLUMNS |
| Average last n rows | TAKE AVERAGE OFFSET COUNT |
| Average numbers ignore zero | AVERAGEIF AVERAGEIFS AVERAGE FILTER |
| Average salary by department | UNIQUE AVERAGEIFS |
| Average top 3 scores | LARGE AVERAGE SEQUENCE |
| Average with multiple criteria | AVERAGEIFS |
| Basic average example | AVERAGE AVERAGEIFS AGGREGATE |
| Moving average formula | OFFSET AVERAGE MIN |
| Must pass 4 out of 6 subjects | IF COUNTIF AND |
| Weighted average | SUMPRODUCT SUM AVERAGE TRANSPOSE |
| Formula | Related Functions |
|---|---|
| Basic filter example | FILTER |
| Biggest gainers and losers | FILTER LARGE SMALL SORT |
| Combine data in multiple worksheets | VSTACK FILTER LET CHOOSECOLS |
| Combine ranges | INDEX SEQUENCE ROWS COLUMNS LET |
| Count unique dates ignore time | INT UNIQUE COUNT LET LAMBDA SCAN |
| Count unique values | UNIQUE COUNTA |
| Count unique values with criteria | UNIQUE FILTER LEN SUM COUNTA |
| Detailed LET function example | LET VLOOKUP IF XLOOKUP |
| Distinct values | UNIQUE |
| Dynamic summary count | UNIQUE COUNTIF LET SORT SCAN LAMBDA |
| Dynamic two-way average | UNIQUE TRANSPOSE AVERAGEIFS |
| Dynamic two-way count | UNIQUE TRANSPOSE COUNTIFS LET HSTACK VSTACK |
| Dynamic two-way sum | UNIQUE TRANSPOSE AVERAGEIFS |
| Extract common values from text strings | FILTER XMATCH ISNUMBER LET SORT |
| Extract common values from two lists | FILTER XMATCH ISNUMBER UNIQUE SORT COUNTIF |
| Extract numbers from text | TEXTSPLIT TOROW DROP |
| Filter and exclude columns | FILTER SORT CHOOSECOLS TAKE |
| Filter and sort without errors | FILTER SORT |
| Filter and transpose horizontal to vertical | FILTER TRANSPOSE |
| Filter by column, sort by row | FILTER SORT |
| Filter by date | FILTER MONTH YEAR DATE |
| FILTER case-sensitive | FILTER SEARCH ISNUMBER |
| Filter contains one of many | FILTER ISNUMBER MATCH |
| Filter data between dates | FILTER DATE |
| Filter every nth row | FILTER SEQUENCE MOD ROW |
| Filter exclude blank values | FILTER |
| Filter horizontal data | FILTER TRANSPOSE |
| FILTER last n valid entries | FILTER SEQUENCE INDEX |
| FILTER on first or last n values | FILTER SEQUENCE INDEX |
| FILTER on top n values | FILTER LARGE SORT |
| FILTER on top n values with criteria | FILTER LARGE SORT |
| Filter text contains | FILTER SEARCH ISNUMBER |
| Filter this or that | FILTER |
| Filter to extract matching values | FILTER COUNTIF COUNTIFS |
| FILTER to remove columns | FILTER MATCH ISNUMBER |
| FILTER to show duplicate values | UNIQUE FILTER COUNTIF |
| Filter values within tolerance | FILTER ABS |
| FILTER with complex multiple criteria | FILTER LEFT MONTH NOT |
| Filter with multiple criteria | FILTER |
| FILTER with multiple OR criteria | FILTER ISNUMBER MATCH |
| FILTER with partial match | FILTER SEARCH ISNUMBER |
| Generate random text strings | SORTBY RANDARRAY COUNTA SEQUENCE CHAR |
| Get column totals | BYCOL LAMBDA MMULT ROW TRANSPOSE SEQUENCE |
| Get row totals | BYROW LAMBDA MMULT COLUMN TRANSPOSE SEQUENCE |
| GROUPBY with monthly totals | GROUPBY HSTACK CHOOSECOLS TEXT MONTH SUMPRODUCT EOMONTH |
| GROUPBY with survey results | GROUPBY PERCENTOF HSTACK SORTBY XMATCH CHOOSECOLS |
| LAMBDA append range | LAMBDA LET INDEX SEQUENCE |
| LAMBDA append range horizontal | LAMBDA LET INDEX SEQUENCE |
| LAMBDA contains one of many | LAMBDA SUMPRODUCT SUM ISNUMBER SEARCH |
| LAMBDA contains which things | LAMBDA LET ISNUMBER SEARCH SORTBY |
| LAMBDA count words | LAMBDA SUBSTITUTE TRIM LEN |
| LAMBDA replace characters recursive | LAMBDA LEFT MID LEN |
| LAMBDA split text to array | LAMBDA FILTERXML TRANSPOSE |
| LAMBDA strip characters | LAMBDA LET MID MATCH ISNUMBER |
| LAMBDA strip trailing characters recursive | LAMBDA MID LEN |
| List upcoming birthdays | SORTBY LET TEXT INDEX SEQUENCE XMATCH |
| MAP with AND and OR logic | MAP AND OR |
| Minimum value if unique | UNIQUE MIN COUNTIF IF |
| Random list of names | TAKE SORTBY RANDARRAY ROWS INDEX CHOOSEROWS |
| Random numbers without duplicates | SEQUENCE SORTBY RANDARRAY INDEX |
| Random sort | SORTBY RANDARRAY ROWS |
| Remove blank rows | FILTER BYROW |
| Sort birthdays by month and day | SORTBY TEXT |
| Sort by custom list | SORTBY MATCH |
| Sort by one column | SORT |
| Sort by substring | SORTBY TEXTBEFORE TEXTAFTER |
| Sort by two columns | SORTBY |
| Sort text by length | SORTBY LEN |
| Sort values by columns | SORT SORTBY |
| Sum numbers with text | TEXTBEFORE TEXTAFTER UNIQUE LET LAMBDA BYROW |
| TEXTSPLIT get numeric values | TEXTSPLIT VALUE IFERROR LET |
| Tiered discounts based on quantity | MAP VSTACK DROP IF MIN MAX |
| Unique rows | UNIQUE SORT |
| Unique values | UNIQUE |
| Unique values by count | UNIQUE FILTER COUNTIF |
| Unique values case-sensitive | REDUCE LAMBDA EXACT VSTACK |
| Unique values from multiple ranges | UNIQUE VSTACK |
| Unique values ignore blanks | UNIQUE FILTER |
| Unique values with criteria | UNIQUE FILTER |
| Unique values with multiple criteria | UNIQUE FILTER |
| UNIQUE with non-adjacent columns | FILTER UNIQUE SORT |
| XLOOKUP match any column | MMULT SEQUENCE COLUMNS |
| Formula | Related Functions |
|---|---|
| Cap percentage between 0 and 100 | MIN MAX MEDIAN IF |
| Find lowest n values | SMALL INDEX MATCH |
| First in last out times | MINIFS MAXIFS MIN MAX IF FILTER |
| Large with criteria | LARGE FILTER IF |
| Larger of two values | MAX IF |
| Max by month | MAXIFS MAX TEXT |
| Max of every nth column | MAX FILTER SEQUENCE COLUMNS CHOOSECOLS COLUMN MOD |
| Max value ignore all errors | AGGREGATE MAXIFS |
| Max value on given weekday | FILTER TEXT MAX IF AGGREGATE |
| Max value with variable column | INDEX MATCH MAX FILTER COUNTIF |
| Maximum change | MAX INDEX MATCH XLOOKUP LET HSTACK SORT VSTACK |
| Maximum if multiple criteria | MAX IF MAXIFS FILTER |
| Maximum value | MAX MAXIFS LARGE |
| Maximum value if | MAXIFS MAX IF FILTER BYROW VSTACK HSTACK UNIQUE |
| Minimum if multiple criteria | MIN IF MINIFS |
| Minimum value | MIN MINIFS SMALL |
| Minimum value if | MINIFS MIN FILTER IF BYROW VSTACK HSTACK UNIQUE |
| Name of nth largest value | LARGE INDEX MATCH XLOOKUP |
| Name of nth largest value with criteria | LARGE INDEX MATCH IF XLOOKUP |
| nth largest value | LARGE |
| nth largest value with criteria | LARGE FILTER IF |
| nth largest without duplicates | MAX UNIQUE IF |
| nth smallest value | SMALL |
| nth smallest value with criteria | SMALL |
| Smaller of two values | MIN IF |
| Formula | Related Functions |
|---|---|
| Case sensitive lookup | EXACT INDEX MATCH XLOOKUP |
| Find closest match | XLOOKUP INDEX MATCH ABS MIN |
| Find longest string | XLOOKUP FILTER INDEX MATCH MAX LEN |
| Find longest string with criteria | XLOOKUP INDEX MATCH MAX LEN |
| Find missing values | COUNTIF IF MATCH ISNUMBER |
| Get address of lookup result | CELL XLOOKUP INDEX MATCH |
| Get all matches cell contains | SEARCH ISNUMBER FILTER TEXTJOIN |
| Get cell content at given row and column | ADDRESS INDIRECT INDEX |
| Get employee information with VLOOKUP | VLOOKUP XLOOKUP CHOOSECOLS TAKE DROP |
| Get first match cell contains | XLOOKUP ISNUMBER SEARCH INDEX MATCH AGGREGATE |
| Get first non-blank value in a list | XLOOKUP NOT ISBLANK INDEX MATCH |
| Get first numeric value in a range | XLOOKUP ISNUMBER INDEX MATCH |
| Get first text value in a range | XLOOKUP VLOOKUP INDEX MATCH ISTEXT |
| Get first text value in a row | HLOOKUP XLOOKUP ISTEXT |
| Get information about max value | TAKE SORT MAX MATCH INDEX |
| Get last match | XLOOKUP XMATCH FILTER TAKE LOOKUP INDEX |
| Get last match cell contains | XLOOKUP LOOKUP SEARCH INDEX MATCH ISNUMBER AGGREGATE |
| Get location of value in 2D array | TOCOL IF ADDRESS ROW COLUMN MAP |
| Get nth match | FILTER INDEX CHOOSEROWS SMALL IF MIN ROW |
| Get nth match with INDEX / MATCH | INDEX SMALL IF |
| Get nth match with VLOOKUP | VLOOKUP COUNTIF |
| Get value of last non-empty cell | LOOKUP XLOOKUP |
| INDEX and MATCH advanced example | INDEX MATCH LEFT |
| INDEX and MATCH all matches | INDEX MATCH AND SUM |
| INDEX and MATCH all partial matches | INDEX AGGREGATE ISNUMBER SEARCH |
| INDEX and MATCH approximate match | INDEX MATCH |
| INDEX and MATCH approximate match with multiple criteria | INDEX MATCH IF |
| INDEX and MATCH case-sensitive | INDEX MATCH EXACT XLOOKUP |
| INDEX and MATCH descending order | INDEX MATCH |
| INDEX and MATCH exact match | INDEX MATCH TRANSPOSE |
| Index and match on multiple columns | MMULT COLUMN TRANSPOSE INDEX |
| INDEX and MATCH two-column lookup | INDEX XMATCH XLOOKUP CHOOSECOLS |
| INDEX and MATCH with multiple criteria | INDEX MATCH |
| INDEX and MATCH with variable columns | INDEX XMATCH XLOOKUP CHOOSECOLS |
| INDEX with variable array | INDEX MATCH CHOOSE |
| Join tables with INDEX and MATCH | INDEX MATCH |
| Left lookup with INDEX and MATCH | INDEX MATCH |
| Left lookup with VLOOKUP | VLOOKUP CHOOSE |
| List missing values | FILTER COUNTIF NOT |
| Look up and return to single cell | FILTER TEXTJOIN GROUPBY LAMBDA UNIQUE ARRAYTOTEXT |
| Look up entire column | XLOOKUP INDEX MATCH |
| Look up entire row | XLOOKUP INDEX MATCH |
| Lookup and sum column | INDEX MATCH SUM |
| Lookup first negative value | XLOOKUP FILTER TAKE INDEX MATCH |
| Lookup last file version | LOOKUP ISNUMBER FIND |
| Lookup latest price | LOOKUP |
| Lookup lowest Monday tide | INDEX MATCH IF MIN XLOOKUP |
| Lookup lowest value | INDEX MATCH MIN |
| Lookup number plus or minus N | XLOOKUP ABS INDEX MATCH |
| Lookup up cost for product or service | VLOOKUP |
| Lookup value between two numbers | LOOKUP |
| Lookup with variable sheet name | VLOOKUP INDIRECT |
| Match first does not begin with | INDEX MATCH LEFT |
| Match first error | MATCH ISERROR |
| Match first occurrence does not contain | INDEX MATCH ISNUMBER SEARCH |
| Match long text | MATCH LEFT MID EXACT |
| Match next highest value | INDEX MATCH |
| Max if criteria match | MAX IF MAXIFS |
| Merge tables with VLOOKUP | VLOOKUP |
| Multi-criteria lookup and transpose | INDEX MATCH |
| Multiple chained VLOOKUPs | VLOOKUP IFERROR |
| Multiple matches in comma separated list | TEXTJOIN |
| Multiple matches into separate columns | FILTER UNIQUE SORT SEQUENCE INDEX SMALL IFERROR COLUMN COLUMNS |
| Multiple matches into separate rows | FILTER UNIQUE SORT TRANSPOSE INDEX SMALL IFERROR ROW ROWS |
| Nearest location with XMATCH | INDEX XMATCH |
| Next largest match with the MATCH function | INDEX MATCH |
| Partial match with numbers and wildcard | MATCH TEXT |
| Partial match with VLOOKUP | VLOOKUP |
| Position of first partial match | MATCH INDEX |
| Position of max value in list | MAX MATCH |
| Quantity based discount | XLOOKUP INDEX MATCH IFNA |
| Rank and score with INDEX and MATCH | RANK INDEX MATCH |
| Reverse VLOOKUP example | VLOOKUP CHOOSE INDEX MATCH XLOOKUP |
| Self-contained VLOOKUP | VLOOKUP |
| Step-based lookup example | INDEX MATCH |
| Sum lookup values using SUMIF | SUMIF SUMPRODUCT |
| Sum range with INDEX | INDEX SUM |
| SUMIFS multiple criteria lookup in table | SUMIFS |
| SUMPRODUCT case-sensitive lookup | SUMPRODUCT EXACT |
| Two-way approximate match multiple criteria | INDEX MATCH IF |
| Two-way lookup with INDEX and MATCH | INDEX MATCH |
| VLOOKUP by date | VLOOKUP |
| VLOOKUP calculate grades | VLOOKUP |
| VLOOKUP calculate shipping cost | VLOOKUP MAX |
| VLOOKUP case-sensitive | VLOOKUP EXACT CHOOSE |
| VLOOKUP faster VLOOKUP | VLOOKUP NA |
| VLOOKUP from another sheet | VLOOKUP |
| VLOOKUP from another workbook | VLOOKUP |
| VLOOKUP if blank return blank | VLOOKUP IF |
| VLOOKUP override output | VLOOKUP |
| VLOOKUP tax rate calculation | VLOOKUP |
| VLOOKUP two-way lookup | VLOOKUP MATCH |
| VLOOKUP variable commission split | VLOOKUP |
| VLOOKUP with 2 lookup tables | VLOOKUP |
| VLOOKUP with multiple criteria | VLOOKUP |
| VLOOKUP with multiple criteria advanced | VLOOKUP CHOOSE |
| VLOOKUP with numbers and text | VLOOKUP ISTEXT IFERROR |
| VLOOKUP with two client rates | VLOOKUP |
| VLOOKUP with variable table array | INDIRECT VLOOKUP IF |
| VLOOKUP without #N/A error | VLOOKUP IFERROR IFNA ISNA |
| XLOOKUP approximate match with multiple criteria | XLOOKUP IF FILTER |
| XLOOKUP basic approximate match | XLOOKUP |
| XLOOKUP basic exact match | XLOOKUP |
| XLOOKUP binary search | XLOOKUP INDEX XMATCH |
| XLOOKUP case-sensitive | XLOOKUP EXACT |
| XLOOKUP date of max value | XLOOKUP INDEX MATCH |
| XLOOKUP horizontal lookup | XLOOKUP HLOOKUP |
| XLOOKUP last match | XLOOKUP |
| XLOOKUP latest by date | XLOOKUP MAX |
| XLOOKUP lookup left | XLOOKUP |
| XLOOKUP lookup row or column | XLOOKUP |
| XLOOKUP match text contains | XLOOKUP SEARCH FIND ISNUMBER |
| XLOOKUP rearrange columns | XLOOKUP |
| XLOOKUP return blank if blank | XLOOKUP IF LET |
| XLOOKUP two-way exact match | XLOOKUP INDEX MATCH |
| XLOOKUP wildcard contains substring | XLOOKUP VLOOKUP TRANSPOSE |
| XLOOKUP wildcard match example | XLOOKUP TRANSPOSE |
| XLOOKUP with Boolean OR logic | XLOOKUP INDEX MATCH |
| XLOOKUP with complex multiple criteria | FILTER LEFT MONTH NOT |
| XLOOKUP with logical criteria | XLOOKUP |
| XLOOKUP with multiple criteria | XLOOKUP INDEX MATCH |
| XLOOKUP with regex match | XLOOKUP |
| XLOOKUP without #N/A error | XLOOKUP IFERROR IFNA |
| XMATCH reverse search | XMATCH INDEX |
| XMATCH with multiple criteria | XMATCH XLOOKUP INDEX MATCH |
| Zodiac sign lookup | INDEX MATCH TEXT DATEVALUE IFNA YEAR |
| Formula | Related Functions |
|---|---|
| Calculate sales commission with if | IF IFS |
| If cell begins with x, y, or z | OR LEFT IF COUNTIF |
| If cell contains | IF SEARCH ISNUMBER |
| If cell contains this or that | SEARCH ISNUMBER OR COUNTIF SUM |
| If cell equals | IF |
| If cell is blank | IF ISBLANK |
| If cell is greater than | IF |
| If cell is not blank | IF ISBLANK NOT |
| If cell is this OR that | IF OR |
| If cell is x or y and z | IF OR AND |
| If complete show checkmark | IF UNICHAR CHAR |
| If date is between two dates | IF AND |
| If else | IF IFS VLOOKUP |
| If not blank multiple cells | IF ISBLANK NOT XLOOKUP |
| If NOT this or that | IF OR NOT |
| If this AND that | IF AND |
| If this AND that OR that | IF AND OR |
| IF with boolean logic | IF |
| IF with other calculations | IF AND |
| IF with wildcards | IF COUNTIF SEARCH ISNUMBER |
| Invoice age and status | IF TODAY |
| Nested IF function example | IF IFS |
| Nested IF with multiple AND | IF AND OR |
| Only calculate if not blank | IF COUNT ISBLANK COUNTBLANK AND OR |
| Return blank if | IF ISBLANK COUNTBLANK |
| Formula | Related Functions |
|---|---|
| Categorize text with keywords | INDEX MATCH ISNUMBER SEARCH XLOOKUP |
| Group arbitrary text values | VLOOKUP |
| Group numbers at uneven intervals | LOOKUP |
| Group numbers with VLOOKUP | VLOOKUP |
| Group times into 3 hour buckets | FLOOR |
| Group times into unequal buckets | VLOOKUP |
| If cell contains one of many things | INDEX MATCH ISNUMBER SEARCH |
| Map inputs to arbitrary values | VLOOKUP CHOOSE |
| Map text to numbers | VLOOKUP |
| Running count group by n size | COUNTA CEILING |
| Formula | Related Functions |
|---|---|
| Highlight 3 smallest values with criteria | SMALL AND |
| Conditional formatting based on another cell | |
| Conditional formatting based on another column | |
| Conditional formatting column is blank | OR AND NOT |
| Conditional formatting date past due | TODAY |
| Conditional formatting dates overlap | SUMPRODUCT |
| Conditional formatting highlight target percentage | |
| Conditional formatting last n rows | |
| Find duplicate values in two columns | COUNTIF AND |
| Gantt chart | AND |
| Gantt chart by week | AND |
| Gantt chart time schedule | AND OR |
| Gantt chart with weekends | WEEKDAY |
| Highlight approximate match lookup conditional formatting | LOOKUP OR AND |
| Highlight blank cells | ISBLANK LEN |
| Highlight bottom values | SMALL |
| Highlight cells that begin with | COUNTIF FIND |
| Highlight cells that contain | SEARCH FIND ISNUMBER |
| Highlight cells that contain one of many | SEARCH FIND ISNUMBER SUMPRODUCT |
| Highlight cells that end with | COUNTIF RIGHT LEN EXACT |
| Highlight cells that equal | EXACT |
| Highlight column differences | EXACT NOT |
| Highlight data by quartile | QUARTILE |
| Highlight dates between | DATE AND |
| Highlight dates greater than | DATE |
| Highlight dates in same month and year | DATE TEXT |
| Highlight dates in the next N days | TODAY AND |
| Highlight dates that are weekends | WEEKDAY OR |
| Highlight duplicate columns | SUMPRODUCT COUNTIF |
| Highlight duplicate rows | COUNTIFS COUNTIF SUMPRODUCT |
| Highlight duplicate values | COUNTIF |
| Highlight entire rows | |
| Highlight every other row | ISEVEN ISODD ROW MOD |
| Highlight integers only | MOD |
| Highlight many matching values | COUNTIF |
| Highlight missing values | COUNTIF |
| Highlight multiples of specific value | MOD |
| Highlight numbers that include symbols | ISNUMBER MID |
| Highlight row and column intersection exact match | OR AND |
| Highlight rows that contain | SEARCH FIND |
| Highlight rows with blank cells | COUNTBLANK |
| Highlight rows with dates between | DATE AND |
| Highlight specific day of week | TEXT |
| Highlight top values | LARGE |
| Highlight unique values | COUNTIF |
| Highlight unprotected cells | CELL |
| Highlight values between | AND |
| Highlight values greater than | |
| Highlight values not between X and Y | AND NOT |
| Shade alternating groups of n rows | ROW CEILING ISEVEN ISODD |
| Formula | Related Functions |
|---|---|
| Data validation allow numbers only | ISNUMBER |
| Data validation allow text only | ISTEXT |
| Data validation allow uppercase only | UPPER EXACT AND |
| Data validation allow weekday only | YEAR TODAY |
| Data validation date in next 30 days | AND TODAY |
| Data validation date in specific year | YEAR TODAY |
| Data validation don’t exceed total | SUM |
| Data validation exists in list | COUNTIF |
| Data validation must begin with | EXACT LEFT COUNTIF |
| Data validation must contain specific text | FIND ISNUMBER |
| Data validation must not contain | ISNUMBER SEARCH SUMPRODUCT |
| Data validation must not exist in list | COUNTIF |
| Data validation no punctuation | FIND COUNT |
| Data validation only dates between | AND DATE |
| Data validation require specific multiple | MOD |
| Data validation require unique number | AND ISNUMBER COUNTIF |
| Data validation specific characters only | MATCH COUNT LEN MID INDIRECT |
| Data validation unique values only | COUNTIF |
| Data validation whole percentage only | TRUNC AND |
| Data validation with conditional list | IF |
| Formula | Related Functions |
|---|---|
| Break ties with helper column and COUNTIF | SMALL INDEX MATCH |
| Rank function example | RANK |
| Rank if formula | COUNTIFS RANK |
| Rank race results | RANK |
| Rank values by month | LARGE INDEX MATCH TEXT IF |
| Rank with ordinal suffix | CHOOSE ABS MOD |
| Rank without ties | RANK COUNTIF |
| Formula | Related Functions |
|---|---|
| Get decimal part of a number | TRUNC |
| Get integer part of a number | TRUNC INT |
| Get number at place value | MOD |
| Round a number | ROUND |
| Round a number down | ROUNDDOWN |
| Round a number down to nearest multiple | FLOOR |
| Round a number to nearest multiple | MROUND |
| Round a number up | ROUNDUP |
| Round a number up to nearest multiple | CEILING |
| Round a number up to next half | CEILING |
| Round a price to end in .99 | ROUND MROUND |
| Round by bundle size | CEILING |
| Round number to n significant figures | LET ROUND LOG10 INT ABS |
| Round price to end in .45 or .95 | CEILING |
| Round time to nearest 15 minutes | MROUND CEILING FLOOR |
| Round to nearest 1000 | ROUND |
| Round to nearest 5 | MROUND CEILING FLOOR |
| Formula | Related Functions |
|---|---|
| Add business days to date | WORKDAY WORKDAY.INTL |
| Add days exclude certain days of week | WORKDAY.INTL |
| Add days to date | TODAY EDATE WORKDAY |
| Add decimal hours to time | TIME MOD |
| Add decimal minutes to time | TIME MOD |
| Add months to date | EDATE EOMONTH |
| Add workdays to date custom workweek | WORKDAY.INTL WORKDAY |
| Add years to date | DATE YEAR MONTH DAY EDATE |
| Assign points based on late time | IF VALUE |
| Basic timesheet formula with breaks | MOD |
| Calculate date overlap in days | MAX MIN |
| Calculate days open | TODAY IF ISBLANK |
| Calculate days remaining | TODAY |
| Calculate expiration date | EOMONTH EDATE |
| Calculate hours between two times | IF MOD |
| Calculate retirement date | EDATE YEARFRAC SIGN |
| Calculate time before expiration date | IF DATEDIF |
| Calculate years between dates | YEARFRAC INT |
| Convert date string to date time | DATEVALUE LEFT MID TIMEVALUE |
| Convert date to Julian format | DATE YEAR TEXT |
| Convert date to month and year | TEXT |
| Convert date to text | TEXT |
| Convert decimal hours to Excel time | |
| Convert decimal minutes to Excel time | |
| Convert decimal seconds to Excel time | |
| Convert Excel time to decimal hours | |
| Convert Excel time to decimal minutes | |
| Convert Excel time to decimal seconds | |
| Convert Excel time to Unix time | DATE |
| Convert text date dd/mm/yy to mm/dd/yy | DATE LEFT MID RIGHT TRIM |
| Convert text timestamp into time | TIME MID |
| Convert text to date | DATE LEFT MID RIGHT |
| Convert time to money | |
| Convert time to time zone | MOD |
| Convert Unix time stamp to Excel date | DATE |
| Convert UTC timestamp to Excel datetime | SUBSTITUTE TEXTSPLIT TIME SUM |
| Count birthdays by month | SUMPRODUCT MONTH |
| Count calls at specific times | COUNTIFS |
| Count dates in current month | COUNTIFS EOMONTH SUMPRODUCT N |
| Count day of week between dates | WEEKDAY ROW INDIRECT SUMPRODUCT SEQUENCE |
| Count holidays between two dates | SUMPRODUCT |
| Count times in a specific range | COUNTIFS TIME |
| Create date range from two dates | TEXT IF |
| Custom weekday abbreviation | WEEKDAY CHOOSE |
| Date is same month | MONTH |
| Date is same month and year | IF AND MONTH YEAR TEXT |
| Date is workday | WORKDAY WORKDAY.INTL |
| Days in month | DAY EOMONTH |
| Display the current date | TODAY |
| Display the current date and time | NOW |
| Due date by category | VLOOKUP WORKDAY XLOOKUP |
| Dynamic calendar formula | WEEKDAY CHOOSE TODAY SEQUENCE DATE |
| Dynamic calendar grid | WEEKDAY CHOOSE TODAY |
| Dynamic date list | TODAY ROWS |
| Extract date from a date and time | INT TRUNC |
| Extract date from text string | MID SEARCH REGEXEXTRACT |
| Extract time from a date and time | MOD INT TRUNC TIME ROUND HOUR MINUTE SECOND |
| Filter on dates expiring soon | FILTER SORT AND TODAY |
| Future time intervals | |
| Generate quarter dates | EDATE SEQUENCE EOMONTH |
| Get age from birthday | DATEDIF TODAY DATE YEARFRAC INT |
| Get date from day number | DATE RIGHT LEFT |
| Get day from date | DAY |
| Get day name from date | TEXT WEEKDAY CHOOSE |
| Get days before a date | TODAY |
| Get days between dates | TODAY DAYS |
| Get days between dates ignoring years | DATEDIF |
| Get days, hours, and minutes between dates | INT TEXT |
| Get days, months, and years between dates | DATEDIF LET |
| Get earliest and latest project dates | MINIFS MAXIFS MIN MAX IF |
| Get first day of month | DAY EOMONTH TODAY |
| Get first day of previous month | EOMONTH |
| Get fiscal quarter from date | MONTH CHOOSE |
| Get fiscal year from date | MONTH YEAR |
| Get last day of month | EOMONTH DATE YEAR MONTH |
| Get last weekday in month | EOMONTH WEEKDAY |
| Get last working day in month | WEEKDAY EOMONTH |
| Get Monday of the week | WEEKDAY |
| Get month from date | MONTH DATE |
| Get month name from date | MONTH CHOOSE TEXT |
| Get months between dates | DATEDIF YEARFRAC MONTH YEAR |
| Get most recent day of week | MOD |
| Get next day of week | MATCH TEXT WEEKDAY |
| Get next scheduled event | MIN IF MINIFS INDEX MATCH |
| Get nth day of week in month | WEEKDAY MOD LET EOMONTH MONTH NA |
| Get nth day of year | DATE YEAR |
| Get percent of year complete | YEARFRAC YEAR DATE |
| Get previous Sunday | WEEKDAY CHOOSE |
| Get project end date | WORKDAY WORKDAY.INTL |
| Get project midpoint | WORKDAY WORKDAY.INTL |
| Get project start date | WORKDAY WORKDAY.INTL |
| Get quarter from date | MONTH ROUNDUP |
| Get same date next month | EDATE |
| Get same date next year | EDATE |
| Get week number from date | WEEKNUM ISOWEEKNUM |
| Get work hours between dates | NETWORKDAYS NETWORKDAYS.INTL |
| Get work hours between dates and times | NETWORKDAYS NETWORKDAYS.INTL |
| Get work hours between dates custom schedule | MID ROW INDIRECT WEEKDAY SUMPRODUCT |
| Get workdays between dates | NETWORKDAYS NETWORKDAYS.INTL |
| Get year from date | YEAR |
| Happy birthday message | TEXT TODAY IF AND MONTH DAY |
| Hours that overlap specific time blocks | MIN MAX LET LAMBDA INT MAP SEQUENCE |
| If Monday, roll back to Friday | WEEKDAY IF |
| Join date and text | TEXT |
| Last n days | AND TODAY |
| Last n months | AND TODAY EOMONTH |
| Last n weeks | AND TODAY WEEKDAY |
| Last updated date stamp | TEXT TODAY |
| List holidays between two dates | TEXTJOIN IF |
| Month number from name | MONTH DATEVALUE |
| Next anniversary date | EDATE DATEDIF |
| Next biweekly payday from date | CEILING |
| Next business day 6 months in future | WORKDAY WORKDAY.INTL |
| Next working day | WORKDAY WORKDAY.INTL |
| Pad week numbers with zeros | TEXT WEEKNUM |
| Parse time string to time | TIME RIGHT LEFT MID |
| Previous working day | WORKDAY WORKDAY.INTL |
| Remove time from timestamp | INT TRUNC |
| Sum by fiscal year | MONTH YEAR SUMPRODUCT SUMIF |
| Sum race time splits | SUM |
| Sum time | SUM SUMIF |
| Sum time by week and project | SUMIFS |
| Sum time over 30 minutes | SUMPRODUCT TIME SUMIFS COUNTIFS |
| Sum time with SUMIFS | SUMIFS |
| Time duration with days | TIME |
| Time in hundredths of a second | TIME SECOND TIMEVALUE MOD |
| Time since start in day ranges | IFS IF |
| Timesheet overtime calculation formula | MIN |
| Workdays per month | NETWORKDAYS NETWORKDAYS.INTL EOMONTH |
| Working days in year | NETWORKDAYS NETWORKDAYS.INTL DATE TODAY |
| Working days left in month | NETWORKDAYS NETWORKDAYS.INTL TODAY |
| Year is a leap year | DATE YEAR MONTH MOD AND OR |
| Formula | Related Functions |
|---|---|
| Biweekly pay schedule | SEQUENCE WORKDAY |
| List all dates in a month | SEQUENCE EOMONTH DAY LET |
| List nth weekdays of the month | LET SEQUENCE FILTER EOMONTH TEXT EDATE |
| List workdays between dates | LET SEQUENCE FILTER WEEKDAY WORKDAY.INTL |
| Semimonthly pay schedule | FILTER SEQUENCE LET DAY EOMONTH WORKDAY |
| Sequence of custom days | WORKDAY.INTL SEQUENCE |
| Sequence of days | SEQUENCE |
| Sequence of leap years | SEQUENCE LET FILTER MOD BYROW AND OR |
| Sequence of months | SEQUENCE EDATE EOMONTH |
| Sequence of times | SEQUENCE TIME |
| Sequence of weekends | WORKDAY.INTL WEEKDAY SEQUENCE |
| Sequence of workdays | SEQUENCE WORKDAY.INTL ROW |
| Sequence of years | SEQUENCE DATE YEAR MONTH DAY |
| Formula | Related Functions |
|---|---|
| 10 most common text values | UNIQUE COUNTIF HSTACK SORT TAKE |
| Abbreviate names or words | TEXTSPLIT TEXTJOIN FILTER MID CODE LEN |
| Add a line break with a formula | CHAR |
| Add line break based on OS | INFO CHAR |
| Capitalize first letter in a text string | REPLACE UPPER LEFT MID LEN |
| Cell begins with | LEFT EXACT COUNTIF |
| Cell contains all of many things | SUMPRODUCT ISNUMBER SEARCH COUNTA COUNT |
| Cell contains number | FIND COUNT SEQUENCE MID LEN |
| Cell contains one of many things | SUMPRODUCT ISNUMBER SEARCH |
| Cell contains one of many with exclusions | SUMPRODUCT ISNUMBER SEARCH |
| Cell contains some words but not others | COUNT SEARCH AND |
| Cell contains specific text | SEARCH ISNUMBER FIND REGEXTEST |
| Cell contains specific words | TEXTSPLIT XMATCH COUNT COUNTA LET |
| Cell ends with | RIGHT EXACT COUNTIF |
| Cell equals one of many things | SUMPRODUCT TRIM |
| Clean and reformat telephone numbers | TEXTSPLIT TEXTJOIN SUBSTITUTE |
| Compare two strings | EXACT |
| Conditional message with REPT function | REPT IF AND |
| Convert numbers to text | TEXT |
| Convert text to numbers | VALUE LEFT RIGHT REGEXREPLACE ISTEXT ISNUMBER |
| Count keywords cell contains | SUMPRODUCT MATCH ISNUMBER SEARCH |
| Count line breaks in cell | LEN SUBSTITUTE CHAR ISBLANK |
| Count numbers in text string | COUNT SEQUENCE MID LEN INDIRECT ROW |
| Count specific characters in a range | LEN SUBSTITUTE UPPER SUMPRODUCT |
| Count specific characters in text string | LEN SUBSTITUTE LOWER |
| Count specific words in a cell | LEN SUBSTITUTE UPPER |
| Count specific words in a range | LEN SUBSTITUTE UPPER SUMPRODUCT |
| Count total characters in a cell | LEN |
| Count total characters in a range | LEN SUMPRODUCT |
| Count total words in a cell | LEN SUBSTITUTE TRIM |
| Count total words in a range | SUMPRODUCT LEN SUBSTITUTE TRIM |
| Double quotes inside a formula | CHAR |
| Encode Unicode sequence into text | UNICHAR TEXTSPLIT TEXTJOIN HEX2DEC |
| Extract last two words from cell | MID LEN SUBSTITUTE FIND |
| Extract multiple lines from a cell | TRIM MID SUBSTITUTE REPT LEN |
| Extract nth word from text string | TEXTSPLIT INDEX TRIM MID SUBSTITUTE REPT LEN |
| Extract substring | MID |
| Extract text between parentheses | MID SEARCH |
| Extract word containing specific text | MID MAX FIND SUBSTITUTE REPT TRIM |
| Extract word that begins with specific character | MID LEN FIND SUBSTITUTE REPT TRIM |
| Find and replace multiple values | SUBSTITUTE REDUCE SEQUENCE LAMBDA REGEXREPLACE INDEX |
| Find nth occurrence of character | SUBSTITUTE FIND TEXTBEFORE LEN |
| Get first word | LEFT FIND ISERROR |
| Get last line in cell | TRIM SUBSTITUTE RIGHT REPT CHAR |
| Get last word | TRIM SUBSTITUTE RIGHT REPT |
| Get Unicode Sequence from text | UNICODE REGEXEXTRACT BASE TEXTJOIN |
| Join cells with comma | TRIM SUBSTITUTE TEXTJOIN |
| MAC address format | TEXTJOIN SEQUENCE MID |
| Make words plural | IF IFNA VLOOKUP |
| Most frequent text with criteria | INDEX MATCH MODE IF |
| Most frequently occurring text | INDEX MATCH MODE |
| Normalize text | SUBSTITUTE TRIM LOWER |
| Number to words | LET LAMBDA MOD MAP INT INDEX CHOOSE ISOMITTED |
| Pad text to equal length | REPT LEN |
| Remove characters from right | LEFT LEN REGEXREPLACE REPLACE |
| Remove file extension from filename | LEFT FIND |
| Remove first character | REPLACE RIGHT LEN LEFT |
| Remove last word | TEXTBEFORE TRIM MID FIND SUBSTITUTE LEN |
| Remove leading and trailing spaces from text | CLEAN TRIM SUBSTITUTE |
| Remove line breaks | SUBSTITUTE CLEAN |
| Remove text by matching | SUBSTITUTE |
| Remove text by position | REPLACE |
| Remove text by variable position | REPLACE FIND |
| Remove unwanted characters | SUBSTITUTE CHAR CODE LEFT CLEAN |
| Replace one character with another | SUBSTITUTE |
| Replace one delimiter with another | TEXTSPLIT TEXTJOIN SUBSTITUTE CHAR |
| Reverse text string | TEXTJOIN MID INDIRECT SEQUENCE |
| Sort comma separated values | TEXTSPLIT SORT TEXTJOIN TRIM FILTERXML |
| Split comma-separated values to multiple columns | TEXTSPLIT REDUCE LAMBDA DROP FILTERXML |
| Split dimensions into three parts | TEXTSPLIT LEFT RIGHT MID LEN SUBSTITUTE |
| Split dimensions into two parts | SUBSTITUTE LEFT RIGHT FIND |
| Split numbers from units of measure | MAX LEFT RIGHT ISNUMBER VALUE MID |
| Split text and numbers | FIND MIN RIGHT LEFT |
| Split text string at specific character | TEXTBEFORE TEXTAFTER LEFT RIGHT LEN FIND |
| Split text string to character array | REGEXEXTRACT MID SEQUENCE LEN |
| Split text with delimiter | TRIM MID SUBSTITUTE REPT LEN |
| Strip html from text or numbers | MID LEN |
| Strip non-numeric characters | REGEXREPLACE TEXTJOIN SEQUENCE MID LEN ROW INDIRECT |
| Strip numeric characters from cell | TEXTJOIN MID ROW INDIRECT SEQUENCE LET |
| Translate letters to numbers | TEXTJOIN VLOOKUP MID ROW T |
| Trim text to n words | LEFT SUBSTITUTE FIND |
| Validate strong password | REGEXTEST COUNT FIND LEN UPPER ISERROR |
| Formula | Related Functions |
|---|---|
| Annual compound interest schedule | FV |
| Annuity solve for interest rate | RATE |
| Bond valuation example | PV FV PRICE |
| CAGR formula examples | GEOMEAN RRI |
| Calculate compound interest | FV |
| Calculate cumulative loan interest | CUMIPMT |
| Calculate cumulative loan principal payments | CUMPRINC |
| Calculate interest for given period | PPMT |
| Calculate interest rate for loan | RATE |
| Calculate loan interest in given year | CUMIPMT |
| Calculate original loan amount | PV |
| Calculate payment for a loan | PMT |
| Calculate payment periods for loan | NPER |
| Calculate periods for annuity | PMT PV FV |
| Calculate principal for given period | PPMT IPMT |
| Calculate simple interest | |
| Compare effect of compounding periods | FV |
| Currency exchange rate example | STOCKHISTORY |
| Effective annual interest rate | EFFECT RRI |
| Estimate mortgage payment | PMT PV FV |
| Future value of annuity | FV PV |
| Future value vs. Present value | PV FV |
| Get current stock price | FIELDVALUE STOCKHISTORY |
| Get stock price (latest close) | STOCKHISTORY TODAY LOOKUP INDEX |
| Get stock price last n days | STOCKHISTORY SORT |
| Get stock price last n months | STOCKHISTORY TODAY EDATE INDEX |
| Get stock price on specific date | STOCKHISTORY |
| Income tax bracket calculation | IF LET VSTACK DROP XLOOKUP XMATCH FILTER OFFSET CHOOSECOLS |
| Mortgage payment schedule | LET SEQUENCE SCAN PMT IPMT PPMT HSTACK VSTACK |
| NPV formula for net present value | NPV |
| Payment for annuity | PMT PV FV |
| Present value of annuity | PV FV |
| Required recovery rate | |
| Simple investing worksheet | FV |
| Tax rate calculation with fixed base | IF |
| Tax rate calculation with two rates | IF |
| Formula | Related Functions |
|---|---|
| Area of a circle | PI POWER |
| Area of a parallelogram | |
| Area of a trapezoid | |
| Area of a triangle | |
| Circumference of a circle | PI |
| Distance formula | SQRT |
| Pythagorean theorem | |
| Surface area of a cone | PI POWER |
| Surface area of a cylinder | PI POWER |
| Surface area of a sphere | PI POWER |
| Volume of a cone | PI POWER |
| Volume of a cylinder | PI POWER |
| Volume of a rectangular prism | |
| Volume of a sphere | PI POWER |
| Formula | Related Functions |
|---|---|
| Count errors in all sheets | SUMPRODUCT ISERROR INDIRECT HYPERLINK ISREF |
| Dynamic workbook reference | INDIRECT |
| Dynamic worksheet reference | INDIRECT |
| Get full workbook name and path | CELL |
| Get sheet name only | CELL TEXTAFTER MID FIND |
| Get workbook name and path without sheet | CELL LEFT FIND SUBSTITUTE LET |
| Get workbook name only | CELL TEXTAFTER TEXTBEFORE MID FIND |
| Get workbook path only | TEXTBEFORE CELL LEFT FIND |
| Indirect named range different sheet | INDIRECT |
| List sheet index numbers | SHEET |
| List sheet names with formula | TEXTAFTER TRANSPOSE INDEX MID ROW NOW T |
| Worksheet name exists | ISREF INDIRECT |
| Formula | Related Functions |
|---|---|
| Create email address from name | LEFT LOWER CONCAT |
| Create email with display name | CONCAT CONCATENATE TEXTJOIN |
| Get domain from email address | TEXTAFTER TEXTSPLIT RIGHT LEN FIND |
| Get domain name from URL | TEXTAFTER TEXTBEFORE LEFT FIND |
| Get name from email address | TEXTSPLIT TEXTBEFORE LEFT FIND |
| Get page from URL | TEXTAFTER MID LEN FIND |
| Get top level domain (TLD) | TEXTAFTER RIGHT LEN SUBSTITUTE FIND |
| Remove protocol from URL | MID RIGHT LEN FIND |
| Remove trailing slash from url | LEN RIGHT LEFT |
| Formula | Related Functions |
|---|---|
| Get first name from name | TEXTBEFORE LEFT FIND |
| Get first name from name with comma | TEXTAFTER RIGHT LEN FIND |
| Get last name from name | TEXTAFTER MID LEN SUBSTITUTE FIND |
| Get last name from name with comma | TEXTBEFORE LEFT FIND |
| Get middle name from full name | TEXTAFTER TEXTBEFORE MID FIND IFERROR |
| Join first and last name | TEXTJOIN CONCAT CONCATENATE |
| Put names into proper case | PROPER TRIM TEXTBEFORE TEXTAFTER |
| Split full name into parts | LET TEXTSPLIT INDEX COUNTA DROP TEXTJOIN HSTACK |
| Formula | Related Functions |
|---|---|
| Calculate percent variance | ABS |
| Calculate percentage of number | |
| Decrease by percentage | |
| Difference is within specific percentage | IF ABS |
| Get amount with percentage | |
| Get original number from percent change | |
| Get original price from percentage discount | |
| Get percent change | |
| Get percentage discount | |
| Get percentage of total | |
| Get profit margin percentage | |
| Get total from percentage | |
| Increase by percentage | |
| Percent of goal | |
| Percent of students absent | |
| Percent sold | |
| Project complete percentage | COUNTA |
| Formula | Related Functions |
|---|---|
| Random date between two dates | RANDBETWEEN WORKDAY |
| Random number between two numbers | RANDBETWEEN RANDARRAY |
| Random number from fixed set of options | CHOOSE RANDBETWEEN |
| Random number weighted probability | RAND MATCH INDEX |
| Random text values | CHOOSE RANDBETWEEN |
| Random times at specific intervals | RAND |
| Random value from list or table | INDEX RANDBETWEEN ROWS |
| Randomly assign data to groups | RANDBETWEEN CHOOSE RANDARRAY ROWS |
| Randomly assign people to groups | INDEX RAND RANK ROUNDUP SEQUENCE RANDARRAY SORTBY LET |
| Formula | Related Functions |
|---|---|
| Add row numbers and skip blanks | COUNTA IF ISBLANK |
| Address of first cell in range | ADDRESS ROW COLUMN CELL |
| Address of last cell in range | ADDRESS ROW COLUMN ROWS COLUMNS |
| All cells in range are blank | SUMPRODUCT |
| All values in a range are at least | COUNTIF NOT |
| Automatic row numbers | SEQUENCE ROW INDEX COUNTA |
| Combine ranges with CHOOSE | CHOOSE |
| Count cells in range | ROWS COLUMNS COUNTA |
| Count visible columns | CELL N SUM |
| COUNTIFS with variable range | COUNTIFS OFFSET ADDRESS INDIRECT ROW |
| Define range based on cell value | INDEX OFFSET |
| Dynamic named range with INDEX | INDEX COUNTA TRIMRANGE |
| Dynamic named range with OFFSET | OFFSET COUNTA TRIMRANGE |
| Dynamic range between two matches | XLOOKUP INDEX MATCH COUNT |
| First column number in range | ROWS MIN |
| First match between two ranges | INDEX MATCH COUNTIF |
| First row number in range | ROW MIN |
| Get address of named range or table | ADDRESS ROW COLUMN ROWS COLUMNS LET TAKE LAMBDA TOCOL |
| Get relative column numbers in range | COLUMN SEQUENCE COLUMNS |
| Get relative row numbers in range | ROW SEQUENCE ROWS |
| Last column number in range | COLUMN COLUMNS MIN |
| Last n rows | TAKE SORTBY SEQUENCE ROW ROWS INDEX |
| Last row in mixed data with blanks | MATCH |
| Last row in mixed data with no blanks | COUNTA |
| Last row in numeric data | MATCH |
| Last row in text data | MATCH REPT |
| Last row number in range | ROW ROWS MIN |
| Multiple cells are equal | AND EXACT |
| Multiple cells have same value | COUNTIF AND |
| Multiple cells have same value case sensitive | EXACT SUMPRODUCT COUNTA |
| Multiple columns are equal | AND COUNTIF |
| Range contains a value not in another range | SUMPRODUCT MATCH ISNA |
| Range contains numbers | SUMPRODUCT ISNUMBER |
| Range contains specific date | COUNTIFS DATE TODAY |
| Row is blank | SUMPRODUCT BYROW LAMBDA |
| Total columns in range | COLUMNS |
| Total rows in range | ROWS |
| Formula | Related Functions |
|---|---|
| Automatic row numbers in Table | ROW INDEX |
| Average last N values in a table | INDEX AVERAGE ROWS |
| Basic inventory formula example | SUMIFS |
| Count table columns | COLUMNS |
| Count table rows | ROWS |
| COUNTIFS with variable table column | COUNTIFS INDEX MATCH INDIRECT |
| Dynamic reference to table | INDIRECT |
| Get column index in Excel Table | MATCH |
| Get column name from index in table | INDEX |
| Percentile IF in table | PERCENTILE |
| Running count in Table | INDEX SUM |
| Running total in Table | INDEX SUM |
| Sum multiple tables | SUM |
| SUMIFS vs other lookup formulas | SUMIFS INDEX MATCH LOOKUP XLOOKUP SUMPRODUCT |
| SUMIFS with Excel Table | SUMIFS |
| Two-way lookup VLOOKUP in a Table | VLOOKUP MATCH |
| Two-way summary with SUMIFS | SUMIFS |
| Formula | Related Functions |
|---|---|
| How to fix a circular reference error | |
| How to fix the #### (hashtag) error | |
| How to fix the #CALC! error | IFERROR ISERROR ERROR.TYPE |
| How to fix the #DIV/0! error | IFERROR ISERROR ERROR.TYPE |
| How to fix the #N/A error | VLOOKUP IFERROR MATCH |
| How to fix the #NAME? error | IFERROR ISERROR ERROR.TYPE |
| How to fix the #NULL! error | IFERROR ISERROR ERROR.TYPE |
| How to fix the #NUM! error | IFERROR ISERROR ERROR.TYPE |
| How to fix the #REF! error | ISREF IFERROR |
| How to fix the #SPILL! error | IFERROR ISERROR ERROR.TYPE |
| How to fix the #VALUE! error | IFERROR ISERROR ERROR.TYPE |
| Formula | Related Functions |
|---|---|
| Abbreviate state names | VLOOKUP INDEX MATCH |
| Add leading zeros to numbers | TEXT |
| All dates in chronological order | IF SUMPRODUCT SORT |
| Basic array formula example | MAX MIN |
| Basic attendance tracking formula | COUNTIF |
| Basic error trapping example | IFERROR |
| Basic in cell histogram | REPT CHAR |
| Basic numeric sort formula | RANK COUNTIF |
| Basic outline numbering | COUNTA MID FIND LEN |
| Basic text sort formula | RANK COUNTIF |
| BMI calculation formula | CONVERT POWER |
| Build hyperlink with VLOOKUP | HYPERLINK VLOOKUP |
| Calculate a ratio from two numbers | GCD |
| Calculate win loss tie totals | SUMPRODUCT |
| Cap percentage at 100 | MIN |
| Cap percentage at specific amount | MIN |
| Carry-on baggage Inches to centimeters | CONVERT |
| Cash denomination calculator | SUMPRODUCT INT LET SCAN MOD DROP HSTACK |
| Celsius to Fahrenheit conversion | CONVERT NOT ISBLANK |
| Change negative numbers to positive | ABS |
| Check register balance | IF AND ISBLANK |
| Coefficient of variation | STDEV.P STDEV.S AVERAGE |
| Conditional median with criteria | MEDIAN |
| Conditional mode with criteria | MODE |
| Convert column letter to number | COLUMN INDIRECT |
| Convert column number to letter | ADDRESS SUBSTITUTE TEXTBEFORE |
| Convert expense time units | INDEX MATCH |
| Convert feet and inches to inches | LEFT FIND MID SUBSTITUTE |
| Convert inches to feet and inches | INT MOD REPT ABS LET |
| Convert negative numbers to zero | MAX |
| Convert numbers to 1 or 0 | IF |
| Convert pounds to kilograms | CONVERT |
| Copy value from every nth column | OFFSET COLUMN |
| Copy value from every nth row | OFFSET ROW |
| Cost of living adjustment | |
| Count consecutive monthly orders | FREQUENCY MAX IF |
| Count values out of tolerance | SUMPRODUCT ABS |
| Count with repeating values | ROUNDUP COLUMN ROW |
| Course completion status summary | COUNTIFS |
| Course completion summary with criteria | COUNTIFS |
| Create array of numbers | INDIRECT ROW |
| Cube root of number | POWER |
| Customer is new | COUNTIFS |
| Display sorted values with helper column | INDEX MATCH ROWS |
| Dropdown sum with all option | SUMIF SUM IF |
| Easy bundle pricing with SUMPRODUCT | SUMPRODUCT |
| Expense begins on specific month | IF |
| Extract unique items from a list | INDEX MATCH COUNTIF LOOKUP |
| Filter values in array formula | ISNUMBER MATCH IF COUNT |
| Fixed value every N columns | MOD COLUMN |
| Flag first duplicate in a list | COUNTIF COUNTIFS |
| Flip table rows to columns | TRANSPOSE |
| Forecast vs actual variance | SUMIFS |
| Formula with locked absolute reference | INDIRECT |
| Get date associated with last entry | LOOKUP |
| Get first entry by month and year | INDEX MATCH TEXT |
| Get last entry by month and year | LOOKUP TEXT |
| Get pivot table grand total | GETPIVOTDATA |
| Get pivot table subtotal | GETPIVOTDATA |
| Get pivot table subtotal grouped date | GETPIVOTDATA |
| Hyperlink to first blank cell | HYPERLINK CELL INDEX MATCH |
| Hyperlink to first match | HYPERLINK CELL INDEX MATCH |
| Increment a calculation with ROW or COLUMN | ROW COLUMN |
| Increment a number in a text string | RIGHT TEXT |
| Increment cell reference with INDIRECT | INDIRECT CELL |
| Leave a comment in a formula | N |
| Link to multiple sheets | HYPERLINK CELL INDEX MATCH |
| List most frequently occurring numbers | MODE ISNUMBER MATCH |
| Longest winning streak | SCAN FREQUENCY MAX IF LET FILTER VSTACK DROP |
| Lookup last file revision | SEARCH ISERROR MAX INDEX IF ROW |
| Mark rows with logical tests | COUNTIFS IF |
| Most frequently occurring number | MODE |
| Multiplication table formula | |
| New customers per month | COUNTIFS EOMONTH |
| Nightly hotel rate calculation | SUMPRODUCT FILTER INDEX MATCH |
| Normalize size units to Gigabytes | MATCH LEFT RIGHT |
| nth root of number | POWER |
| Number is whole number | MOD TRUNC INT |
| Odometer gas mileage log | SUM MAX MIN |
| One or the other not both | XOR |
| Pad a number with zeros | TEXT REPT |
| Parse XML with formula | FILTERXML |
| Random sort formula | INDEX MATCH |
| Range contains duplicates | OR COUNTIF SUMPRODUCT |
| Range contains one of many substrings | SUMPRODUCT COUNTIF |
| Range contains one of many values | SUMPRODUCT ISNUMBER SEARCH |
| Range contains specific text | COUNTIF |
| Repeat fixed value every 3 months | MOD DATEDIF |
| Repeat range of values | SEQUENCE MOD CHOOSEROWS CHOOSECOLS |
| Repeat sequence of numbers | SEQUENCE MOD |
| Return array with INDEX function | INDEX MATCH N |
| Reverse a list or range | ROWS ROW SORTBY SEQUENCE TEXTSPLIT TEXTJOIN REDUCE INDEX |
| Risk Matrix Example | INDEX MATCH |
| Score quiz answers with key | SUM COUNTA |
| Search entire worksheet for value | COUNTIF |
| Search multiple worksheets for value | COUNTIF INDIRECT |
| Send email with formula | HYPERLINK |
| Show formula text with formula | FORMULATEXT IFERROR ISFORMULA |
| Simple currency conversion | VLOOKUP IF |
| Sort and extract unique values | MMULT TRANSPOSE INDEX MATCH |
| Sort numbers ascending or descending | SMALL LARGE |
| Sort text and numbers with formula | RANK COUNTIF COUNT |
| Split payment across months | AND |
| Square root of number | SQRT POWER ABS |
| Standard deviation calculation | STDEV.P STDEV.S STDEV STDEVP |
| Student class enrollment with table | IF COUNTIF |
| Sum every 3 cells | OFFSET COLUMN |
| Sum Roman numbers | ARABIC ROMAN SUMPRODUCT SUM |
| Sum text values like numbers | INDEX MATCH N |
| Text is greater than number | COUNTIF COUNTIFS SUMPRODUCT ISNUMBER |
| Transpose table without zeros | TRANSPOSE IF |
| Unwrap column into fields | OFFSET TRANSPOSE ROW |
| Validate input with check mark | IF COUNTIF |
| Value exists in a range | COUNTIF MATCH ISNUMBER |
| Value is between two numbers | AND MAX MIN |
| Value is within tolerance | IF ABS |
| Volunteer hours requirement calculation | AND COUNTIF SUM |
Explanation
The default behavior of the MATCH function is to match the “next smallest” value in a list that’s sorted in ascending order. Essentially, MATCH moves forward in the list until it encounters a value larger than the lookup value, then drops back to the previous value.
So, when lookup values are sorted in ascending order, both of these formulas return “next smallest”:
=MATCH(value,array) // default
=MATCH(value,array,1) // explicit
However, by setting match type to -1, and sorting lookup values in descending order, MATCH will return the next largest match. So, as seen in the example:
=MATCH(F6,length,-1)
returns 4, since 400 is the next largest match after 364.
Find associated cost
The full INDEX/MATCH formula to retrieve the associated cost in cell F8 is:
=INDEX(cost,MATCH(F6,length,-1))
Explanation
Working from the inside out, we use the DATEDIF function to calculate how many complete years are between the original anniversary date and the “as of” date, where the as of date is any date after the anniversary date:
DATEDIF(B5,C5,"y")
Note: in this case, we are arbitrarily fixing the “as of” date as June 1, 2017 in all examples.
Because we are interested in the next anniversary date, we add 1 to the DATEDIF result, then multiply by 12 to convert to years to months.
Next, the month value goes into the EDATE function, with the original date from column B. The EDATE function rolls the original date forward by the number of months given in the previous step which creates the next upcoming anniversary date.
As of today
To calculate the next anniversary as of today, use the TODAY() function for the “as of” date:
=EDATE(date,(DATEDIF(date,TODAY(),"y")+1)*12)
Explanation
This formula depends on the CEILING function , which rounds numbers up to a given multiple. It works because of how dates work in Excel’s default 1900 date system, where the first day is the number 1, which is Sunday, January 1, 1900.
In this scheme, the first Friday is day number 6, the second Friday is day number 13, and day 14 is the second Saturday. What this means is that all second Saturdays in the future are evenly divisible by 14.
The formula uses this fact to figure out 2nd Saturdays, then subtracts 1 to get the Friday previous.
The other Friday
If you need to get the alternate Friday in an every-other-Friday scheme, you can use this version of the formula:
=CEILING(A1+8,14)-8
The idea is the same, but the formula needs to roll forward 8 days to get to an even multiple of 14. Once CEILING returns a date, 8 days are subtracted to move back to the Friday previous.
Note: I ran into this formula as an answer on stack overflow by the awesome Barry Houdini.
Explanation
Working from the inside out, EDATE first calculates a date 6 months in the future. In the example shown, that date is December 24, 2015.
Next, the formula subtracts 1 day to get December 23, 2015, and the result goes into the WORKDAY function as the start date, with days = 1, and the range B9:B11 provided for holidays.
WORKDAY then calculates the next business day one day in the future, taking into account holidays and weekends.
If you need more flexibility with weekends, you can use WORKDAY.INTL.
Explanation
In the worksheet shown, column B contains 12 dates. The goal is to calculate the next working day after each date, taking into account weekends (Saturday and Sunday) and the holidays listed in column F. In other words, the formula should automatically skip weekends and any dates defined as non-working days.
WORKDAY function
The WORKDAY function takes a date and returns the next working day n days in the future or past. You can use WORKDAY to calculate things like ship dates, delivery dates, and completion dates that need to take into account working and non-working days. The generic syntax for WORKDAY looks like this:
=WORKDAY(start_date,days,[holidays])
Where days is a number (n) and holidays is an optional range that contains non-working dates. For this problem, we want the next working day, so we provide 1 for days . The formula in D5, copied down, looks like this:
=WORKDAY(B5,1,holidays)
Where holidays is the named range F5:F15, which contains days that should be excluded. The WORKDAY function is fully automatic. Given a valid date, it will add days to the date, skipping weekends and holidays. Named ranges behave like absolute references by default, so the range will not change as the formula is copied down. Without a named range, you will need to lock the reference like this:
=WORKDAY(B5,1,$F$5:$F$15)
As the formula is copied down, it returns the next business day after the starting date in column B. Saturdays and Sundays are automatically skipped, as well as any dates that appear in the range F5:F15.
Current date or next workday
There may be situations where you want to return the current date when it’s a working day or the next working date if not. To do this, you can adjust the formula like so:
=WORKDAY(B5-1,1,holidays)
Here, we first subtract 1 day from the date inside the WORKDAY function, then feed that date to WORKDAY as the start_date . WORKDAY then moves forward one day to the original date and checks the result. If the original date is a working day, WORKDAY returns the date unchanged. Otherwise, WORKDAY will continue to move forward one day at a time, skipping weekends and holidays along the way, until it finds a valid workday. You can see the result in the worksheet above.
Custom weekends
The WORKDAY function defines a weekend as Saturday and Sunday only. If you need more flexibility on which days of the week are considered weekends or working days, use the WORKDAY.INTL function instead. For example, to calculate the next working day for this example with a standard work week of Monday-Thursday, where weekend days are Friday, Saturday, and Sunday, you can use WORKDAY.INTL like this:
=WORKDAY.INTL(B5,1,"0000111",holidays)
WORKDAY.INTL includes an extra argument called weekend that can be provided as a string of 1s and 0s like “0000111”. In this scheme, a 1 indicates a weekend and a 0 indicates a workday. For more details, see How to use the WORKDAY.INTL function .
