Microsoft Excel is a robust spreadsheet software with a wide range of functions , Microsoft Excel is a robust spreadsheet software with a wide range of functions

**Math and Trigonometry:**- SUM, SUMIF, SUMIFS
- AVERAGE, AVERAGEIF, AVERAGEIFS
- COUNT, COUNTIF, COUNTIFS
- MIN, MAX
- ROUND, ROUNDUP, ROUNDDOWN
- SQRT
- SIN, COS, TAN

**Text Functions:**- CONCATENATE, CONCAT
- LEFT, RIGHT, MID
- LEN
- UPPER, LOWER, PROPER
- TRIM
- SUBSTITUTE
- FIND, SEARCH

**Logical Functions:**- IF, IFERROR, IFNA
- AND, OR, NOT
- XOR
- IF, nested IF

**Date and Time Functions:**- NOW, TODAY
- DATE, TIME
- DATEDIF
- EDATE, EOMONTH
- NETWORKDAYS

**Lookup and Reference:**- VLOOKUP, HLOOKUP, LOOKUP
- INDEX, MATCH
- ADDRESS, ROW, COLUMN
- OFFSET

**Statistical Functions:**- AVERAGE, MEDIAN, MODE
- STDEV, STDEVP
- VAR, VARP
- CORREL, COVAR

**Financial Functions:**- PMT, PV, FV
- RATE, NPER, NPV
- IRR, MIRR

**Database Functions:**- DSUM, DCOUNT, DAVERAGE
- DMAX, DMIN

**Engineering Functions:**- BESSELI, BESSELJ, BESSELK, BESSELY
- BIN2DEC, DEC2BIN

**Information Functions:**- ISNUMBER, ISTEXT, ISBLANK
- CELL, INFO

**Array Formulas:**- SUMPRODUCT
- TRANSPOSE
- MMULT

**Conditional Formatting:**- Color scales, data bars, icon sets

**Mathematical Functions:**- ABS, INT, MOD
- EXP, LN, LOG
- RAND, RANDBETWEEN

**Text Functions:**- TEXT
- CHAR, CODE
- CLEAN, UNICHAR, UNICODE
- LEFTB, RIGHTB, MIDB (for double-byte character sets)

**Logical Functions:**- CHOOSE
- IFS
- SWITCH

**Date and Time Functions:**- HOUR, MINUTE, SECOND
- DATEVALUE, TIMEVALUE
- WEEKDAY, WEEKNUM
- WORKDAY, WORKDAY.INTL

**Lookup and Reference:**- MATCH (with wildcard)
- ADDRESS (with row and column numbers)
- INDIRECT
- CELL (with various information)

**Statistical Functions:**- PERCENTILE, QUARTILE
- LARGE, SMALL
- Z.TEST, T.TEST
- COUNTBLANK

**Financial Functions:**- AMORDEGRC, AMORLINC
- ACCRINT, ACCRINTM
- CUMIPMT, CUMPRINC

**Database Functions:**- DGET, DMAX, DMIN
- DCOUNTA, DPRODUCT

**Engineering Functions:**- CONVERT
- GCD, LCM

**Information Functions:**- CELL, INFO
- FORMULATEXT, N, T

**Array Formulas:**- ARRAYFORMULA
- FREQUENCY

**Statistical Analysis ToolPak Functions:**- Analysis ToolPak is an Excel add-in that provides additional statistical functions such as ANOVA, Histogram, Regression, etc.

**Power Query Functions:**- Power Query functions can be used for data transformation and manipulation.

**Dynamic Arrays:**- Excel 365 introduces dynamic arrays, allowing formulas to spill results into adjacent cells.

**Statistical Analysis ToolPak Functions (continued):**- The Analysis ToolPak add-in also includes functions like F-Test, t-Test, Regression, Moving Average, Exponential Smoothing, and more.

**Array Formulas (continued):**- FREQUENCY
- TRANSPOSE
- MINVERSE
- MUNIT

**Database Functions (continued):**- DATEDIF
- DAVERAGE
- DGET
- DPRODUCT

**Information Functions (continued):**- ERROR.TYPE
- ISERR, ISERROR
- ISFORMULA

**Logical Functions (continued):**- IFERROR
- IFNA
- XOR

**Text Functions (continued):**- REPT
- CLEAN
- CODE

**Date and Time Functions (continued):**- EDATE
- NETWORKDAYS
- WORKDAY, WORKDAY.INTL

**Lookup and Reference (continued):**- INDEX, MATCH
- HLOOKUP, VLOOKUP
- OFFSET

**Engineering Functions (continued):**- BIN2DEC, BIN2HEX, BIN2OCT
- DEC2BIN, DEC2HEX, DEC2OCT
- HEX2BIN, HEX2DEC, HEX2OCT
- OCT2BIN, OCT2DEC, OCT2HEX

