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 , 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.
excelCopy code=AVERAGE(A1:A10)
Count:
- Counts the number of cells that contain numbers in a given range.
excelCopy code=COUNT(A1:A10)
Concatenate:
- Combines multiple text strings into one.
excelCopy code=CONCATENATE(A1, " ", B1)
IF Statement:
- Performs a logical test and returns one value if the test is true and another if false.
excelCopy code=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.
excelCopy code=VLOOKUP(A1, B1:C10, 2, FALSE)
HLOOKUP:
- Similar to VLOOKUP, but searches for the value in the first row of a table.
excelCopy code=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.
excelCopy code=INDEX(C1:E10, MATCH(A1, B1:B10, 0), 3)
SUMIF:
- Adds up numbers based on a specified condition.
excelCopy code=SUMIF(A1:A10, ">5")
TEXT:
- Converts a value to text in a specific format.
excelCopy code=TEXT(A1, "yyyy-mm-dd")
LEN:
- Returns the number of characters in a text string.
excelCopy code=LEN(A1)

SUMPRODUCT:
- Multiplies corresponding components in arrays and returns the sum of those products.
excelCopy code=SUMPRODUCT(A1:A10, B1:B10)
INDEX and MATCH (Multi-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.

IFERROR:
- Returns a specified value if a formula results in an error; otherwise, it returns the result of the formula.
excelCopy code=IFERROR(VLOOKUP(A1, B1:C10, 2, FALSE), "Not Found")
MATCH with Wildcard:
- Using MATCH with a wildcard for partial matches.
excelCopy code=MATCH("abc*", A1:A10, 0)
SUMIFS:
- Adds up numbers based on multiple criteria.
excelCopy code=SUMIFS(C1:C10, A1:A10, "X", B1:B10, "Y")
DATE Functions:
- Various date-related functions like YEAR, MONTH, DAY, etc.
excelCopy code=YEAR(A1)
TRIM:
- Removes extra spaces from text.
excelCopy code=TRIM(A1)
ROUND:
- Rounds a number to a specified number of digits.
excelCopy code=ROUND(A1, 2)
HYPERLINK:
- Creates a clickable hyperlink.
excelCopy code=HYPERLINK("http://www.example.com", "Visit Example")
RAND and RANDBETWEEN:
- Generates random numbers.
excelCopy code=RAND() =RANDBETWEEN(1, 100)
CHOOSE:
- Selects a value from a list of values based on a given position.
excelCopy code=CHOOSE(A1, "Option 1", "Option 2", "Option 3")

OFFSET:
- Returns a reference offset from a starting cell or range of cells.
excelCopy code=OFFSET(A1, 2, 3, 1, 1)
SUBTOTAL:
- Returns a subtotal in a list or database.
excelCopy code=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.
excelCopy code=SUMPRODUCT(A1:A10, B1:B10, C1:C10)
INDIRECT:
- Returns the reference specified by a text string.
excelCopy code=INDIRECT("Sheet1!A1")
DATEDIF:
- Calculates the difference between two dates in years, months, or days.
excelCopy code=DATEDIF(A1, B1, "d")
CEILING and FLOOR:
- Rounds numbers away from or towards zero.
excelCopy code=CEILING(A1, 5) =FLOOR(A1, 10)
CONCAT:
- Concatenates a range of cells.
excelCopy code=CONCAT(A1:A10)
IFS:
- Returns a value based on multiple conditions.
excelCopy code=IFS(A1>10, "High", A1>5, "Medium", A1<=5, "Low")
XLOOKUP:
- A modern alternative to VLOOKUP and HLOOKUP with enhanced features.
excelCopy code=XLOOKUP(A1, B1:B10, C1:C10)
SEQUENCE:
- Generates a sequence of numbers.
excelCopy code=SEQUENCE(10, 1, 1, 2)
IF with AND and OR:
- Using nested IF, AND, and OR functions for more complex logical tests.
excelCopy code=IF(AND(A1>5, OR(B1="X", B1="Y")), "Condition Met", "Not Met")
TEXTJOIN:
- Joins text from multiple ranges with a specified delimiter.
excelCopy code=TEXTJOIN(", ", TRUE, A1:A10)
SWITCH:
- Evaluates an expression against a list of values and returns the corresponding result.
excelCopy code=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 SMALL for Nth Smallest Value in a Range:

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.