The most-used category in business Excel. Master this first if you're new — lookup functions are how you connect data between sheets, tables, and workbooks.
If you work with data in Excel, you'll spend more time in lookup functions than any other category. VLOOKUP alone is the third most-used function in the world. XLOOKUP replaced it in 2020 with cleaner syntax and better defaults. INDEX/MATCH remains the pro's choice for complex scenarios. This page organizes all 28 lookup and reference functions with recommendations on which to use when.
The classic vertical lookup. Widely known, still widely used, but has real limitations — only searches left-to-right, breaks when columns move. Learn it because everyone else uses it.
VLOOKUP Complete Guide →The 2020 replacement for VLOOKUP. Searches left OR right, handles missing values gracefully, no column-index counting. If you're on Excel 365, use this by default.
XLOOKUP Complete Guide →Two functions used together. More flexible than VLOOKUP — works in any direction, handles complex scenarios. Slightly harder to learn but pays back in every complex model.
INDEX/MATCH Complete Guide →Pick a value from a list by position. Great for scenario models, month-name lookups, and dynamic references without a lookup table. Underused for how powerful it is.
CHOOSE Complete Guide →Only if you're on Excel 365 or 2021+ and control the workbook. VLOOKUP still works everywhere and isn't deprecated. But XLOOKUP is easier to write, easier to maintain, and less error-prone — use it by default in new work.
On modern Excel with modern computers, the speed difference is negligible for most workbooks. Where INDEX/MATCH wins is flexibility — bidirectional lookups, two-way lookups, and complex nested scenarios that VLOOKUP simply cannot do.
Usually one of five causes: exact-match flag missing, leading/trailing spaces in lookup values, numbers stored as text (or vice versa), lookup value not in the first column of the table, or the value truly doesn't exist. Read our VLOOKUP #N/A fix guide.
Functions like FILTER, UNIQUE, and SORT that return multiple results at once instead of just one match. They live in the Dynamic Arrays category and often replace complex lookup formulas entirely.
Yes — VLOOKUP, HLOOKUP, INDEX, MATCH, and XLOOKUP all work in Google Sheets with identical syntax. Some newer functions like CHOOSEROWS or TAKE are Excel-only.
Type "find the price for product ID in column A" and get the exact XLOOKUP or INDEX/MATCH formula.
Get the Add-in →