**Financial Functions (continued):**- DB, DDB
- SLN, SYD
- FVSCHEDULE

**Mathematical Functions (continued):**- PI
- RANDARRAY
- SIGN

**Error Checking Functions:**- IFERROR
- IFNA
- ISERR, ISERROR

**Data Validation:**- Data validation tools to control the type and format of data entered into cells.

**PivotTables and PivotCharts:**- PivotTables and PivotCharts for data analysis and visualization.

**What-If Analysis:**- Goal Seek, Scenario Manager, and Data Tables for analyzing different scenarios.

**Solver:**- Solver add-in for solving optimization problems.

**Sparklines:**- Miniature charts within a cell for visualizing trends.

**Conditional Formatting (continued):**- Rules for highlighting cells based on specific conditions.

**Power Query:**- Power Query for data shaping, cleaning, and transformation.

**Power Pivot:**- Power Pivot for data modeling and creating relationships between tables.

**Power BI Integration:**- Integration with Power BI for advanced data visualization and business intelligence.

**Excel Tables:**- Tables for managing and analyzing related data in a structured format.

**Structured References:**- Structured references in tables for easy referencing of data.

**Excel Templates:**- Pre-built templates for various purposes like budgets, calendars, and invoices.

**Dynamic Arrays (continued):**- UNIQUE
- SORT
- FILTER
- SEQUENCE

**3D Formulas:**- Consolidate data from multiple worksheets using 3D formulas.

**Hyperlinks:**- Creating hyperlinks to navigate within the workbook or to external resources.

**Named Ranges:**- Naming ranges of cells for easier referencing in formulas.

**Scenario Manager:**- Scenario Manager for creating and managing different scenarios in a worksheet.

**Data Validation (continued):**- List validation, allowing users to choose from a predefined list.

**Array Formulas (continued):**- CSE (Ctrl+Shift+Enter) to create array formulas.

**Data Consolidation:**- Consolidating data from multiple ranges into a single range.

**Grouping and Outlining:**- Grouping rows or columns for better organization and collapsing/expanding sections.

**Data Forms:**- Creating data entry forms for easy data input.

**Custom Views:**- Saving and managing different custom views of a worksheet.

**Solver (continued):**- Solver for optimization and goal-seeking problems.

**Data Analysis ToolPak (continued):**- Descriptive Statistics, Histogram, Moving Average, Regression, and more.

**Excel Web App:**- Collaboration and editing Excel files online through the web app.

**Macro Recorder:**- Recording and running macros for automating repetitive tasks.

**VBA (Visual Basic for Applications):**- Advanced users can use VBA to create custom macros and automate complex tasks.

excelCopy code

`=SUM(A1:A10)`

This formula adds up the values in cells A1 through A10.

**Average:**- Calculates the average of a range of numbers.

`=AVERAGE(A1:A10)`

**Count:**- Counts the number of cells that contain numbers in a given range.

`=COUNT(A1:A10)`

**Concatenate:**- Combines multiple text strings into one.

`=CONCATENATE(A1, " ", B1)`

**IF Statement:**- Performs a logical test and returns one value if the test is true and another if false.

`=IF(A1>10, "Greater than 10", "Less than or equal to 10")`

**VLOOKUP:**- Searches for a value in the first column of a table and returns a value in the same row from another column.

`=VLOOKUP(A1, B1:C10, 2, FALSE)`

**HLOOKUP:**- Similar to VLOOKUP, but searches for the value in the first row of a table.

`=HLOOKUP(A1, B1:D10, 3, FALSE)`

**INDEX and MATCH:**- A powerful combination to look up a value in a table using a two-dimensional lookup.

`=INDEX(C1:E10, MATCH(A1, B1:B10, 0), 3)`

**SUMIF:**- Adds up numbers based on a specified condition.

`=SUMIF(A1:A10, ">5")`

**TEXT:**- Converts a value to text in a specific format.

`=TEXT(A1, "yyyy-mm-dd")`

**LEN:**- Returns the number of characters in a text string.

`=LEN(A1)`

**SUMPRODUCT:**- Multiplies corresponding components in arrays and returns the sum of those products.

`=SUMPRODUCT(A1:A10, B1:B10)`

**INDEX and MATCH (Multi-Criteria):**- Using INDEX and MATCH with multiple criteria for more complex lookups.

`=INDEX(C1:C10, MATCH(1, (A1:A10="X")*(B1:B10="Y"), 0))`

- This is an array formula, so remember to enter it with Ctrl+Shift+Enter.

**IFERROR:**- Returns a specified value if a formula results in an error; otherwise, it returns the result of the formula.

`=IFERROR(VLOOKUP(A1, B1:C10, 2, FALSE), "Not Found")`

