Lookup function comparison

Excel Lookup Functions Guide

Compare XLOOKUP, VLOOKUP, HLOOKUP, INDEX, MATCH, and FILTER so you can choose the best lookup formula for each task.

Overview

  • Excel lookup functions connect keys to attributes — prices, names, tax rates. This hub orders them by skill level and links to comparisons, tutorials, and error fixes.

Functions in this cluster

  • VLOOKUP — legacy vertical lookup, key must be leftmost.
  • HLOOKUP — horizontal variant (rare today).
  • INDEX + MATCH — flexible, works on older Excel.
  • XLOOKUP — modern default in M365.
  • FILTER — all matches spill (M365).

Learning path

  • Week 1: [VLOOKUP tutorial](/blog/vlookup-excel-tutorial/) + [fix #N/A](/errors/na/).
  • Week 2: [INDEX MATCH guide](/blog/index-match-excel-guide/).
  • Week 3: [XLOOKUP](/functions/xlookup/) + [vs VLOOKUP](/compare/xlookup-vs-vlookup/).

Error-first path (for troubleshooters)

  • Start at [VLOOKUP #N/A](/problems/vlookup-returning-na/).
  • Then [XLOOKUP not finding value](/problems/xlookup-not-finding-value/).
  • Reference pages: [VLOOKUP](/functions/vlookup/) · [XLOOKUP](/functions/xlookup/).

Lookup function map

  • Use XLOOKUP for most modern lookup tasks because it can search left or right and has built-in not-found handling.
  • Use VLOOKUP when you need compatibility with older spreadsheets or legacy workbooks.
  • Use INDEX and MATCH when you need flexible row and column matching in complex models.

Common lookup problems

  • Use exact match when the lookup value must match the table value exactly.
  • Check for hidden spaces, mismatched data types, and unsorted lookup columns.
  • Use IFERROR only after fixing the underlying lookup logic.

Recommended next reads

  • Tutorial: VLOOKUP step-by-step and XLOOKUP migration on the blog.
  • Compare XLOOKUP vs VLOOKUP and INDEX MATCH vs XLOOKUP before changing legacy files.
  • Fix #N/A with the dedicated error page and VLOOKUP troubleshooting guide.

Quick answer: excel lookup functions

  • Excel lookup functions include VLOOKUP (legacy), XLOOKUP (modern), INDEX/MATCH (flexible), HLOOKUP (row-based), and FILTER (all matches in M365). Start with VLOOKUP basics, then INDEX MATCH or XLOOKUP.
  • Beginner path: VLOOKUP tutorial → #N/A fixes.
  • M365 path: XLOOKUP → FILTER for multi-match lists.

Learning path by skill level

  • Beginner: VLOOKUP tutorial → SUMIF totals → IF labels.
  • Intermediate: INDEX MATCH guide → SUMIFS/COUNTIFS dashboards → data validation lists.
  • Advanced: XLOOKUP → FILTER dynamic reports → Power Query cleanup before lookups.

Lookup cheat sheet links

  • [VLOOKUP](/functions/vlookup/) · [XLOOKUP](/functions/xlookup/) · [INDEX](/functions/index/) · [MATCH](/functions/match/) · [FILTER](/functions/filter/)
  • [Fix #N/A](/errors/na/) · [VLOOKUP #N/A problem](/problems/vlookup-returning-na/) · [XLOOKUP vs VLOOKUP](/compare/xlookup-vs-vlookup/)

People also ask

  • What is the best lookup function in Excel? — XLOOKUP for new M365 work; VLOOKUP for legacy; INDEX MATCH for two-way.
  • How to choose between VLOOKUP and INDEX MATCH? — VLOOKUP for simple right-side columns; INDEX MATCH for flexibility.