Excel formulas
The Excel formulas you actually reach for — lookups, logic, text, totals and dates — each with an example and a one-line explanation.
Last updated
Lookups
| Formula | What it does |
|---|---|
| =VLOOKUP(A2, D:E, 2, FALSE) | Finds A2 in the range's FIRST column (D) and returns the 2nd column of that range (E) — the number counts columns inside D:E from 1, it is not an offset. FALSE demands an exact match. |
| =XLOOKUP(A2, D:D, E:E, "not found") | The modern replacement for VLOOKUP — looks left or right, and names a fallback instead of erroring. |
| =INDEX(E:E, MATCH(A2, D:D, 0)) | MATCH finds the row, INDEX returns the value there — works looking left, which VLOOKUP can't. |
| =HLOOKUP(A2, D1:H2, 2, FALSE) | Same as VLOOKUP but searches across a row instead of down a column. |
Logic
| Formula | What it does |
|---|---|
| =IF(A2>100, "High", "Low") | Tests a condition and returns one value if true, another if false. |
| =IFS(A2>100, "High", A2>50, "Mid", TRUE, "Low") | Checks conditions in order and returns the first match — reads cleaner than nested IFs. |
| =IFERROR(A2/B2, "n/a") | Runs the formula, and if it errors, shows your fallback instead of #DIV/0! or #N/A. |
| =IF(AND(A2>0, B2>0), "Both", "No") | AND requires every condition true; swap in OR to require just one. |
| =IF(A2>100, "High", IF(A2>50, "Mid", "Low")) | Nested IF for more than two outcomes — IFS above does the same job with less punctuation. |
Text
| Formula | What it does |
|---|---|
| =CONCAT(A2, " ", B2) or =A2&" "&B2 | Joins text from separate cells into one string — the & version works in every version of Excel. |
| =LEFT(A2, 3) =RIGHT(A2, 3) =MID(A2, 2, 3) | Pulls characters from the start, the end, or from a middle position you specify. |
| =TRIM(A2) | Strips leading, trailing and repeated internal spaces — fixes text pasted from elsewhere. |
| =LEN(A2) | Counts how many characters are in the cell, spaces included. |
| =TEXT(A2, "0.00") | Formats a number as text using a pattern you choose, e.g. currency or padded decimals. |
| =SUBSTITUTE(A2, "-", "/") | Replaces every occurrence of one piece of text with another inside a cell. |
| =TEXTSPLIT(A2, ",") | Splits one cell into several by a delimiter, spilling the pieces across neighboring cells. |
Numbers & totals
| Formula | What it does |
|---|---|
| =SUMIF(A:A, "West", B:B) | Adds up column B only where the matching row in column A meets the condition. |
| =SUMIFS(C:C, A:A, "West", B:B, ">100") | Like SUMIF but with multiple conditions across multiple columns, all must be true. |
| =COUNTIF(A:A, "West") =COUNTIFS(A:A, "West", B:B, ">100") | Counts rows matching one condition, or matching several at once. |
| =AVERAGEIF(A:A, "West", B:B) | Averages column B only where the matching row in column A meets the condition. |
| =ROUND(A2, 2) | Rounds to the number of decimal places you specify — negative numbers round to the left of the decimal. |
| =SUBTOTAL(9, A2:A100) | Sums (or averages, counts, etc. by code) only the visible rows — ignores anything filtered out. |
Dates
| Formula | What it does |
|---|---|
| =TODAY() | Today's date, recalculated every time the sheet opens or changes. |
| =NOW() | Today's date plus the current time, also live-recalculated. |
| =DATEDIF(A2, B2, "Y") | The difference between two dates in whole years — swap "Y" for "M" or "D" for months or days. |
| =EOMONTH(A2, 0) | The last day of A2's month — use 1 or -1 for next or previous month. |
| =NETWORKDAYS(A2, B2) | Counts working days between two dates, excluding weekends (and holidays if you list them). |
| =WEEKDAY(A2) | Returns the day of the week as a number, 1 for Sunday by default. |
Worth remembering
- →Everything here works in Google Sheets except two: TEXTSPLIT is Excel 365 only (Sheets uses SPLIT), and CONCAT in Sheets takes exactly two arguments, so use & or CONCATENATE for three or more. XLOOKUP needs Excel 2021/365 — Sheets has had it since 2022; older Excel needs VLOOKUP or INDEX+MATCH.
- →SUBTOTAL's first argument is a function code (9 = SUM, 1 = AVERAGE, 3 = COUNTA) — it's the one formula built specifically to respect filters.
Questions
Why does my lookup return #N/A on values that look identical?
Almost always trailing whitespace, a non-breaking space, or a number stored as text. Compare LEN of the two cells — a length difference with no visible difference confirms it immediately.
What do the dollar signs actually do?
They freeze part of a reference when the formula is filled. $A$1 is fixed, A$1 fixes the row, $A1 fixes the column — and getting this wrong is the most common cause of a formula that works in one cell and not the next.
Should I still use VLOOKUP?
Use XLOOKUP if you have it, and INDEX/MATCH otherwise. VLOOKUP's column number is a position within the range, so inserting a column silently returns the wrong field rather than erroring.
Why is my date showing as a five-digit number?
Because dates are stored as serial numbers counting from 1900, and the cell has a number format rather than a date one. The value is correct; only the display is wrong.
When should I stop using formulas?
When you are writing the same formula for a dozen categories, or when the workbook has become slow. A pivot table does both jobs with less to maintain and far less to get wrong.