**MATCH with Wildcard:**- Using MATCH with a wildcard for partial matches.

`=MATCH("abc*", A1:A10, 0)`

**SUMIFS:**- Adds up numbers based on multiple criteria.

`=SUMIFS(C1:C10, A1:A10, "X", B1:B10, "Y")`

**DATE Functions:**- Various date-related functions like YEAR, MONTH, DAY, etc.

`=YEAR(A1)`

**TRIM:**- Removes extra spaces from text.

`=TRIM(A1)`

**ROUND:**- Rounds a number to a specified number of digits.

`=ROUND(A1, 2)`

**HYPERLINK:**- Creates a clickable hyperlink.

`=HYPERLINK("http://www.example.com", "Visit Example")`

**RAND and RANDBETWEEN:**- Generates random numbers.

`=RAND() =RANDBETWEEN(1, 100)`

**CHOOSE:**- Selects a value from a list of values based on a given position.

`=CHOOSE(A1, "Option 1", "Option 2", "Option 3")`

**OFFSET:**- Returns a reference offset from a starting cell or range of cells.

`=OFFSET(A1, 2, 3, 1, 1)`

**SUBTOTAL:**- Returns a subtotal in a list or database.

`=SUBTOTAL(109, A1:A10)`

- The second argument specifies the function (e.g., 109 for SUM).

**SUMPRODUCT with Multiple Arrays:**- Using SUMPRODUCT with multiple arrays for more complex calculations.

`=SUMPRODUCT(A1:A10, B1:B10, C1:C10)`

**INDIRECT:**- Returns the reference specified by a text string.

`=INDIRECT("Sheet1!A1")`

**DATEDIF:**- Calculates the difference between two dates in years, months, or days.

`=DATEDIF(A1, B1, "d")`

**CEILING and FLOOR:**- Rounds numbers away from or towards zero.

`=CEILING(A1, 5) =FLOOR(A1, 10)`

**CONCAT:**- Concatenates a range of cells.

`=CONCAT(A1:A10)`

**IFS:**- Returns a value based on multiple conditions.

`=IFS(A1>10, "High", A1>5, "Medium", A1<=5, "Low")`

**XLOOKUP:**- A modern alternative to VLOOKUP and HLOOKUP with enhanced features.

`=XLOOKUP(A1, B1:B10, C1:C10)`

**SEQUENCE:**- Generates a sequence of numbers.

`=SEQUENCE(10, 1, 1, 2)`

**IF with AND and OR:**- Using nested IF, AND, and OR functions for more complex logical tests.

`=IF(AND(A1>5, OR(B1="X", B1="Y")), "Condition Met", "Not Met")`

**TEXTJOIN:**- Joins text from multiple ranges with a specified delimiter.

`=TEXTJOIN(", ", TRUE, A1:A10)`

**SWITCH:**- Evaluates an expression against a list of values and returns the corresponding result.

`=SWITCH(A1, 1, "One", 2, "Two", 3, "Three", "Other")`

**LOOKUP:**

- Searches for a value in a range and returns a corresponding value in the same position in another range.

excelCopy code

`=LOOKUP(A1, B1:B10, C1:C10)`

**Nesting Functions:**

- Combining multiple functions within a single formula.

excelCopy code

`=IF(SUM(A1:A10)>100, "Above Threshold", "Below Threshold")`

**LOGICAL Functions (CONTINUED):**

- OR, XOR, NOT for more complex logical tests.

excelCopy code

`=IF(OR(A1>10, B1="X"), "Condition Met", "Not Met")`

**DATE Functions (CONTINUED):**

- EOMONTH (returns the last day of the month).

excelCopy code

`=EOMONTH(A1, 0)`

**RANK:**

- Returns the rank of a number in a list of numbers.

excelCopy code

`=RANK(A1, A1:A10)`

**IF and ISBLANK:**

- Using ISBLANK function with IF for conditional checks.

excelCopy code

`=IF(ISBLANK(A1), "Empty", "Not Empty")`

**Array Formulas (CONTINUED):**

- Using Ctrl+Shift+Enter to create array formulas for advanced calculations.

excelCopy code

`=SUM(IF(A1:A10>5, B1:B10, 0))`

**INDEX and MATCH (CONTINUED):**

- Using INDEX and MATCH for two-way lookups.

excelCopy code

`=INDEX(A1:C10, MATCH("X", B1:B10, 0), MATCH("Y", A1:A10, 0))`

**ROUNDUP and ROUNDDOWN:**

- Rounds a number up or down to the nearest multiple.

excelCopy code

`=ROUNDUP(A1, 0) =ROUNDDOWN(A1, 0)`

**AVERAGEIF and AVERAGEIFS (CONTINUED):**

- Using AVERAGEIF and AVERAGEIFS with multiple criteria.

excelCopy code

