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 "VLOOKUP"

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

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

XLOOKUP()

Lookup & Reference

Modern 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 Functions

Tests 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

ISERROR()

Information Functions

Tests if value is an error

Syntax:

=ISERROR(value)

Examples:

=ISERROR(A1/B1)

TRUE if division results in error

=IF(ISERROR(VLOOKUP(A1, B:C, 2, FALSE)), "Not Found", VLOOKUP(A1, B:C, 2, FALSE))

Handle VLOOKUP errors

=IFERROR(A1/B1, "Division Error")

Simpler error handling

Note: Use IFERROR() for simpler error handling

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

LOOKUP()

Lookup & Reference

Looks up value in vector or array

Syntax:

=LOOKUP(lookup_value, lookup_vector, [result_vector])

Examples:

=LOOKUP(A1, B:B, C:C)

Vector form lookup

=LOOKUP("z", A1:A10)

Find last value ≤ 'z'

=LOOKUP(9.99E+307, B:B)

Get last non-empty value in column

Note: Requires sorted data. Use VLOOKUP or XLOOKUP for more control

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

📊 102 Total Formulas 🏷️ 10 Categories ⚡ Quick Search