Searches a range or array for a match and returns the corresponding item from a second range. Replaces VLOOKUP and HLOOKUP with faster, safer lookups that can look in any direction (left, right, up, down).
Syntax:=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Example Spreadsheet:
| Emp ID (A) | Name (B) | Department (C) | Salary (D) |
|---|
| E-101 | John Doe | Engineering | $95,000 |
| E-102 | Sarah Smith | Marketing | $82,000 |
| E-103 | Alex Ray | Product | $91,000 |
| E-104 | Emily Chen | Design | $88,000 |
Searches for a value in the first column of a table range and returns a value in the same row from a specified column to the right.
Syntax:=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
Example Spreadsheet:
| Product Code (D) | Product Name (E) | Category (F) | Unit Price (G) |
|---|
| P-100 | Wireless Mouse | Accessories | $29.99 |
| P-200 | Mechanical Keyboard | Hardware | $89.99 |
| P-300 | 4K USB-C Monitor | Displays | $349.99 |
Returns the value of an element in a table or array selected by row and column position numbers. Commonly paired with MATCH.
Syntax:=INDEX(array, row_num, [column_num])
Example Spreadsheet:
| Item (A) | Region (B) | Sales (C) |
|---|
| Laptops | North | $12,400 |
| Desktops | South | $8,900 |
| Monitors | East | $5,600 |
| Tablets | West | $7,800 |
Searches for a specified item in a range of cells and returns the relative position of that item (not the item itself).
Syntax:=MATCH(lookup_value, lookup_array, [match_type])
Example Spreadsheet:
| Products (A) | Inventory (B) |
|---|
| Laptops | 45 |
| Desktops | 20 |
| Monitors | 85 |
| Tablets | 32 |
Returns a cell or range of cells that is a specified number of rows and columns away from an initial reference cell.
Syntax:=OFFSET(reference, rows, cols, [height], [width])
Example Spreadsheet:
| A | B | C |
|---|
| Start | 10 | 20 |
| 30 | 40 | 50 |
| 60 | 70 | 80 |
| 90 | 100 | Goal: 110 |
Returns the reference specified by a text string. Allows creating dynamic formulas where sheet names or cell references change based on cell inputs.
Syntax:=INDIRECT(ref_text, [a1])
Example Spreadsheet:
| Sheet Number (A1) | Formula Target | Result |
|---|
| 2 | ="Sheet" & A1 & "!B5" | Value from Sheet2!B5 |
Checks whether a condition is met, returning one specified value if TRUE, and another value if FALSE.
Syntax:=IF(logical_test, value_if_true, [value_if_false])
Example Spreadsheet:
| Student (A) | Score (B) | Result (C) |
|---|
| Michael | 88 | Pass |
| Emma | 64 | Retake |
| Lucas | 92 | Pass |
Evaluates multiple conditions in sequential order and returns the value corresponding to the first TRUE condition without nesting IF formulas.
Syntax:=IFS(logical_test1, value_if_true1, [logical_test2, value_if_true2], ...)
Example Spreadsheet:
| Score (A) | Grade |
|---|
| 94 | A |
| 83 | B |
| 72 | C |
| 58 | F |
Catches formula errors (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, #NULL!) and returns a clean custom result instead of an ugly error code.
Syntax:=IFERROR(value, value_if_error)
Example Spreadsheet:
| Revenue (A) | Units (B) | Avg Price (C) |
|---|
| $1,000 | 10 | $100 |
| $500 | 0 | $0 (Safeguarded) |
Returns TRUE if all its arguments evaluate to TRUE; returns FALSE if one or more arguments evaluate to FALSE.
Syntax:=AND(logical1, [logical2], ...)
Example Spreadsheet:
| Age (A) | Status (B) | Eligible? |
|---|
| 22 | Citizen | Eligible |
| 17 | Citizen | Ineligible |
| 30 | Non-Citizen | Ineligible |
Returns TRUE if any argument evaluates to TRUE; returns FALSE only if all arguments evaluate to FALSE.
Syntax:=OR(logical1, [logical2], ...)
Example Spreadsheet:
| Role (A) | Tenure Years (B) | Status |
|---|
| Engineer | 6 | Bonus |
| Manager | 2 | Bonus |
| Intern | 1 | Standard |
Adds all numbers in a range that meet multiple criteria across multiple columns.
Syntax:=SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Example Spreadsheet:
| Region (A) | Quarter (B) | Category (C) | Sales (D) |
|---|
| North | Q1 | Hardware | $4,500 |
| South | Q1 | Software | $3,200 |
| North | Q2 | Hardware | $6,100 |
| North | Q2 | Services | $1,800 |
Counts the number of rows or cells across multiple ranges that meet multiple conditions simultaneously.
Syntax:=COUNTIFS(criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Example Spreadsheet:
| Status (A) | Department (B) |
|---|
| Completed | IT |
| In Progress | IT |
| Completed | Sales |
| Completed | IT |
Calculates the arithmetic average of all numbers in a range that meet one or more conditions.
Syntax:=AVERAGEIFS(average_range, criteria_range1, criteria1, ...)
Example Spreadsheet:
| Dept (A) | Level (B) | Salary (C) |
|---|
| Engineering | Senior | $120,000 |
| Engineering | Junior | $75,000 |
| Engineering | Senior | $130,000 |
| Marketing | Senior | $110,000 |
Rounds a number to a specified number of decimal digits according to standard mathematical rounding rules.
Syntax:=ROUND(number, num_digits)
Example Spreadsheet:
| Input Number (A) | num_digits | Result |
|---|
| 14.567 | 2 | 14.57 |
| 99.234 | 1 | 99.2 |
| 1420.75 | -2 | 1,400 (Nearest hundred) |
Splits text strings into an array of rows or columns using specified delimiters (e.g., commas, spaces, dashes). Modern alternative to "Text to Columns".
Syntax:=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])
Example Spreadsheet:
| Full Text (A) | Split Col 1 | Split Col 2 | Split Col 3 |
|---|
| John, Smith, NY | John | Smith | NY |
Combines text from multiple ranges or strings into one string, inserting a specified delimiter between each item. Easily skips blank cells.
Syntax:=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
Example Spreadsheet:
| Item 1 | Item 2 | Item 3 | Result |
|---|
| Red | (blank) | Blue | Red, Blue |
Removes all leading, trailing, and repeated spaces from text, leaving only single spaces between words. Essential for cleaning VLOOKUP key mismatches.
Syntax:=TRIM(text)
Example Spreadsheet:
| Raw String (A) | Trimmed Result |
|---|
| " Item A100 " | Item A100 |
Substitutes new_text for old_text in a text string. Case-sensitive.
Syntax:=SUBSTITUTE(text, old_text, new_text, [instance_num])
Example Spreadsheet:
| Original (A) | Formula | Cleaned |
|---|
| 123-456-7890 | =SUBSTITUTE(A2, "-", "") | 1234567890 |
Calculates the number of days, months, or years between two dates. Ideal for calculating employee tenure, age, or project timelines.
Syntax:=DATEDIF(start_date, end_date, unit)
Example Spreadsheet:
| Start Date (A) | End Date (B) | Unit | Result |
|---|
| 2020-01-15 | 2026-11-03 | "Y" | 6 Years |
| 2020-01-15 | 2026-11-03 | "M" | 81 Months |
Returns the number of whole working days between two dates, automatically excluding Saturdays, Sundays, and optional public holidays.
Syntax:=NETWORKDAYS(start_date, end_date, [holidays])
Example Spreadsheet:
| Start (A) | End (B) | Workdays |
|---|
| 2026-11-02 (Mon) | 2026-11-13 (Fri) | 10 Workdays |
Returns a list of unique values from a range or array, eliminating all duplicate rows or items automatically.
Syntax:=UNIQUE(array, [by_col], [exactly_once])
Example Spreadsheet:
| Raw List (A) | Unique Output |
|---|
| North | North |
| South | South |
| North | East |
| East | |
Filters a range of data based on criteria you define, returning only matching rows into a new dynamic table array.
Syntax:=FILTER(array, include, [if_empty])
Example Spreadsheet:
| ID | Client | Status | Amount |
|---|
| 101 | Acme Corp | Pending | $4,000 |
| 102 | Beta LLC | Paid | $2,500 |
| 103 | Gamma Inc | Pending | $6,200 |