`=AVERAGEIFS(C1:C10, A1:A10, "X", B1:B10, "Y")`

**INDEX and MATCH (Multi-Sheet):**

- Using INDEX and MATCH across multiple sheets.

excelCopy code

`=INDEX('Sheet2'!C1:C10, MATCH(A1, 'Sheet2'!B1:B10, 0))`

**TRANSPOSE:**

- Transposes a range of cells, switching rows to columns and vice versa.

excelCopy code

`=TRANSPOSE(A1:B5)`

**IF with ISNUMBER:**

- Combining IF with ISNUMBER to check if a cell contains a numeric value.

excelCopy code

`=IF(ISNUMBER(A1), "Numeric", "Not Numeric")`

**TEXT Functions (CONTINUED):**

- CONCAT, TEXTJOIN for concatenating text.

excelCopy code

`=CONCAT(A1:A10) =TEXTJOIN(", ", TRUE, A1:A10)`

**OFFSET and COUNTA:**

- Using OFFSET and COUNTA to create dynamic ranges.

excelCopy code

`=OFFSET($A$1, 0, 0, COUNTA($A:$A), 1)`

**IF and ISTEXT:**

- Using ISTEXT with IF to check if a cell contains text.

excelCopy code

`=IF(ISTEXT(A1), "Text", "Not Text")`

**MATCH with INDEX (Two-way Lookup):**

- Using MATCH with INDEX for a two-way lookup.

excelCopy code

`=INDEX(C1:E10, MATCH(A1, B1:B10, 0), MATCH(B1, C1:E1, 0))`

**INDEX and SMALL:**

- Using INDEX and SMALL for retrieving the nth smallest value.

excelCopy code

`=INDEX(A1:A10, SMALL(IF(A1:A10<>0, MATCH(A1:A10, A1:A10, 0)), 2))`

- This is an array formula, so remember to enter it with Ctrl+Shift+Enter.

**COUNTIF and Wildcard:**

- Using COUNTIF with a wildcard for partial matches.

excelCopy code

`=COUNTIF(A1:A10, "abc*")`

**IF with SEARCH:**

- Using SEARCH within IF for partial text matching.

excelCopy code

`=IF(ISNUMBER(SEARCH("apple", A1)), "Contains Apple", "No Apple")`

**INDEX and MATCH with Multiple Criteria:**

- Using INDEX and MATCH with multiple criteria for more complex lookups.

excelCopy code

`=INDEX(C1:C10, MATCH(1, (A1:A10="X")*(B1:B10="Y"), 0))`

- This is an array formula, so remember to enter it with Ctrl+Shift+Enter.

**SUMPRODUCT with Criteria:**

- Using SUMPRODUCT with criteria for summing based on multiple conditions.

excelCopy code

`=SUMPRODUCT((A1:A10="X")*(B1:B10="Y")*(C1:C10))`

**IF with ISTEXT and ISNUMBER:**

- Combining ISTEXT and ISNUMBER within IF for versatile text and number checks.

excelCopy code

`=IF(ISTEXT(A1), "Text", IF(ISNUMBER(A1), "Number", "Other"))`

**INDEX and MATCH (Two Criteria):**

- Using INDEX and MATCH with two criteria for a lookup.

excelCopy code

`=INDEX(D1:D10, MATCH(1, (A1:A10="X")*(B1:B10="Y"), 0))`

- This is an array formula, so remember to enter it with Ctrl+Shift+Enter.

**SUMIFS with Date Range:**

- Using SUMIFS to sum values based on date range criteria.

excelCopy code

`=SUMIFS(C1:C10, A1:A10, ">01/01/2022", A1:A10, "<01/31/2022")`

**IF and SEARCH with Case-Insensitive:**

- Using IF and SEARCH for case-insensitive text matching.

excelCopy code

`=IF(ISNUMBER(SEARCH("apple", A1, 1)), "Contains Apple", "No Apple")`

**INDEX and MATCH (Lookup from Right to Left):**

- Using INDEX and MATCH to perform a lookup from right to left.

excelCopy code

`=INDEX(A1:A10, MATCH("Y", B1:B10, 0))`

**AVERAGEIF with Multiple Criteria:**

- Using AVERAGEIF with multiple criteria.

excelCopy code

`=AVERAGEIF(A1:A10, "X", B1:B10, "Y")`

**CHOOSE with INDEX:**

- Combining CHOOSE and INDEX for a dynamic selection.

excelCopy code

`=INDEX(CHOOSE(A1, B1:B10, C1:C10, D1:D10), MATCH("Y", C1:C10, 0))`

**INDEX and MATCH (Lookup with Error Handling):**

- Using INDEX and MATCH with IFERROR for error handling.

excelCopy code

`=IFERROR(INDEX(A1:A10, MATCH("X", B1:B10, 0)), "Not Found")`

