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.
| Function | Syntax | Best for | Watch out |
|---|---|---|---|
| VLOOKUP | =VLOOKUP(value, table, col_index, FALSE) | Quick right-side lookup; legacy compatibility | Cannot 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-found | Excel 365 / 2021+ only |
| INDEX-MATCH | =INDEX(return_col, MATCH(value, lookup_col, 0)) | Left lookups; two-way matrix; older Excel | Two functions; MATCH needs 0 for exact |
| FILTER | =FILTER(array, include, [if_empty]) | Return every matching row, not just one | 365 only; spills; give if_empty to avoid #CALC! |
→ Full VLOOKUP vs XLOOKUP comparison · INDEX-MATCH vs XLOOKUP · XLOOKUP vs FILTER
XLOOKUP — simplest, looks any direction, handles missing values.INDEX-MATCH (or VLOOKUP for a simple right-side lookup).XLOOKUP or INDEX-MATCH — VLOOKUP cannot.FILTER.INDEX with two MATCHes, or XLOOKUP nested.IFNA(...,"—") or use XLOOKUP’s 4th argument. See the #N/A fix.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.