Returning values in Excel is the dependable process of retrieving a result from a dataset based on criteria, a position, or a lookup key. Whether you need a simple exact match, a closest match, or a value from a specific row and column, Excel provides multiple functions and features to control how and what is returned. This guide covers core methods such as INDEX, VLOOKUP, XLOOKUP, and FILTER, explains exact versus approximate matching, outlines syntax patterns, and highlights common pitfalls. You will learn when each technique is appropriate, how to handle missing items and errors, and how to structure formulas for clarity and maintenance.
Exact Match vs Approximate Match
An exact match returns a value only when the lookup finds an identical item according to data type and formatting. An approximate match finds the largest value less than or equal to a target, which requires data sorted in ascending order. Choose exact match for IDs, names, and SKUs; choose approximate match for scenarios such as tax tables, discount tiers, and age bands where interpolation is intended.
Exact Match Characteristics
- Requires an exact equality between lookup value and lookup array
- Works with text, numbers, dates, and error values
- Does not depend on sort order
- Typically safer and more predictable in spreadsheets maintained over time
Approximate Match Characteristics
- Finds the largest value less than or equal to the lookup value
- Requires data sorted in ascending order for reliable results
- Useful for ranges, brackets, and tiered calculations
- Can return incorrect results if data is unsorted or duplicates exist
INDEX and MATCH: A Robust Combination
INDEX and MATCH together form a flexible and resilient lookup pattern. INDEX retrieves a value from a specified position in a range, while MATCH locates the position of a lookup value. This combination supports both vertical and horizontal lookups, allows left-to-right and right-to-left searches, and does not require data to be sorted. It is widely used in dashboards, data validation, and reporting workflows.
Core INDEX and MATCH Patterns
| Pattern | Description | Use Case |
|---|---|---|
| INDEX column, MATCH exact | Returns an item based on an exact lookup | Look up price by product ID |
| INDEX row, MATCH exact | Returns an item across columns | Look up monthly value by name and month |
| INDEX 2D area, MATCH row, MATCH col | Returns a cell at the intersection | Matrix-style retrieval from a table |
INDEX MATCH Examples
- Exact match vertical: =INDEX(B2:B100, MATCH(E2, A2:A100, 0))
- Two-way lookup: =INDEX(C2:G100, MATCH(row_value, A2:A100, 0), MATCH(col_value, C1:G1, 0))
- Approximate with sorted data: =INDEX(C2:C100, MATCH(E2, A2:A100, 1))
VLOOKUP, HLOOKUP, and Legacy Patterns
VLOOKUP searches the first column of a table and returns a value from another column in the same row. HLOOKUP works horizontally across rows. These functions are concise but require the lookup column to be the first in the table array, which can limit flexibility. They support both exact and approximate match modes and remain common in inherited spreadsheets.
VLOOKUP Quick Reference
| Argument | Description |
|---|---|
| lookup_value | The value to search for |
| table_array | Range containing at least two columns |
| col_index_num | Column number in table_array to return |
| range_lookup | FALSE for exact, TRUE or omitted for approximate |
Common VLOOKUP Patterns
- Exact match: =VLOOKUP(G2, A2:D50, 4, FALSE)
- Approximate match: =VLOOKUP(G2, A2:D50, 3, TRUE)
- Error handling with IFERROR: =IFERROR(VLOOKUP(G2, A2:D50, 2, FALSE), "Not found")
XLOOKUP: Modern Alternative
XLOOKUP replaces VLOOKUP, HLOOKUP, and INDEX/MATCH with a simpler, more consistent syntax. It searches a range for a key and returns a corresponding result from a results range. XLOOKUP supports exact and approximate matching, wildcards, and search direction control. It can return multiple values and works with dynamic arrays when used appropriately.
XLOOKUP Syntax Highlights
- lookup_value: value to find
- lookup_array: range to search
- return_array: range to return from
- [if_not_found]: optional custom message
- [match_mode]: 0 exact, -1 exact exact or next smaller, 1 exact or next larger, 2 wildcard
- [search_mode]: 1 search first to last, -1 search last to first
XLOOKUP Examples
- Exact match: =XLOOKUP(E2, A2:A100, B2:B100, "Not found", 0)
- Approximate match descending: =XLOOKUP(E2, A2:A100, B2:B100, , -1, -1)
- Return multiple values: =XLOOKUP(E2#, A2:A100, B2:D100)
FILTER and Dynamic Array Functions
FILTER returns multiple rows or columns that meet one or more conditions, which is useful when more than one item matches. With dynamic arrays, you can extract unique values, sort, and return results that automatically spill. These capabilities make it easier to build lists, summaries, and conditional extracts without helper columns.
FILTER and Related Functions Overview
| Function | Purpose | Example Use |
|---|---|---|
| FILTER | Return values meeting conditions | Extract rows where status = "Complete" |
| UNIQUE | Extract distinct values | List of product names without duplicates |
| SORT | Sort range or array | Rank customers by revenue |
| SEQUENCE | Generate numeric arrays | Create row or column indices |
| XMATCH | Return position only | Find location of a value |
FILTER and XMATCH Combined
- Return row where condition met: =FILTER(A2:E100, (B2:B100="Active") * (C2:C100=E2))
- First matching row: =INDEX(A2:C100, XMATCH(TRUE, (D2:D100>1000), 0))
Best Practices and Common Pitfalls
Using structured references with tables improves readability and reduces errors from shifting ranges. Prefer exact match unless approximate behavior is explicitly required. Always consider error handling with IFERROR or IFS to manage #N/A and other error conditions gracefully. When using INDEX and MATCH, verify that dimensions align and that MATCH returns a valid numeric position. With XLOOKUP, check match_mode and search_mode to avoid unexpected direction or matching behavior. Keep lookup tables consistent in type and formatting, and avoid volatile patterns that recalculate excessively in large models.
Summary and Decision Guide
Return values in Excel reliably by choosing the right function and match strategy. For simple vertical lookups, XLOOKUP is the modern default; INDEX and MATCH remain powerful and widely compatible; VLOOKUP and HLOOKUP work for quick tasks on stable tables. Use FILTER and dynamic arrays when you expect multiple results. Follow match mode and sort-order rules, handle errors, and prefer exact match for identifiers and keys. These patterns will support accurate, maintainable spreadsheets across reports, analyses, and production models.