**RANDARRAY for Random Data:**

- Generating an array of random numbers with RANDARRAY.

excelCopy code

`=RANDARRAY(5, 3)`

**FILTER for Dynamic Data Selection:**

- Using FILTER to dynamically select data based on criteria.

excelCopy code

`=FILTER(A1:C10, (B1:B10="X")*(C1:C10>5))`

**TEXT Functions (CONTINUED):**

- MID, LEFT, RIGHT for extracting substrings from text.

excelCopy code

`=MID(A1, 2, 3) =LEFT(A1, 5) =RIGHT(A1, 4)`

**IFS with OR and AND:**

- Combining IFS with OR and AND for multiple conditions.

excelCopy code

`=IFS(AND(A1>5, B1="X"), "Condition 1", OR(A1<=5, B1="Y"), "Condition 2", TRUE, "Other")`

**INDEX and MATCH with OFFSET:**

- Using INDEX, MATCH, and OFFSET for more flexible lookups.

excelCopy code

`=INDEX(OFFSET(A1:A10, 0, 1), MATCH("X", B1:B10, 0))`

**ROW and COLUMN:**

- Returns the row or column number of a reference.

excelCopy code

`=ROW(A1) =COLUMN(A1)`

**CHOOSE with HYPERLINK:**

- Using CHOOSE with HYPERLINK for dynamic linking.

excelCopy code

`=HYPERLINK(CHOOSE(A1, "http://link1.com", "http://link2.com", "http://link3.com"), "Click me")`

**INDEX and MATCH (Two Criteria, Multi-Column):**

- Using INDEX and MATCH with two criteria across multiple columns.

excelCopy code

`=INDEX(D1:F10, MATCH(1, (A1:A10="X")*(B1:B10="Y"), 0), 3)`

- This is an array formula, so remember to enter it with Ctrl+Shift+Enter.

**CONCAT with IF and TEXT:**

- Concatenating values with conditions using CONCAT, IF, and TEXT.

excelCopy code

`=CONCAT(IF(A1:A10>5, TEXT(A1:A10, "0"), ""))`

- This is an array formula, so remember to enter it with Ctrl+Shift+Enter.

**INDEX and MATCH with Dynamic Range:**

- Using INDEX and MATCH with dynamic named ranges.

excelCopy code

`=INDEX(MyDynamicRange, MATCH("X", MyDynamicRangeCriteria, 0))`

**TEXT and MID for Extracting Date Components:**

- Extracting day, month, and year from a date using TEXT and MID.

excelCopy code

`=TEXT(A1, "dd") =TEXT(A1, "mm") =TEXT(A1, "yyyy")`

**SUMPRODUCT with INDEX and MATCH:**

- Using SUMPRODUCT with INDEX and MATCH for advanced calculations.

excelCopy code

`=SUMPRODUCT(INDEX(B1:B10, MATCH(A1:A10, C1:C10, 0)))`

**INDEX and MATCH (Case-Sensitive Lookup):**

- Performing a case-sensitive lookup using INDEX and MATCH.

excelCopy code

`=INDEX(B1:B10, MATCH(TRUE, EXACT(A1, C1:C10), 0))`

- This is an array formula, so remember to enter it with Ctrl+Shift+Enter.

**FREQUENCY:**

- Calculates how often values occur within a range.

excelCopy code

`=FREQUENCY(A1:A10, {0, 5, 10, 15})`

- This is an array formula, so remember to enter it with Ctrl+Shift+Enter.

**SUMIFS with OR Logic:**

- Using SUMIFS with OR logic for multiple criteria.

excelCopy code

`=SUMIFS(C1:C10, A1:A10, "X", B1:B10, {"Y", "Z"})`

**INDEX and MATCH with Dynamic Sheet Reference:**

- Using INDEX and MATCH with a dynamic sheet reference.

excelCopy code

`=INDEX(INDIRECT("'" & A1 & "'!B:B"), MATCH("X", INDIRECT("'" & A1 & "'!A:A"), 0))`

**CHOOSE with Named Ranges:**

- Utilizing CHOOSE with named ranges for dynamic selection.

excelCopy code

`=CHOOSE(A1, NamedRange1, NamedRange2, NamedRange3)`

**PMT Function (Loan Payment):**

- Calculates the payment for a loan based on constant payments and a constant interest rate.

excelCopy code

`=PMT(0.05/12, 12*5, -10000)`

**INDEX and MATCH with Multiple Results:**

- Using INDEX and MATCH to retrieve multiple results.

excelCopy code

`=INDEX(A1:A10, SMALL(IF(B1:B10="X", ROW(B1:B10)-ROW(B1)+1), ROW(INDIRECT("1:"&COUNTIF(B1:B10,"X")))))`

- This is an array formula, so remember to enter it with Ctrl+Shift+Enter.

