Here are some concepts and formulas for Excel, sortable and searchable by keyword.
| Category | Subcategory | Formula | Syntax | Description | Use Case | Keywords |
|---|---|---|---|---|---|---|
| Lookup | Basic Lookup | VLOOKUP | #N/A | Searches leftmost column of a table, returns the value N columns to the right. Last arg: FALSE=exact match, TRUE=approximate. | Find employee name by ID; match department codes to department names. Column to search must be the leftmost in the range. | lookup, vlookup, search, find, match, table, reference, id |
| Lookup | Basic Lookup | HLOOKUP | !ERROR! D3 -> D3 -> Cyclic Reference in Formula | Horizontal version of VLOOKUP — searches across the top row and returns a value from a specified row below. | Look up values in wide transposed tables; less common than VLOOKUP but same logic rotated 90 degrees. | lookup, hlookup, horizontal, search, row, table |
| Lookup | Modern Lookup | XLOOKUP | N/A | Modern VLOOKUP replacement. Args: lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode:-1=from bottom]. | Searches left or right, returns multiple columns, built-in error handling, reverse search. Office 365/2021+. | lookup, xlookup, search, find, modern, flexible, error handling |
| Lookup | Flexible Lookup | INDEX | match, position, find, row, search, index | Returns the value at a specified row/column position within a range. Row 0 = entire column; Col 0 = entire row. | Use standalone to return a value by position, or combined with MATCH for the most flexible two-way lookup pattern. | index, position, reference, array, row, column |
| Lookup | Flexible Lookup | MATCH | #N/A | Returns the relative position of a lookup value in a range. Match type: 0=exact, 1=less than (sorted asc), -1=greater than (sorted desc). | Pair with INDEX to build direction-agnostic lookups; find row number to feed into OFFSET or other functions. | match, position, find, row, search, index |
| Lookup | Flexible Lookup | INDEX+MATCH | #N/A | Classic two-formula combo: MATCH finds the position, INDEX retrieves the value. Works in any direction. | Most flexible lookup — searches any column, returns any column, handles unsorted data. Gold standard before XLOOKUP. | index, match, lookup, flexible, two-way, any direction |
| Lookup | Two-Way Lookup | INDEX+MATCH (2D) | !ERROR! D8 -> D3 -> D3 -> Cyclic Reference in Formula | Uses two MATCH calls to find both row and column position, enabling a true two-dimensional table lookup. | Look up a value at the intersection of a row and column — like a rate table or cross-tab matrix. | two-way, 2d, matrix, row, column, cross-tab, rate table |
| Lookup | Dynamic Reference | OFFSET | !ERROR! D9 -> D3 -> D3 -> Cyclic Reference in Formula | Returns a range offset from a starting cell by a number of rows and columns. Args: reference, rows, cols, [height], [width]. | Build dynamic named ranges; create rolling window summaries; shift reference point based on input values. | offset, dynamic, range, shift, rows, columns, rolling |
| Lookup | Dynamic Reference | INDIRECT | 0 | Evaluates a text string as a cell reference at runtime. Reference is built dynamically from text concatenation. | Build cross-sheet references driven by a dropdown; construct dynamic range names from cell values. | indirect, dynamic, text, reference, sheet, range, variable |
| Lookup | Selection | CHOOSE | Feb | Returns the Nth value from a list of options. Index must be 1 or greater. | Map a number to a label; build scenario selectors; create switchable data source references. | choose, select, option, list, index, map, scenario |
| Aggregate | Sum | SUM | 0 | Adds all numeric values in a range. Ignores text and blank cells. | Total charges, total visits, budget rollups. The most fundamental Excel function. | sum, add, total, aggregate |
| Aggregate | Sum | SUMIF | !ERROR! D13 -> D117 -> D3 -> D3 -> Cyclic Reference in Formula | Sums values in one range where a single condition is met. Args: range, criteria, sum_range. | Total charges for one state; sum only completed records; total by a single category. | sumif, sum, conditional, criteria, single |
| Aggregate | Sum | SUMIFS | !ERROR! D14 -> D117 -> D3 -> D3 -> Cyclic Reference in Formula | Sums values where ALL multiple criteria are met. Args: sum_range, criteria_range1, criteria1, criteria_range2, criteria2… | Total charges for TX programs that are Critical — the single most-used aggregate formula in data analytics. | sumifs, sum, multiple, criteria, conditional, aggregate |
| Aggregate | Sum | SUMPRODUCT | !ERROR! D15 -> D3 -> D3 -> Cyclic Reference in Formula | Multiplies corresponding elements of arrays and returns their sum. Works like SUMIFS but more flexible; no CTRL+SHIFT+ENTER needed. | Weighted averages; conditional sums with complex criteria; matrix calculations. | sumproduct, weighted, average, multiply, array, flexible |
| Aggregate | Count | COUNT | !ERROR! D16 -> D3 -> D3 -> Cyclic Reference in Formula | Counts cells containing numeric values only. Blanks and text are ignored. | Count how many records have a charge entered; verify numeric completeness. | count, numeric, cells |
| Aggregate | Count | COUNTA | 99 | Counts all non-blank cells regardless of data type (text, numbers, errors). | Count total records in a column; verify no blanks in a required field. | counta, count, non-blank, all |
| Aggregate | Count | COUNTBLANK | 0 | Counts empty cells in a range. | Audit data completeness; find missing required fields; quality control checks. | countblank, blank, empty, missing, count |
| Aggregate | Count | COUNTIF | 0 | Counts cells meeting a single condition. | Count how many programs are Critical; duplicate detection (COUNTIF > 1 = duplicate). | countif, count, single, criteria, conditional |
| Aggregate | Count | COUNTIFS | 0 | Counts rows where ALL specified criteria are met. | Count TX programs that are Critical; multi-condition record counts. | countifs, count, multiple, criteria, conditional |
| Aggregate | Average | AVERAGE | !ERROR! D21 -> D3 -> D3 -> Cyclic Reference in Formula | Returns the arithmetic mean of a range. Ignores blank cells (treats them as if they don’t exist). | Average compliance score, average charge, average satisfaction rating. | average, mean, aggregate |
| Aggregate | Average | AVERAGEIF | !ERROR! D22 -> D117 -> D3 -> D3 -> Cyclic Reference in Formula | Averages values where a single condition is met. | Average score for TX programs only; average charge for one visit type. | averageif, average, conditional, single |
| Aggregate | Average | AVERAGEIFS | !ERROR! D23 -> D117 -> D3 -> D3 -> Cyclic Reference in Formula | Averages values where ALL multiple criteria are met. | Average compliance score for On Track TX programs only. | averageifs, average, multiple, criteria |
| Aggregate | Stats | MIN | !ERROR! D24 -> D3 -> D3 -> Cyclic Reference in Formula | Returns the smallest numeric value in a range. | Find lowest satisfaction score; identify minimum charge or headcount. | min, minimum, smallest, stats |
| Aggregate | Stats | MAX | !ERROR! D25 -> D3 -> D3 -> Cyclic Reference in Formula | Returns the largest numeric value in a range. | Find highest escalation count; identify peak charge; find maximum value. | max, maximum, largest, stats |
| Aggregate | Stats | MEDIAN | !ERROR! D26 -> D3 -> D3 -> Cyclic Reference in Formula | Returns the middle value when data is sorted — less affected by outliers than AVERAGE. | Median complaint count; typical charge without being skewed by extreme values. | median, middle, stats, distribution |
| Aggregate | Stats | STDEV | !ERROR! D27 -> D3 -> D3 -> Cyclic Reference in Formula | Calculates standard deviation (spread) of a sample. Use STDEVP for an entire population. | Measure variability in compliance scores; identify how spread out your data is. | stdev, standard deviation, variability, spread, stats |
| Aggregate | Stats | PERCENTILE | !ERROR! D28 -> D3 -> D3 -> Cyclic Reference in Formula | Returns the value at the Nth percentile. PERCENTILE(range,0.9) = 90th percentile. | Find the 90th percentile complaint count; identify top/bottom performance thresholds. | percentile, distribution, stats, rank |
| Aggregate | Stats | RANK | !ERROR! D29 -> D3 -> D3 -> Cyclic Reference in Formula | Returns the rank of a value within a range. 0=descending (largest=1), 1=ascending. | Rank programs by compliance score; create leaderboards. | rank, order, position, stats |
| Aggregate | Filtered | SUBTOTAL | !ERROR! D30 -> D3 -> D3 -> Cyclic Reference in Formula | Aggregates visible rows only — respects active filters. Function_num: 9=SUM, 2=COUNT, 1=AVERAGE, 4=MAX, 5=MIN. | Running totals on filtered lists — standard SUM counts hidden rows, SUBTOTAL does not. | subtotal, filter, visible, sum, count, aggregate |
| Aggregate | Filtered | AGGREGATE | !ERROR! D31 -> D3 -> D3 -> Cyclic Reference in Formula | Like SUBTOTAL but also ignores error cells. Args: function_num (9=SUM), options (5=ignore hidden+errors), range. | Sum or average while ignoring both hidden rows AND error cells simultaneously. | aggregate, filter, errors, sum, count, visible |
| Aggregate | Math | ROUND | #N/A | Rounds a number to N decimal places. 0=whole number, negative=tens/hundreds. | Round percentages to 1 decimal; round currency to 2 places for reporting consistency. | round, decimal, precision, math |
| Aggregate | Math | ROUNDUP | #N/A | Always rounds up, away from zero, to N decimal places. | Ceiling calculations; ensure minimum thresholds are met. | roundup, ceiling, math |
| Aggregate | Math | ROUNDDOWN | #N/A | Always rounds down, toward zero, to N decimal places. | Floor calculations; truncate values without rounding. | rounddown, floor, truncate, math |
| Aggregate | Math | ABS | #VALUE! | Returns the absolute value (removes negative sign). | Calculate variance without caring about direction; ensure positive results. | abs, absolute, positive, math |
| Aggregate | Math | MOD | #VALUE! | Returns the remainder after division. MOD(n,2)=0 means even. | Identify even/odd rows for alternating formatting; build cyclic row patterns. | mod, remainder, division, even, odd, math |
| Aggregate | Math | INT | #VALUE! | Rounds a number down to the nearest integer. | Truncate decimals; extract whole number portion from a decimal. | int, integer, truncate, whole, math |
| Aggregate | Math | POWER | 1024 | Raises a number to a power. Same as 2^10 in Excel notation. | Compound growth calculations; statistical computations. | power, exponent, math |
| Logical | Conditional | IF | #N/A | Returns one value if condition is TRUE, another if FALSE. Can be nested but gets unwieldy past 3 levels. | Classify compliance scores; create status labels; flag records meeting a threshold. | if, conditional, true, false, logical |
| Logical | Conditional | IFS | #N/A | Tests multiple conditions in order, returns first TRUE result. The TRUE at the end = else/default catch-all. | Multi-tier classification without deeply nested IFs. Office 2016+. | ifs, multiple, conditions, logical, tiers |
| Logical | Conditional | SWITCH | Other | Matches a value against a list of exact cases. Cleaner than nested IFs for exact match scenarios. | Convert state abbreviations to full names; map status codes to display labels; code-to-description mapping. | switch, match, map, convert, label, code |
| Logical | Conditional | IF (nested) | #N/A | IF formulas inside other IF formulas for branching logic. Gets hard to read past 3 levels — prefer IFS. | Grade bands; tier logic in older Excel versions that don’t have IFS. | if, nested, multiple, conditions, branches |
| Logical | Boolean | AND | Returns TRUE only if ALL conditions are true. Used standalone or inside IF. | Multi-condition quality flags: compliance high AND complaints low both required. | and, all, conditions, boolean, logical | |
| Logical | Boolean | OR | 1 | Returns TRUE if ANY condition is true. | Flag for review if compliance low OR escalations high — either is enough to trigger. | or, any, condition, boolean, logical |
| Logical | Boolean | NOT | 1 | Reverses a TRUE/FALSE value. | Flag records where a required field is NOT blank; invert any boolean check. | not, reverse, negate, boolean, logical |
| Logical | Boolean | XOR | Returns TRUE if exactly ONE condition is true (exclusive or) — not both and not neither. | Detect cases where exactly one of two conditions is met — mutually exclusive flags. | xor, exclusive, or, boolean | |
| Logical | Error Handling | IFERROR | Not Found | Returns a custom value if the formula produces any error. Catches all error types. | Suppress #N/A from lookups; show 0 instead of #DIV/0!; keep reports clean. | iferror, error, handle, suppress, n/a, div0 |
| Logical | Error Handling | IFNA | Not Found | Only catches #N/A errors — other errors still surface. More targeted than IFERROR. | Suppress only ‘not found’ lookup errors without hiding formula bugs. | ifna, error, n/a, not found, lookup |
| Logical | Error Handling | ISERROR | 1 | Returns TRUE if the formula produces any error. Used inside IF to build custom error logic. | Flag cells with errors; build conditional logic around potential error states. | iserror, error, check, detect, logical |
| Logical | Type Check | ISBLANK | Returns TRUE only if the cell is completely empty — not even a space. | Find missing required fields; conditional formatting to flag blanks; data completeness audits. | isblank, blank, empty, missing, check | |
| Logical | Type Check | ISNUMBER | Returns TRUE if the cell contains a numeric value. | Validate that charge fields contain numbers before aggregating; data type checks. | isnumber, number, numeric, check, type | |
| Logical | Type Check | ISTEXT | 1 | Returns TRUE if the cell contains text. | Detect text in numeric columns; find data entry errors in required number fields. | istext, text, check, type |
| Text | Extract | LEFT | Loo | Returns N characters from the start (left) of a text string. | Pull 2-char state code from ‘TX-HCBS-001’; extract year from a date-formatted string. | left, extract, start, beginning, characters |
| Text | Extract | RIGHT | okup | Returns N characters from the end (right) of a text string. | Extract last 4 digits of an ID; pull file extension from a filename. | right, extract, end, characters |
| Text | Extract | MID | up | Returns N characters starting at a specific position. Args: text, start_num, num_chars. | Extract program segment from structured ID ‘TX-HCBS-001’; pull middle section of a code. | mid, extract, middle, position, characters |
| Text | Find | FIND | #VALUE! | Returns position number of a substring within text. Case-sensitive. Returns error if not found. | Locate delimiter position to drive dynamic LEFT/MID extractions from structured text. | find, position, locate, character, case-sensitive |
| Text | Find | SEARCH | #VALUE! | Like FIND but case-insensitive. Returns error if substring not found — wrap in IFERROR. | Check if a keyword appears anywhere in a notes field regardless of capitalization. | search, find, locate, case-insensitive, keyword |
| Text | Join | CONCAT | Lookup – Basic Lookup | Joins text values together. Replaces the older CONCATENATE function. | Build ‘Last, First’ names; combine program code + state into a display label. | concat, concatenate, join, combine, text |
| Text | Join | TEXTJOIN | Lookup, Lookup, Lookup, Lookup, Lookup, Lookup, Lookup, Lookup, Lookup | Joins a range of values with a delimiter, optionally skipping blanks. Args: delimiter, ignore_blanks, text1, … | Combine a list of names or codes into one comma-separated string; build summary cells. | textjoin, join, delimiter, list, range, combine |
| Text | Join | & (operator) | Lookup Basic Lookup | Concatenation operator — joins text values directly without a function. | Quick joins: first + space + last; label + colon + value. | concatenate, join, operator, combine, text |
| Text | Clean | TRIM | Lookup | Removes leading, trailing, and extra internal spaces (reduces multiple spaces to one). | Fix imported data with invisible spaces causing VLOOKUP mismatches; normalize spacing. | trim, spaces, clean, leading, trailing |
| Text | Clean | CLEAN | Lookup | Removes non-printable characters such as line breaks, tabs, and null characters. | Clean up text imported from databases, PDFs, or legacy systems with hidden characters. | clean, non-printable, line break, characters, import |
| Text | Clean | SUBSTITUTE | Lookup | Replaces all occurrences of a specific substring with another string. Case-sensitive. | Strip dashes from IDs; remove prefixes; standardize inconsistent text formats. | substitute, replace, remove, text, clean |
| Text | Clean | REPLACE | NEWkup | Replaces text at a specific character position. Args: text, start_num, num_chars, new_text. | Swap a prefix or suffix by position when the text pattern is consistent by location. | replace, position, swap, text |
| Text | Format | TEXT | Lookup | Formats a number or date as a text string using a format code. Result is text, not a number. | Display dates consistently in labels; format numbers for concatenation in report strings. | text, format, date, number, display |
| Text | Format | UPPER | LOOKUP | Converts all text to uppercase. | Standardize state codes, IDs, or imported data before matching or lookup. | upper, uppercase, case, format |
| Text | Format | LOWER | lookup | Converts all text to lowercase. | Normalize text for comparison; clean up inconsistent casing in imported data. | lower, lowercase, case, format |
| Text | Format | PROPER | Lookup | Converts text to title case — first letter of each word capitalized. | Format names and addresses that were entered in all caps or all lowercase. | proper, title case, capitalize, format |
| Text | Measure | LEN | 6 | Returns the number of characters in a text string. | Validate field lengths; detect blank vs whitespace-only entries; check ID format lengths. | len, length, characters, count, measure |
| Text | Measure | EXACT | Compares two strings exactly, including case. Returns TRUE if identical. | Enforce uppercase-only fields in data validation; detect case mismatches. | exact, compare, case, match, text | |
| Text | Convert | VALUE | #VALUE! | Converts a text string that looks like a number into an actual numeric value. | Fix numbers stored as text (common in CSV imports) so they aggregate correctly. | value, convert, text, number, numeric |
| Text | Convert | NUMBERVALUE | #VALUE! | Converts text to number with control over decimal and thousands separators. | Handle number formats from international sources with different separator conventions. | numbervalue, convert, decimal, separator, international |
| Date | Current | TODAY | 46224 | Returns today’s date as a serial number. Updates automatically each day the workbook recalculates. | Dynamic aging calculations; highlight past-due items; date-relative conditional formatting. | today, current, date, dynamic |
| Date | Current | NOW | 46224.146863426 | Returns current date and time. Updates on recalculation. | Timestamp reports; log when a macro last ran; display current datetime in a cell. | now, current, datetime, timestamp |
| Date | Difference | DATEDIF | #VALUE! | Calculates the difference between two dates. Units: Y (complete years), M (complete months), D (days), YM, YD, MD. | Days since last audit; months enrolled in program; years of service; case age in days. | datedif, difference, years, months, days, age |
| Date | Difference | DAYS | #VALUE! | Returns the number of days between two dates. Simpler than DATEDIF for day counts only. | SLA tracking; days between intake and first service; simple day difference. | days, difference, count, duration |
| Date | Difference | NETWORKDAYS | #VALUE! | Counts working days (Mon–Fri) between two dates, excluding weekends. Optional holiday range arg. | Business days to resolve a complaint; SLA compliance; working day duration. | networkdays, business days, working, weekday, sla |
| Date | Difference | NETWORKDAYS.INTL | Like NETWORKDAYS but with customizable weekend definition (e.g., weekend=7 for Sunday only). | Organizations with non-standard work weeks; international teams with different weekend days. | networkdays, international, custom, weekend, business days | |
| Date | Build | DATE | #VALUE! | Constructs a date from separate year, month, day values. | Build the first day of each month; combine separate Y/M/D columns into a date. | date, build, construct, year, month, day |
| Date | Build | EOMONTH | #VALUE! | Returns the last day of the month, offset by N months. 0=current month, 1=next month, -1=prior month. | Set deadlines to end of month; build monthly reporting period end dates. | eomonth, end of month, last day, month |
| Date | Build | EDATE | #VALUE! | Returns a date exactly N months before or after a start date. | Calculate 6-month review dates; annual renewal dates; 90-day follow-up scheduling. | edate, months, add, subtract, date |
| Date | Extract | YEAR | #VALUE! | Extracts the 4-digit year from a date. | Group data by year; build fiscal year labels; year-over-year comparisons. | year, extract, date, group |
| Date | Extract | MONTH | #VALUE! | Extracts the month number (1–12) from a date. | Group data by month; build monthly summaries; month-based conditional formatting. | month, extract, date, group, number |
| Date | Extract | DAY | #VALUE! | Extracts the day of the month (1–31) from a date. | Build date labels; check if entry date is end of month; day-level analysis. | day, extract, date |
| Date | Extract | WEEKDAY | #VALUE! | Returns day of week as a number. Type 2 = Mon=1 through Sun=7. | Flag weekend entries; identify which day of week incidents cluster on. | weekday, day of week, extract, date |
| Date | Extract | WEEKNUM | #VALUE! | Returns the ISO week number of the year (1–53). | Group data by week for trend analysis; build weekly summary reports. | weeknum, week number, extract, date |
| Date | Extract | ISOWEEKNUM | #VALUE! | Returns the ISO 8601 week number. Monday is always day 1 of the week. | Standard week numbering for international reports; consistent week definitions. | isoweeknum, iso, week, international |
| Date | Extract | TEXT (date) | Lookup | Converts a date to a formatted text label using format codes. | Create month/year labels like ‘January 2024’ for chart axes and report headers. | text, format, date, label, month, year |
| Dynamic Arrays | Filter | FILTER | !ERROR! D89 -> D3 -> D3 -> Cyclic Reference in Formula | Returns rows meeting a condition. Spills automatically. Args: array, include, [if_empty]. Requires Office 365/2021+. | Extract all Critical programs into a summary section without helper columns or manual copying. | filter, dynamic, spill, criteria, extract, array |
| Dynamic Arrays | Filter | FILTER (AND) | !ERROR! D90 -> D3 -> D3 -> Cyclic Reference in Formula | Multiply boolean arrays inside FILTER for AND logic. Both conditions must be true. | Show only TX programs with escalations > 10 — multi-condition filtered extract. | filter, and, multiple, criteria, dynamic, array |
| Dynamic Arrays | Filter | FILTER (OR) | !ERROR! D91 -> D3 -> D3 -> Cyclic Reference in Formula | Add boolean arrays inside FILTER for OR logic. Either condition being true is sufficient. | Show TX or CA programs — multiple states or categories in one filtered result. | filter, or, multiple, criteria, dynamic, array |
| Dynamic Arrays | Sort | SORT | !ERROR! D92 -> D3 -> D3 -> Cyclic Reference in Formula | Sorts a range by a column. Args: array, [sort_index], [sort_order: 1=asc/-1=desc], [by_col]. | Dynamic leaderboard sorted by compliance score; auto-updating ranked table. | sort, dynamic, order, rank, spill |
| Dynamic Arrays | Sort | SORTBY | Aggregate | Sorts by one or more external columns not necessarily in the output array. | Sort names alphabetically using a separate column; multi-level sorts. | sortby, sort, external, multiple keys, dynamic |
| Dynamic Arrays | Unique | UNIQUE | VLOOKUP | Returns distinct values from a range. Set by_col=TRUE to deduplicate columns instead of rows. | Build dynamic dropdown source lists; get unique program codes or state list. | unique, distinct, deduplicate, list, dynamic |
| Dynamic Arrays | Unique | SORT+UNIQUE | # operator | Nest SORT around UNIQUE for a sorted distinct list in one formula. | Sorted unique department list — replaces Remove Duplicates + manual sort entirely. | sort, unique, sorted, distinct, list |
| Dynamic Arrays | Generate | SEQUENCE | 1 | Generates a number array. Args: rows, [cols], [start], [step]. | Auto-generate month numbers, row IDs, or numbered lists without dragging. | sequence, generate, numbers, series, list |
| Dynamic Arrays | Generate | SEQUENCE (date series) | 45292 | Combine SEQUENCE with EDATE/DATE to generate a series of dates. | Build a calendar header for 12 months; generate monthly date labels automatically. | sequence, date, series, months, generate |
| Dynamic Arrays | Combine | FILTER+SORT | !ERROR! D98 -> D3 -> D3 -> Cyclic Reference in Formula | Nest FILTER inside SORT to filter first, then sort the result. | Filtered + sorted results in one formula — no helper ranges needed. | filter, sort, combine, nest, dynamic |
| Dynamic Arrays | Combine | FILTER+COUNTIF | 1 | Use UNIQUE to generate distinct list, then COUNTIF to count each occurrence. | Count records per unique category without a PivotTable. | countif, unique, count, frequency, distribution |
| Dynamic Arrays | Spill Reference | # operator | 0 | D2# references the entire spill range starting at D2 — expands automatically as source data changes. | COUNTIF against a spilled UNIQUE list; both update together when source data grows. | spill, reference, hash, dynamic, range, array |
| Dynamic Arrays | Lookup | XLOOKUP (multi-col) | N/A | Return multiple columns at once by specifying a multi-column return array. | Fetch name, department, and title in a single formula — no need for three separate XLOOKUPs. | xlookup, multiple columns, return, lookup, array |
| Dynamic Arrays | Lookup | XLOOKUP (reverse) | N/A | Search mode -1 searches from the bottom up, returning the most recent match. | Find the most recent audit, most recent visit, or latest record for a given ID. | xlookup, reverse, last, recent, bottom, search |
| Financial | Loan | PMT | #VALUE! | Calculates fixed periodic payment. Args: rate (per period), nper (total periods), pv (loan amount as negative). | Monthly mortgage payment; car loan payment; any fixed-installment loan calculation. | pmt, payment, loan, mortgage, annuity |
| Financial | Savings | FV | #VALUE! | Future value of regular contributions plus an initial balance. Args: rate, nper, pmt, [pv]. | What will a retirement account be worth; college fund projections. | fv, future value, savings, investment, compound |
| Financial | Investment | NPV | #VALUE! | Net present value of future cash flows. Year 0 (investment) must be ADDED OUTSIDE the function — NPV starts at period 1. | Capital investment decisions; project ROI analysis; is this project worth the cost? | npv, net present value, investment, discount, roi |
| Financial | Investment | IRR | 6.5397108193161E+51 | Internal rate of return — the discount rate that makes NPV = 0. Year 0 (negative) must be INSIDE the range. | Compare project returns to a hurdle rate; if IRR > hurdle rate, project passes. | irr, internal rate of return, return, investment, hurdle |
| Financial | Depreciation | SLN | #VALUE! | Straight-line depreciation per period. Args: cost, salvage value, useful life. | Annual depreciation on equipment; asset scheduling. | sln, depreciation, straight line, asset |
| Statistical | Regression | FORECAST | !ERROR! Argument 2 passed to TablePress\PhpOffice\PhpSpreadsheet\Calculation\Statistical\Trends::FORECAST() must be of the type array, string given | Returns a predicted value based on linear regression from known data points. | Project next month’s complaint volume based on historical trend. | forecast, predict, trend, regression, linear |
| Statistical | Regression | FORECAST.ETS | Exponential smoothing forecast for time-series data. Handles seasonality automatically. | Forecast compliance scores or complaint volume with seasonal patterns. | forecast, ets, exponential, time series, seasonal | |
| Statistical | Correlation | CORREL | !ERROR! D110 -> D3 -> D3 -> Cyclic Reference in Formula | Returns the correlation coefficient (-1 to 1) between two data series. | Measure relationship between complaints and escalations; identify correlated metrics. | correl, correlation, relationship, statistics |
| Statistical | Frequency | FREQUENCY | !ERROR! D111 -> D3 -> D3 -> Cyclic Reference in Formula | Returns a frequency distribution as an array. Must be entered as array formula. | Build score distribution histogram; count how many records fall in each range bucket. | frequency, distribution, histogram, buckets, stats |
| Information | Cell Info | CELL | Returns information about a cell: address, format, row, col, filename, type, width. | Get current cell address; check cell format type programmatically. | cell, info, address, format, type | |
| Information | Workbook | INFO | / | Returns information about the current operating environment: directory, numfile, origin, etc. | Get file path; check Excel version in a formula. | info, workbook, directory, environment |
| Information | Error Check | ERROR.TYPE | #N/A | Returns a number indicating which type of error a cell contains (1=#NULL!, 2=#DIV/0!, etc.). | Identify specific error types to handle them differently in logic. | error type, error, check, identify |
| Database | DB Functions | DSUM | !ERROR! D115 -> D3 -> D3 -> Cyclic Reference in Formula | Database SUM with criteria in a separate criteria range table. Args: database, field, criteria. | Sum with complex criteria that are easier to manage in a table format than SUMIFS. | dsum, database, sum, criteria, table |
| Database | DB Functions | DCOUNT | !ERROR! D116 -> D3 -> D3 -> Cyclic Reference in Formula | Database COUNT with criteria range. | Count records matching complex criteria stored in a separate table. | dcount, database, count, criteria |
| Database | DB Functions | DAVERAGE | !ERROR! D117 -> D3 -> D3 -> Cyclic Reference in Formula | Database AVERAGE with criteria range. | Average records matching complex criteria stored in a separate table. | daverage, database, average, criteria |