Excel Formulas Cheat Sheet — VLOOKUP, INDEX, MATCH, SUMIF, IF | Dataplexa

Excel Formulas

VLOOKUP · INDEX & MATCH · SUMIF & COUNTIF · IF & nested IF · Text · Date · Array

Sheet 1 of 3 Excel 365 Beginner–Inter. Printable

Formula Fundamentals

must know first

How Formulas Work

= sign starts every formula

=A1+B1             add two cells
=A1*1.1            multiply by 1.1
=A1/B1             divide
=A1^2              power (A1 squared)

Operator precedence:
  ^ → * / → + - → &
  Use ( ) to override order
=(A1+B1)*C1

Cell References

Relative  — moves when copied
=A1              row & col shift

Absolute  — stays fixed ($)
=$A$1            F4 to toggle

Mixed     — lock one axis
=$A1             col fixed, row moves
=A$1             row fixed, col moves

Named Range
=SUM(SalesData)   readable!

Operators & Wildcards

Comparison operators
=  <>  >  <  >=  <=

Text join (concatenate)
=A1&" "&B1
=CONCAT(A1," ",B1)

Wildcards (in SUMIF, VLOOKUP…)
*     any number of chars
?     exactly one char
~*    literal asterisk

=COUNTIF(A:A,"S*")  starts with S

