Excel Formulas Cheat Sheet

Master Excel with our comprehensive formulas reference guide. From basic math to advanced lookup functions, find syntax, examples, and expert tips for all essential Excel formulas.

Found 6 formulas matching "INDEX"

Excel Mastery Guide

Beginner

Start with basic calculations and data organization

Intermediate

Master lookup functions and data analysis

Advanced

Advanced functions and financial modeling

  • • INDEX-MATCH combinations
  • • Array formulas
  • • Financial functions (PV, FV, PMT)
  • • Statistical analysis

Excel Pro Tips

F4 Key: Toggle between absolute and relative references ($A$1, A$1, $A1, A1)

Ctrl + Shift + Enter: Enter array formulas (older Excel versions)

F9 Key: Calculate formulas manually when set to manual calculation mode

Ctrl + `: Toggle between formula view and result view

INDEX()

Lookup & Reference

Returns value at intersection of specified row and column

Syntax:

=INDEX(array, row_num, [column_num])

Examples:

=INDEX(A1:C10, 5, 2)

Value from row 5, column 2 of range

=INDEX(A:A, 10)

10th value in column A

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

Use with MATCH for lookup

Note: Often combined with MATCH for flexible lookups

CHOOSE()

Lookup & Reference

Chooses value from list based on index number

Syntax:

=CHOOSE(index_num, value1, [value2], ...)

Examples:

=CHOOSE(A1, "Red", "Blue", "Green")

Return color based on number 1-3

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

Day name from today's date

=CHOOSE(A1, B:B, C:C, D:D)

Choose column based on index

Note: Index starts at 1. Returns error if index is out of range

VLOOKUP()

Lookup & Reference

Looks up value in leftmost column and returns value from specified column

Syntax:

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

Examples:

=VLOOKUP(A1, B:D, 2, FALSE)

Exact match lookup in column B, return from column C

=VLOOKUP("Apple", A1:C10, 3, TRUE)

Approximate match lookup

=VLOOKUP(A1, Sheet2!B:E, 4, FALSE)

Lookup from another sheet

Note: Use FALSE for exact match, TRUE for approximate match

HLOOKUP()

Lookup & Reference

Looks up value in top row and returns value from specified row

Syntax:

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Examples:

=HLOOKUP(A1, B1:F5, 3, FALSE)

Exact match in row 1, return from row 3

=HLOOKUP("Q1", A1:E10, 2, FALSE)

Find Q1 and return value from row 2

=HLOOKUP(A1, Sheet2!A1:Z5, 4, TRUE)

Approximate match from another sheet

Note: Horizontal version of VLOOKUP

SORT()

Dynamic Array Functions

Sorts array by one or more columns

Syntax:

=SORT(array, [sort_index], [sort_order], [by_col])

Examples:

=SORT(A1:C10)

Sort by first column ascending

=SORT(A1:C10, 2, -1)

Sort by column 2 descending

=SORT(A1:C10, {1,2}, {1,-1})

Sort by column 1 asc, then column 2 desc

Note: Excel 365 and Excel 2021. sort_order: 1=ascending, -1=descending

MODE.MULT()

Statistical Functions

Returns vertical array of most frequently occurring values

Syntax:

=MODE.MULT(array)

Examples:

=MODE.MULT(A1:A10)

All most frequently occurring values

=MODE.MULT({1,1,2,2,3,4})

Returns array with 1 and 2

=INDEX(MODE.MULT(A1:A10), 1)

Get first mode from array

Note: Returns array formula. Available in Excel 2010+. Use Ctrl+Shift+Enter in older versions

Master Excel formulas with practice and repetition. Bookmark this page for quick reference!

📊 102 Total Formulas 🏷️ 10 Categories ⚡ Quick Search