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.
Excel Mastery Guide
Beginner
Start with basic calculations and data organization
Intermediate
Master lookup functions and data 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
XMATCH()
Lookup & ReferenceEnhanced version of MATCH function
Syntax:
=XMATCH(lookup_value, lookup_array, [match_mode], [search_mode])Examples:
=XMATCH(A1, B:B) Find exact position of A1 in column B
=XMATCH(A1, B:B, 1) Find exact match or next smallest
=XMATCH(A1, B:B, 0, -1) Search from last to first
Note: Available in Excel 365 and Excel 2021. More options than MATCH
MATCH()
Lookup & ReferenceReturns position of value in array
Syntax:
=MATCH(lookup_value, lookup_array, [match_type])Examples:
=MATCH("Apple", A1:A10, 0) Find exact position of 'Apple'
=MATCH(100, B1:B10, 1) Find largest value ≤ 100
=MATCH(A1, B:B, 0) Find position of A1 value in column B
Note: 0=exact match, 1=largest ≤ value, -1=smallest ≥ value
INDEX()
Lookup & ReferenceReturns 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
VLOOKUP()
Lookup & ReferenceLooks 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
TEXTSPLIT()
Text FunctionsSplits text into array using delimiters
Syntax:
=TEXTSPLIT(text, col_delimiter, [row_delimiter], [ignore_empty], [match_mode], [pad_with])Examples:
=TEXTSPLIT(A1, ",") Split by comma into columns
=TEXTSPLIT(A1, " ", CHAR(10)) Split by space and line breaks
=TEXTSPLIT("apple,banana,,cherry", ",", , TRUE) Split and ignore empty values
Note: Excel 365 Insider. Returns dynamic array. ignore_empty: TRUE skips empty results
XLOOKUP()
Lookup & ReferenceModern lookup function that replaces VLOOKUP and HLOOKUP
Syntax:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Examples:
=XLOOKUP(A1, B:B, C:C) Basic lookup from B to C
=XLOOKUP(A1, B:B, C:C, "Not Found") With custom error message
=XLOOKUP(A1, B:B, C:E) Return multiple columns
Note: Available in Excel 365 and Excel 2021. More flexible than VLOOKUP
ISNA()
Information FunctionsTests if value is #N/A error
Syntax:
=ISNA(value)Examples:
=ISNA(VLOOKUP(A1, B:C, 2, FALSE)) TRUE if VLOOKUP returns #N/A
=IF(ISNA(MATCH(A1, B:B, 0)), "Not Found", "Found") Handle MATCH #N/A errors
=IFNA(VLOOKUP(A1, B:C, 2, FALSE), "Not Found") Simpler #N/A handling
Note: Specifically tests for #N/A error. Use IFNA() for simpler handling
HLOOKUP()
Lookup & ReferenceLooks 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
Master Excel formulas with practice and repetition. Bookmark this page for quick reference!