fx LookupLab
Cheat sheet · Print me

Excel Lookup Cheat Sheet

The five lookup functions side by side, a quick “which one should I use” guide, and the shortcuts worth memorizing. Print it (Ctrl/Cmd+P) and keep it next to your keyboard.

Lookup functions — syntax side by side

FunctionSyntaxBest forWatch out
VLOOKUP=VLOOKUP(value, table, col_index, FALSE)Quick right-side lookup; legacy compatibilityCannot look left; col_index breaks if columns move; always set FALSE
HLOOKUP=HLOOKUP(value, table, row_index, FALSE)Horizontal tables (headers in a row)Same limits as VLOOKUP, transposed
XLOOKUP=XLOOKUP(value, lookup_arr, return_arr, [if_missing])Modern default; any direction; built-in not-foundExcel 365 / 2021+ only
INDEX-MATCH=INDEX(return_col, MATCH(value, lookup_col, 0))Left lookups; two-way matrix; older ExcelTwo functions; MATCH needs 0 for exact
FILTER=FILTER(array, include, [if_empty])Return every matching row, not just one365 only; spills; give if_empty to avoid #CALC!

→ Full VLOOKUP vs XLOOKUP comparison · INDEX-MATCH vs XLOOKUP · XLOOKUP vs FILTER

Which one should I use?

Essential shortcuts

Navigate & select

  • Go to cell / rangeCtrl+G
  • Edge of data regionCtrl+↑↓←→
  • Select to edgeCtrl+Shift+↑↓
  • Select whole column / rowCtrl+Space / Shift+Space
  • Select allCtrl+A
  • Last used cellCtrl+End

Edit & formulas

  • Edit active cellF2
  • Absolute reference $A$1F4
  • AutoSumAlt+=
  • Toggle formula viewCtrl+`
  • Fill down / rightCtrl+D / Ctrl+R
  • Paste specialCtrl+Alt+V

Format & data

  • Format cells dialogCtrl+1
  • Number / date / time formatCtrl+Shift+1 / 3 / 2
  • Insert tableCtrl+T
  • Filter toggleCtrl+Shift+L
  • Insert current date / timeCtrl+; / Ctrl+Shift+;
  • Repeat last actionF4 / Ctrl+Y

Workbook

  • New / open / saveCtrl+N / O / S
  • Find / replaceCtrl+F / H
  • Switch worksheetCtrl+PgUp / PgDn
  • Undo / redoCtrl+Z / Y
  • PrintCtrl+P
  • Mac: substitute ⌘ for Ctrl⌘

Want to practice these? The free 30-exercise workbook covers lookup, text, date, logic, statistical and dynamic-array functions with an answer key. Every function also has a live in-browser lab.