Lookups in Excel: Master VLOOKUP, XLOOKUP, and INDEX/MATCH
A comprehensive 2026 guide to Excel lookups, covering VLOOKUP, HLOOKUP, XLOOKUP, INDEX/MATCH, error handling, and dynamic arrays for robust data retrieval.
Lookups in Excel let you pull related data from a table using a key you specify. The most common methods are VLOOKUP and XLOOKUP, with INDEX/MATCH offering flexibility when the lookup column isn’t leftmost. This guide walks through practical examples, best practices, and tips to build robust lookups excel workflows in 2026.
Understanding lookups in Excel and when to use them
Lookups excel are essential for pulling related data from a table based on a key. They streamline data integration across sheets, dashboards, and reports, reducing manual search time and minimizing errors. The core idea is simple: you provide a lookup_value, tell Excel where the data lives, and specify which column contains the return value. This section starts with a practical example and then expands into common variants you’ll encounter in daily work.
=VLOOKUP(A2, Sales!A:C, 3, FALSE)Explanation:
- lookup_value: A2
- table_array: Sales!A:C (data range that contains the key and return column)
- col_index_num: 3 (the third column in the table_range, i.e., column C)
- range_lookup: FALSE (exact match required)
Common variants and notes:
- If the key isn’t found, VLOOKUP returns #N/A unless wrapped with IFERROR.
- VLOOKUP requires the key to be in the leftmost column of the table_array.
- For unsorted data or exact matches, prefer FALSE for range_lookup.
- Alternative: INDEX/MATCH can perform similar lookups with greater flexibility.
XLS Library note: In practice, you’ll often build a small lookup table on a summary sheet and reuse the pattern across reports to ensure consistency.
code_examples_count_only_warning_only_for_breakdown_and_variations_not_needed_for_explanation_and_variation_explanation_ensure_code_present_in_blocks_to_match_requirements
extra_caption_ignored_please_ignore_this_field
Steps
Estimated time: 45-75 minutes
- 1
Identify dataset and keys
List the table that contains the lookup column and the return column. Determine the exact key you’ll use as lookup_value and where it lives.
Tip: Label your data clearly and use named ranges if possible. - 2
Choose a lookup method
Decide between VLOOKUP, INDEX/MATCH, or XLOOKUP based on your Excel version and data layout.
Tip: If you’re on 365, XLOOKUP often simplifies logic and improves reliability. - 3
Build the base formula
Write a simple lookup to verify correctness before adding complexity (e.g., exact match).
Tip: Start with a small range to test quickly. - 4
Test with multiple samples
Check several keys to ensure edge cases (not found, duplicates) are handled.
Tip: Include a not-found case to test error handling. - 5
Extend to multiple columns
If needed, retrieve values from additional columns and consider array formulas or dynamic arrays.
Tip: Document what each column represents for future readers.
Prerequisites
Required
- Required
- Familiarity with basic Excel functionsRequired
- Organized data tables with a clear key columnRequired
Optional
- Optional
- Basic data validation practices (optional)Optional
Keyboard Shortcuts
| Action | Shortcut |
|---|---|
| CopyCopy selected cell(s) before applying formulas | Ctrl+C |
| PastePaste formula results or values | Ctrl+V |
| Move to next cellNavigate while building formulas | Arrow keys or Enter |
| Cancel formula editingCancel editing a cell formula | Esc |
People Also Ask
What is the difference between VLOOKUP and XLOOKUP?
VLOOKUP searches the leftmost column and returns a value from a specified column to the right. XLOOKUP is more flexible: it can search in any column, return values from any column, and supports explicit not-found messages and wildcard matching. For new work, XLOOKUP is generally preferred when available.
VLOOKUP looks only to the right, while XLOOKUP is more flexible and easier to read. If you have XLOOKUP, use it for most lookups.
Can I look up data when the key isn’t in the first column?
Yes. Use INDEX/MATCH or XLOOKUP with a return array; INDEX/MATCH is particularly useful when you need the key to be left of the return column. For older Excel versions, INDEX/MATCH provides a robust alternative to VLOOKUP.
If your key isn’t in the leftmost column, INDEX/MATCH is a reliable workaround that lets you pull values from anywhere.
How do I perform multi-criteria lookups?
Multi-criteria lookups can be done with INDEX/MATCH combining multiple conditions or with XLOOKUP in some cases using concatenated keys or arrays. A common pattern is INDEX with MATCH and a logical test across columns.
Use a composite key or an array-based MATCH to combine criteria.
What should I do if a lookup returns not found often?
Use IFERROR or IFNA to provide friendly messages or fall back to alternate data sources. Verify that your keys match exactly (no extra spaces) and check data types.
Wrap your formula with IFERROR to show a helpful message instead of an error.
Is XLOOKUP available on all Excel versions?
XLOOKUP is available in Excel 365 and newer perpetual releases. If you’re on older Excel versions, you’ll rely on VLOOKUP, HLOOKUP, or INDEX/MATCH.
XLOOKUP is great, but only if your Excel version supports it; otherwise use tried-and-true alternatives.
The Essentials
- Choose the right lookup function for data needs.
- Prefer XLOOKUP for modern Excel lookups.
- Use INDEX/MATCH for left-lookups and multi-criteria scenarios.
- Wrap lookups in IFERROR to provide friendly fallbacks.
- Validate data types and keys before applying lookups.