**IF and WEEKDAY:**

- Using WEEKDAY within IF for conditional date calculations.

excelCopy code

`=IF(WEEKDAY(A1)=1, "Sunday", "Not Sunday")`

**OFFSET and MATCH for Dynamic Data Ranges:**

- Using OFFSET and MATCH to create dynamic data ranges.

excelCopy code

`=OFFSET($A$1, 0, 0, MATCH("*", $A$1:$A$1000, 0), 1)`

**PERCENTILE.INC:**

- Returns the k-th percentile of values in a range.

excelCopy code

`=PERCENTILE.INC(A1:A10, 0.75)`

**INDEX and MATCH with Multiple Criteria (Summing):**

- Using INDEX and MATCH with multiple criteria to sum corresponding values.

excelCopy code

`=SUM(INDEX(B1:B10 * (A1:A10="X") * (C1:C10="Y"), , ))`

- This is an array formula, so remember to enter it with Ctrl+Shift+Enter.

**IFS with DATE Functions:**

- Using IFS with DATE functions for conditional date-based calculations.

excelCopy code

`=IFS(MONTH(A1)=1, "January", MONTH(A1)=2, "February", TRUE, "Other")`

**INDEX and MATCH with Dynamic Header Row:**

- Using INDEX and MATCH with a dynamic header row for flexible lookups.

excelCopy code

`=INDEX(INDEX($B$1:$Z$10, MATCH("X", $A$1:$A$10, 0), 0), MATCH("Y", $B$1:$Z$1, 0))`

**AVERAGE and OFFSET for Rolling Average:**

- Using AVERAGE and OFFSET to calculate a rolling average.

excelCopy code

`=AVERAGE(OFFSET(A1, -2, 0, 3, 1))`

**INDEX and MATCH with Multi-Criteria (Sum Range):**

- Using INDEX and MATCH with multiple criteria to sum values from a dynamic range.

excelCopy code

`=SUM(INDEX($C$1:$C$100, MATCH(1, ($A$1:$A$100="X")*($B$1:$B$100="Y"), 0)))`

- This is an array formula, so remember to enter it with Ctrl+Shift+Enter.

**MAXIFS and MINIFS:**

- Using MAXIFS and MINIFS to find the maximum and minimum values based on multiple criteria.

excelCopy code

`=MAXIFS(C1:C10, A1:A10, "X", B1:B10, "Y") =MINIFS(C1:C10, A1:A10, "X", B1:B10, "Y")`

**IF and NETWORKDAYS:**

- Using IF and NETWORKDAYS to calculate the number of working days between two dates.

excelCopy code

`=IF(NETWORKDAYS(A1, B1) > 5, "More than 5 workdays", "5 or fewer workdays")`

**INDEX and MATCH for Two-Way Lookup (Dynamic Range):**

- Using INDEX and MATCH for a two-way lookup with a dynamic range.

excelCopy code

`=INDEX(OFFSET($B$1, MATCH("X", $A$1:$A$10, 0), 0, 1, COUNTA($B$1:$Z$1)), , MATCH("Y", $B$1:$Z$1, 0))`

**CHOOSE with Date Functions:**

- Using CHOOSE with date functions for dynamic date calculations.

excelCopy code

`=CHOOSE(WEEKDAY(A1), "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")`

**INDEX and MATCH with Dynamic Row and Column References:**

- Using INDEX and MATCH with dynamic row and column references for flexible lookups.

excelCopy code

`=INDEX(INDEX($B$1:$Z$10, MATCH("X", $A$1:$A$10, 0), 0), MATCH("Y", $B$1:$Z$1, 0))`

**SUMPRODUCT for Weighted Sum:**

- Using SUMPRODUCT to calculate a weighted sum.

excelCopy code

`=SUMPRODUCT(A1:A10, B1:B10)`

**IFS with ISNUMBER and SEARCH:**

- Using IFS with ISNUMBER and SEARCH for conditional text matching.

excelCopy code

`=IFS(ISNUMBER(SEARCH("apple", A1)), "Contains Apple", ISNUMBER(SEARCH("orange", A1)), "Contains Orange", TRUE, "Other")`

**INDEX and MATCH with Dynamic Named Range:**

- Using INDEX and MATCH with a dynamic named range.

excelCopy code

`=INDEX(MyDynamicNamedRange, MATCH("X", MyDynamicNamedRangeCriteria, 0))`

**FILTER with Multiple Criteria:**– Using FILTER with multiple criteria to extract data based on conditions.`excel =FILTER(A1:C10, (A1:A10="X")*(B1:B10="Y"))`

**INDEX and MATCH for Last Occurrence:**– Using INDEX and MATCH to find the last occurrence of a value.`excel =INDEX(A1:A10, MAX(IF(A1:A10="X", ROW(A1:A10)-MIN(ROW(A1:A10))+1, 0)))`