Golden rule: Always start with =. Press F4 to cycle through reference types ($A$1 → A$1 → $A1 → A1). Use Ctrl+` to show all formulas at once.

Lookup Functions

VLOOKUP · HLOOKUP · INDEX · MATCH · XLOOKUP

VLOOKUP — vertical lookup

=VLOOKUP(lookup_value,
         table_array,
         col_index_num,
         [range_lookup])

Exact match (most common)
=VLOOKUP(A2,$D$2:$F$100,2,FALSE)

Approximate match (sorted data)
=VLOOKUP(A2,$D$2:$F$100,2,TRUE)

Wildcard VLOOKUP
=VLOOKUP("*"&A2&"*",D:F,2,FALSE)

INDEX + MATCH — flexible & fast

INDEX: return value at position
=INDEX(array, row_num, [col_num])

MATCH: return position of value
=MATCH(lookup_value, array, [type])
  0=exact  1=less  -1=greater

Combined — the VLOOKUP killer
=INDEX($C$2:$C$100,
   MATCH(A2,$B$2:$B$100,0))

2-way lookup (row & column)
=INDEX(B2:F10,
   MATCH(H1,A2:A10,0),
   MATCH(H2,B1:F1,0))

XLOOKUP — Excel 365 / 2021+

=XLOOKUP(lookup_value,
          lookup_array,
          return_array,
          [if_not_found],
          [match_mode],
          [search_mode])

Basic — replaces VLOOKUP
=XLOOKUP(A2,D:D,E:E)

With fallback if not found
=XLOOKUP(A2,D:D,E:E,"Not found")

Wildcard match_mode = 2
=XLOOKUP("S*",D:D,E:E,,2)

VLOOKUP limitation: Can only look right — the lookup column must be leftmost. INDEX+MATCH has no this restriction and is faster on large datasets. Use XLOOKUP if your Excel version supports it.

IF & Logic Functions

IF · IFS · AND · OR · NOT

IF — basic & nested

=IF(logical_test, value_if_true, value_if_false)

=IF(A2>90,"A","B")

Nested IF (up to 64 levels)
=IF(A2>=90,"A",
  IF(A2>=80,"B",
  IF(A2>=70,"C","F")))

IFS, AND, OR, NOT

IFS — cleaner than nested IF
=IFS(A2>=90,"A",
     A2>=80,"B",
     A2>=70,"C",
     TRUE,"F")

AND — all conditions must be true
=IF(AND(A2>0,A2<100),"OK","Bad")

OR — any condition true
=IF(OR(A2="NY",A2="LA"),"USA","Other")

NOT — invert a condition
=IF(NOT(ISBLANK(A2)),A2,"Empty")

Use IFS instead of deeply nested IF in Excel 2019+ and 365 — it's much easier to read and maintain.

SUM & COUNT Family

aggregate

SUM variants

=SUM(A1:A100)
=SUMIF(range, criteria, [sum_range])
=SUMIF(A:A,"North",B:B)

SUMIFS — multiple criteria
=SUMIFS(sum_range,
        criteria_range1, criteria1,
        criteria_range2, criteria2)
=SUMIFS(C:C,A:A,"North",B:B,"Q1")

COUNT variants

=COUNT(A:A)      count numbers only
=COUNTA(A:A)     count non-empty cells
=COUNTBLANK(A:A) count empty cells

COUNTIF — one condition
=COUNTIF(A:A,"North")
=COUNTIF(B:B,">100")

COUNTIFS — multiple conditions
=COUNTIFS(A:A,"North",B:B,">50")

Math & Statistical Functions

numbers

Math Essentials

=ROUND(A1, 2)      round to 2 dp
=ROUNDUP(A1, 0)    always round up
=ROUNDDOWN(A1,0)  always round down
=INT(A1)           floor integer
=MOD(A1, 5)        remainder
=ABS(A1)           absolute value
=POWER(A1, 3)      A1 cubed
=SQRT(A1)          square root
=RAND()            random 0–1
=RANDBETWEEN(1,100)

Statistical

=AVERAGE(A1:A100)
=AVERAGEIF(A:A,"North",B:B)
=AVERAGEIFS(C:C,A:A,"N",B:B,"Q1")
=MEDIAN(A1:A100)
=MODE(A1:A100)
=MAX(A1:A100)
=MIN(A1:A100)
=LARGE(A1:A100, 3) 3rd largest
=SMALL(A1:A100, 2) 2nd smallest
=STDEV(A1:A100)   std deviation

Rank, Percentile & Subtotal

Rank a value in a list
=RANK(A2,$A$2:$A$100,0)
  0=descending  1=ascending

=PERCENTRANK(A1:A100,A2)
=PERCENTILE(A1:A100,0.9)

SUBTOTAL ignores hidden rows
=SUBTOTAL(9,A1:A100)  9=SUM
=SUBTOTAL(1,A1:A100)  1=AVERAGE
=SUBTOTAL(2,A1:A100)  2=COUNT

Text Functions

string manipulation

Extract & Clean

=LEFT(A1, 3)         first 3 chars
=RIGHT(A1, 4)        last 4 chars
=MID(A1, 3, 5)      5 chars from pos 3
=LEN(A1)             string length
=TRIM(A1)            remove extra spaces
=CLEAN(A1)           remove non-printable
=SUBSTITUTE(A1,"old","new")

Search, Case & Format

=FIND("@",A1)      case-sensitive pos
=SEARCH("@",A1)    case-insensitive pos
=UPPER(A1)          ALL CAPS
=LOWER(A1)          all lowercase
=PROPER(A1)         Title Case
=TEXT(A1,"0.00")   number as text
=TEXT(A1,"dd/mm/yyyy")
=VALUE(A1)          text → number
=REPT("★", A1)   repeat char A1 times

Date & Time Functions

dates

Today, Now & Extract

=TODAY()           current date
=NOW()             current date + time
=YEAR(A1)          extract year
=MONTH(A1)         extract month 1–12
=DAY(A1)           extract day 1–31
=WEEKDAY(A1,2)    1=Mon…7=Sun
=WEEKNUM(A1)       week number of year
=DATE(2026,1,15)  build a date

Date Arithmetic

Days between two dates
=B1-A1             simple subtraction
=DATEDIF(A1,B1,"D")  days
=DATEDIF(A1,B1,"M")  months
=DATEDIF(A1,B1,"Y")  years

Working days only
=NETWORKDAYS(A1,B1)
=WORKDAY(A1, 30)  30 biz days from A1
=EDATE(A1, 3)     3 months from A1
=EOMONTH(A1, 0)  last day of month

Error Handling

IFERROR · IFNA · ISERROR · error types

IFERROR & IFNA

IFERROR — catch any error
=IFERROR(formula, value_if_error)
=IFERROR(
    VLOOKUP(A2,D:F,2,FALSE),
    "Not found")

IFNA — only #N/A errors
=IFNA(
    VLOOKUP(A2,D:F,2,FALSE),
    0)

Return blank on error
=IFERROR(A1/B1,"")

Error Types

#DIV/0!  divided by zero
#N/A     lookup value not found
#NAME?   unrecognised function name
#NULL!   invalid cell intersection
#NUM!    invalid numeric value
#REF!    invalid cell reference
#VALUE!  wrong argument type
######   column too narrow to show

IS* Check Functions

=ISERROR(A1)   any error → TRUE
=ISERR(A1)     any error except #N/A
=ISNA(A1)      #N/A only → TRUE
=ISBLANK(A1)   empty cell → TRUE
=ISNUMBER(A1) number → TRUE
=ISTEXT(A1)   text → TRUE
=ISLOGICAL(A1)TRUE/FALSE → TRUE

Use IFNA over IFERROR for lookup functions — IFERROR silently hides all errors including typos in your formula. IFNA only catches the expected "not found" case.

Dynamic Array Formulas

Excel 365 · 2021+

FILTER — extract matching rows

=FILTER(array, include, [if_empty])

Rows where region = "North"
=FILTER(A2:C100,
        A2:A100="North",
        "No results")

Multiple conditions (AND)
=FILTER(A2:C100,
    (A2:A100="North")*
    (B2:B100>100))

Multiple conditions (OR)
=FILTER(A2:C100,
    (A2:A100="N")+
    (A2:A100="S"))

SORT, SORTBY & UNIQUE

SORT — sort a range
=SORT(A2:C100, 2, -1)
  col 2, descending (-1)

SORTBY — sort by another range
=SORTBY(A2:A100,
        B2:B100, -1)

UNIQUE — deduplicated list
=UNIQUE(A2:A100)

Unique across rows (by_col=FALSE,
exactly_once=TRUE)
=UNIQUE(A2:C100,FALSE,TRUE)

SEQUENCE & TOCOL / TOROW

SEQUENCE — generate number series
=SEQUENCE(10)        1 to 10
=SEQUENCE(5,3)     5 rows × 3 cols
=SEQUENCE(10,1,0,5) 0,5,10…45

TOCOL — range to single column
=TOCOL(A1:C3)

LET — name intermediate results
=LET(
    x, A1*1.2,
    y, B1+50,
    x+y)

Spill range (#): Dynamic array formulas spill into neighboring cells automatically. Reference the whole spill range with A1# — the # tells Excel to include all spilled results.

Excel Formulas Mastery Checklist

sheet 1 complete

References & Logic	Key point
Toggle reference types	F4 key → $A$1
Conditional branching	IF / IFS / AND / OR
Nested conditions cleanly	IFS over nested IF
Use wildcards in criteria	* and ? in SUMIF

Lookups & Aggregates	Key point
Lookup in any direction	INDEX+MATCH or XLOOKUP
Sum with one condition	SUMIF(range, criteria, sum)
Sum with many conditions	SUMIFS
Ignore hidden rows in sum	SUBTOTAL(9, range)

Text, Dates & Errors	Key point
Clean and format text	TRIM · PROPER · TEXT
Calculate date differences	DATEDIF · NETWORKDAYS
Trap lookup errors	IFNA over IFERROR
Filter / sort dynamically	FILTER · SORT · UNIQUE

Next up → Sheet 2: Excel Pivot Tables · pivot · slicer · calculated field · grouping · value field settings — master summarising large datasets with zero formulas.

Back to Cheat Sheets