Excel formula guides with syntax and examples.
XLOOKUP, FILTER, IF, SUMIFS, dates, percentages, and text. Examples you can adapt to your workbook.
Free is 3 generations per day. Sign up to keep chats on any device.
XLOOKUP
How to Use XLOOKUP in Excel: Syntax and Examples
XLOOKUP searches one row or column and returns the value from the same position in another row or column. It is a clearer, more flexible replacement for many VLOOKUP and INDEX/MATCH formulas.
Read guide- VLOOKUPHow to Use VLOOKUP in Excel: Exact Match ExamplesVLOOKUP searches the first column of a table and returns a value from another column in the same row. It remains useful in older workbooks, but exact matching should be specified explicitly for most business data.
- IFExcel IF Function: Conditions and ExamplesThe IF function tests a condition and returns one value when it is true and another when it is false. It is the foundation for conditional spreadsheet logic.
- SUMIFExcel SUMIF: Add Values with One ConditionSUMIF adds values that meet one condition. The condition can target text, a number, a date, a comparison, or a wildcard pattern.
- SUMIFSExcel SUMIFS: Sum with Multiple CriteriaSUMIFS adds values only when every supplied condition is met. It is ideal for totals constrained by combinations such as region, status, owner, or date range.
- COUNTIFExcel COUNTIF: Count Cells That Match a ConditionCOUNTIF counts cells in one range that match one condition. Use it to count statuses, values above a threshold, duplicates, blanks, or text patterns.
- INDEX MATCHINDEX MATCH in Excel: Flexible Lookup ExamplesINDEX returns a value at a position, while MATCH finds that position. Together they create a durable lookup that can search in any direction and works in Excel versions without XLOOKUP.
- Date differenceCalculate the Difference Between Dates in ExcelExcel stores dates as serial numbers, so simple subtraction returns elapsed days. DATEDIF calculates completed units, while NETWORKDAYS counts working days.
- Percentage formulasExcel Percentage Formulas: Change, Total and MarkupExcel percentages are decimal values displayed with percentage formatting. The correct formula depends on whether you need a share of a total, a rate of change, or an amount increased or reduced by a rate.
- AVERAGEExcel AVERAGE: Mean and Weighted Average ExamplesAVERAGE calculates the arithmetic mean of numeric values. It ignores empty cells and text, but includes zeros; conditional and weighted averages need different formulas.
- IFERRORExcel IFERROR: Replace Formula Errors SafelyIFERROR returns a fallback when an expression produces any Excel error. It is useful at expected failure points, but broad error handling should not replace fixing incorrect source data or formulas.
- CONCAT and TEXTJOINCombine Text in Excel with &, CONCAT, and TEXTJOINExcel can combine text with the & operator, CONCAT, or TEXTJOIN. TEXTJOIN is best for ranges because it adds a delimiter and can ignore blank cells.
- FILTERHow to Use FILTER in Excel: Spill ExamplesFILTER returns every row or column that meets a condition and spills the result into neighboring cells. It replaces many helper columns used for extracting matching records.
- IFSExcel IFS: Multiple Conditions Without NestingIFS evaluates condition and value pairs in order and returns the first match. It is easier to read than nested IF when you have several mutually exclusive outcomes.
- TEXTExcel TEXT Function: Dates and Number FormatsTEXT converts a number or date into text using a format code. It is the reliable way to keep readable dates and padded IDs when you combine values with & or TEXTJOIN.
- LEFT, RIGHT, MIDExcel LEFT, RIGHT, and MID Text ExtractionLEFT, RIGHT, and MID return part of a text string. Combine them with FIND or LEN when the piece you need is not a fixed number of characters.
- XMATCHHow to Use XMATCH in Excel: Lookup PositionXMATCH returns the relative position of a lookup value in a row or column. Pair it with INDEX when you need a flexible two-way lookup without VLOOKUP column numbers.
- UNIQUEExcel UNIQUE: Distinct Values and Spill ListsUNIQUE spills the distinct values from a range. It is the fastest way to build a clean list for data validation, summaries, or FILTER criteria.
- SORTExcel SORT and SORTBY: Rank and Reorder DataSORT reorders a range by one or more columns and spills the result. SORTBY can sort one range using the order of another range, which is useful for ranking.
- COUNTIFSExcel COUNTIFS: Count Rows With Several RulesCOUNTIFS counts rows where every criterion is true. Each criteria range must be the same size. It is the counting counterpart to SUMIFS.
- ROUNDExcel ROUND, ROUNDUP, and ROUNDDOWN ExamplesROUND changes a number to a chosen number of digits. Use it before presenting currency, packing quantities, or comparing values that would otherwise differ by floating-point noise.
- TODAY and NOWExcel TODAY and NOW: Current Date FormulasTODAY returns the current date. NOW returns the current date and time. Both are volatile: they recalculate when the workbook calculates, which is useful for aging and risky for historical snapshots.
- SWITCHExcel SWITCH: Cleaner Alternatives to Nested IFSWITCH compares one expression to a list of values and returns the matching result. It is cleaner than nested IF when you are matching exact codes rather than ranges.
- SUBSTITUTEExcel SUBSTITUTE: Replace Text in CellsSUBSTITUTE replaces specific text with other text. Unlike REPLACE, it finds the old string rather than a character position, which is better for cleaning imported data.
- SUMExcel SUM Function: Add a Range FastSUM adds every number in a range. Text and blanks are ignored, which makes it safer than a long A1+A2+A3 chain.
- AND / ORExcel AND and OR Inside IF FormulasAND requires every test to be true. OR requires at least one. Use them inside IF, or multiply and add Boolean arrays.
- IFNAExcel IFNA: Catch Lookup Misses OnlyIFNA handles only #N/A. Prefer it over IFERROR on lookups so other errors stay visible.
- TEXTJOINExcel TEXTJOIN: Combine Cells With a DelimiterTEXTJOIN concatenates a range with a delimiter and can ignore empty cells, which CONCATENATE cannot do cleanly.
- MID / RIGHTExcel MID and RIGHT Text ExtractionMID takes a slice from the middle. RIGHT takes a slice from the end. Pair them with FIND when the position changes.
- VALUEExcel VALUE: Text That Looks Like a NumberVALUE turns a numeric string into a real number. Use it after CSV imports that store amounts as text.
- DATEExcel DATE Function: Build a Safe DateDATE(year, month, day) creates a real date from parts. It is safer than typing 1/2/2026 in a mixed locale file.
- EOMONTHExcel EOMONTH: Last Day of the MonthEOMONTH returns the last calendar day of a month offset from a start date. Offset 0 is this month. Offset -1 is last month.
- NETWORKDAYSExcel NETWORKDAYS: Count Business DaysNETWORKDAYS counts inclusive weekdays between two dates and can skip a holiday range.
- INDEXExcel INDEX: Return a Cell by PositionINDEX returns the value at a position inside a range. Combined with MATCH it can look left, right, or two-way.
- MATCHExcel MATCH: Find a Position in a ListMATCH returns a position, not a value. Feed that position to INDEX when you want the matching cell.
- LETExcel LET: Name the Parts of a FormulaLET assigns names inside a formula. Use it when the same expression appears twice or the formula is hard to read.
- LAMBDAExcel LAMBDA: Reusable Custom FunctionsLAMBDA turns an expression into a function you can name and reuse. It is how SCAN, MAP, and REDUCE take custom logic.
- VSTACK / HSTACKExcel VSTACK and HSTACK: Append RangesVSTACK stacks ranges vertically. HSTACK places them side by side. Missing columns become #N/A unless you wrap IFNA.
- CHOOSECOLSExcel CHOOSECOLS: Pick Columns From an ArrayCHOOSECOLS returns only the column indexes you list, in that order. Use it to slim a FILTER result.
- GROUPBY / PIVOTBYExcel GROUPBY and PIVOTBY for TotalsGROUPBY is a formula pivot with one row field. PIVOTBY adds a column field. Both spill and update when the source changes.
- SUMPRODUCTExcel SUMPRODUCT: Conditional TotalsSUMPRODUCT multiplies arrays element by element and adds the result. Boolean tests coerce to 1 and 0.
Need a formula for this worksheet?
Describe your columns, criteria, and result. Copy a formula written for Excel.