– This is an array formula, so remember to enter it with Ctrl+Shift+Enter.**TEXT Functions for Formatting:**– Using TEXT functions for custom formatting.`excel =TEXT(A1, "dddd, mmmm d, yyyy")`

**INDEX and MATCH with Partial Matching:**– Using INDEX and MATCH with partial matching.`excel =INDEX(B1:B10, MATCH("*apple*", A1:A10, 0))`

**INDEX and SMALL for Nth Smallest Value:**– Using INDEX and SMALL to find the nth smallest value.`excel =INDEX(A1:A10, SMALL(IF(A1:A10<>0, MATCH(A1:A10, A1:A10, 0)), 2))`

– This is an array formula, so remember to enter it with Ctrl+Shift+Enter.**IF and ISTEXT with Multi-Column Check:**– Using IF and ISTEXT with a multi-column check.`excel =IF(ISTEXT(A1)+ISTEXT(B1)+ISTEXT(C1)>1, "Multiple Text Columns", "No or One Text Column")`

**INDEX and MATCH with Dynamic Column Reference:**– Using INDEX and MATCH with a dynamic column reference for flexible lookups.`excel =INDEX($B$1:INDEX($B$1:$Z$1, 1, MATCH("Y", $B$1:$Z$1, 0)), MATCH("X", $A$1:$A$10, 0))`

**OFFSET and SUM for Dynamic Sum Range:**– Using OFFSET and SUM for a dynamic sum range.`excel =SUM(OFFSET($B$1, 0, 0, MATCH("Total", $A$1:$A$100, 0), 1))`

**MATCH with INDEX for Unique Values:**– Using MATCH with INDEX to extract unique values.`excel =INDEX(A1:A10, MATCH(0, COUNTIF($C$1:C1, A1:A10)+COUNTIF($B$1:B1, A1:A10), 0))`

– This is an array formula, so remember to enter it with Ctrl+Shift+Enter.**IF and MIN/MAX for Conditional Extremes:**– Using IF with MIN and MAX for conditional extremes.`excel =IF(A1>B1, MIN(A1:A10), MAX(A1:A10))`

**INDEX and MATCH with Multi-Criteria and Dynamic Range:**– Using INDEX and MATCH with multiple criteria and a dynamic range.`excel =INDEX(OFFSET($C$1, MATCH("X", $A$1:$A$10, 0), 0, 1, COUNTA($C$1:$Z$1)), , MATCH("Y", $C$1:$Z$1, 0))`

**SUMIFS with Date Criteria:**– Using SUMIFS with date criteria.`excel =SUMIFS(B1:B10, A1:A10, ">01/01/2022", A1:A10, "<01/31/2022")`

**INDEX and MATCH for Top N Values:**– Using INDEX and MATCH to find the top N values.`excel =INDEX(A1:A10, MATCH(LARGE(B1:B10, 1), B1:B10, 0))`

**TEXT Functions for Time Formatting:**– Using TEXT functions for custom time formatting.`excel =TEXT(A1, "hh:mm AM/PM")`

**SUMPRODUCT with INDEX and MATCH (Two Criteria):**– Using SUMPRODUCT with INDEX and MATCH for two criteria.`excel =SUMPRODUCT((A1:A10="X")*(B1:B10="Y")*(C1:C10))`

**IFS with ISERR for Error Handling:**– Using IFS with ISERR for error handling.`excel =IFS(ISERR(1/0), "Error occurred", TRUE, "No Error")`

**INDEX and MATCH for Lookup with Interpolation:**– Using INDEX and MATCH for lookup with interpolation.`excel =INDEX(B1:B10, MATCH(1, (A1:A10<=X)*(A2:A11>X), 0)) + (X - INDEX(A1:A10, MATCH(1, (A1:A10<=X)*(A2:A11>X), 0))) / (INDEX(A1:A10, MATCH(1, (A1:A10<=X)*(A2:A11>X), 0) + 1) - INDEX(A1:A10, MATCH(1, (A1:A10<=X)*(A2:A11>X), 0)))`

– This is an array formula, so remember to enter it with Ctrl+Shift+Enter.**SUMPRODUCT for Weighted Average:**– Using SUMPRODUCT for weighted average calculation.`excel =SUMPRODUCT(A1:A10, B1:B10) / SUM(B1:B10)`

**IFERROR and VLOOKUP for Error Handling:**– Using IFERROR with VLOOKUP for error handling.`excel =IFERROR(VLOOKUP(A1, Table1, 2, FALSE), "Not Found")`

**INDEX and MATCH for Dynamic Table Lookup:**– Using INDEX and MATCH for dynamic table lookup.`excel =INDEX(INDIRECT("Table1[" & B1 & "]"), MATCH(A1, Table1[Column1], 0))`

**SUMIFS with Wildcards for Text Matching:**– Using SUMIFS with wildcards for text matching.`excel =SUMIFS(C1:C10, A1:A10, "X*", B1:B10, "*Y*")`

**INDEX and MATCH for Nth Occurrence:**– Using INDEX and MATCH to find the Nth occurrence of a value.`excel =INDEX(A1:A10, SMALL(IF(A1:A10="X", ROW(A1:A10)-MIN(ROW(A1:A10))+1, ""), N))`

– This is an array formula, so remember to enter it with Ctrl+Shift+Enter.**SUMPRODUCT with INDIRECT for Dynamic Range Sum:**– Using SUMPRODUCT with INDIRECT for dynamic range sum.`excel =SUMPRODUCT(INDIRECT("Sheet1!" & A1 & ":" & B1))`

**INDEX and MATCH for Unique Values with Multiple Criteria:**– Using INDEX and MATCH to extract unique values with multiple criteria.`excel =INDEX(A1:A10, MATCH(0, COUNTIF($B$1:B1, A1:A10)+COUNTIFS($C$1:C1, ">5"), 0))`

– This is an array formula, so remember to enter it with Ctrl+Shift+Enter.**IFS and CONCAT for Concatenating Text Based on Conditions:**– Using IFS and CONCAT for concatenating text based on conditions.`excel =IFS(A1>5, "High", A1<=5, "Low", TRUE, "Medium") & CONCAT(B1:B10)`

**INDEX and MATCH with Multiple Criteria for Dynamic Range:**– Using INDEX and MATCH with multiple criteria for a dynamic range.`excel =INDEX(OFFSET($C$1, MATCH("X", $A$1:$A$10, 0), 0, 1, COUNTA($C$1:$Z$1)), , MATCH("Y", $C$1:$Z$1, 0))`

**TEXTJOIN with FILTER for Concatenating Filtered Data:**– Using TEXTJOIN with FILTER to concatenate data based on specific criteria.`excel =TEXTJOIN(", ", TRUE, FILTER(A1:A10, B1:B10="X", C1:C10>5))`

**INDEX and MATCH with Advanced Dynamic Range:**– Using INDEX and MATCH with INDEX to create an advanced dynamic range.`excel =INDEX($B$1:$Z$1, , MATCH("Y", $B$1:$Z$1, 0)+1)`

**CHOOSE with TODAY for Dynamic Date Calculations:**– Using CHOOSE with TODAY for dynamic date calculations.`excel =CHOOSE(WEEKDAY(TODAY()), "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")`

**INDEX and MATCH for Case-Insensitive Lookup:**– Using INDEX and MATCH for a case-insensitive lookup.`excel =INDEX(B1:B10, MATCH(TRUE, EXACT(A1, C1:C10), 0))`

– This is an array formula, so remember to enter it with Ctrl+Shift+Enter.**IFS with INDEX and MATCH for Complex Conditions:**– Using IFS with INDEX and MATCH for complex conditions.`excel =IFS(INDEX(A1:A10, MATCH("X", B1:B10, 0))>5, "High", INDEX(A1:A10, MATCH("X", B1:B10, 0))<=5, "Low", TRUE, "Medium")`

**INDEX and MATCH for Unique Values Across Multiple Columns:**– Using INDEX and MATCH to extract unique values across multiple columns.`excel =INDEX($A$1:$C$10, MATCH(0, COUNTIF($D$1:D1, $A$1:$C$10)+COUNTIF($D$1:D1, $A$1:$C$10), 0))`

– This is an array formula, so remember to enter it with Ctrl+Shift+Enter.**INDEX and MATCH for Dynamic Chart Series:**– Using INDEX and MATCH for dynamic chart series.`excel =INDEX(Sheet1!$B$1:$Z$1, , MATCH(Sheet1!$A$1, Sheet1!$B$1:$Z$1, 0))`

**SUMIFS with INDEX and MATCH for Multiple Criteria:**– Using SUMIFS with INDEX and MATCH for multiple criteria.`excel =SUMIFS(C1:C10, A1:A10, INDEX(A1:A10, MATCH("X", B1:B10, 0)))`

**INDEX and MATCH for Conditional Formatting Rule:**– Using INDEX and MATCH to create a conditional formatting rule.`excel =INDEX($A$1:$A$10, MATCH("X", $B$1:$B$10, 0)) = $C$1`

**INDEX and SMALL for Nth Smallest Value in a Range:**– Using INDEX and SMALL to find the Nth smallest value in a range.`excel =INDEX(A1:A10, SMALL(IF(A1:A10<>0, MATCH(A1:A10, A1:A10, 0)), 3))`

– This is an array formula, so remember to enter it with Ctrl+Shift+Enter.