Quick Navigation

LOOKUP Function

Excel 2007+

Summary

The LOOKUP function searches for a value in a one-dimensional vector or the first row/column of a multi-dimensional array and returns a corresponding value from the same position in another vector or the last row/column of the array. It performs approximate matching when exact matches aren't found.

Syntax

LOOKUP(lookup_value, lookup_vector, [result_vector])

Parameters

Parameter	Type	Required	Description
lookup_value	`Any`	Yes	The value LOOKUP searches for. Can be a number, text, logical value, or cell reference.
lookup_vector	`Range`	Yes	A single row or column range that must be sorted in ascending order: ..., -2, -1, 0, 1, 2..., A-Z, FALSE, TRUE.
result_vector	`Range`	No	Optional single row or column range (same size as lookup_vector) containing values to return.

Using the LOOKUP Function

LOOKUP excels at simple one-dimensional lookups where data is pre-sorted. Use vector form for precise control over lookup and result ranges. The function automatically finds the largest value less than or equal to your search term when no exact match exists, making it ideal for range-based lookups like pricing tiers or grade boundaries.

Common LOOKUP Examples

Exact Match Lookup

=LOOKUP(4.19, A2:A6, B2:B6)

Searches for 4.19 in sorted frequencies (A2:A6) and returns corresponding color from B2:B6. Returns 'orange'.

Approximate Match Example

=LOOKUP(5.75, A2:A6, B2:B6)

Can't find exact 5.75, so matches largest value ≤ 5.75 (5.17) and returns 'yellow'.

Error When Too Small

=LOOKUP(0, A2:A6, B2:B6)

Returns #N/A since 0 is smaller than smallest value (4.14) in lookup_vector.

Array Form Usage

=LOOKUP("B", A1:E1)

Searches first row of array for 'B' and returns value from last row, same column.

Frequently Asked Questions

LOOKUP uses binary search algorithm which requires ascending order for correct approximate matching. Unsorted data produces unreliable results.

Vector form needs explicit lookup_vector and result_vector. Array form uses single range and auto-selects first/last row/column based on array dimensions.

Use LOOKUP for simple 1D vector lookups. Use VLOOKUP for 2D tables when you need column index control and exact matching.

Common Errors and Solutions

#N/A Error

Cause: lookup_value smaller than all values in lookup_vector or data not sorted ascending

Solution: Ensure lookup_vector sorted ascending. Verify lookup_value isn't smaller than minimum value.

Wrong Results

Cause: lookup_vector not sorted in ascending order

Solution: Sort lookup_vector: numbers ...,-2,-1,0,1,2..., text A-Z, logical FALSE then TRUE

#REF! Error

Cause: result_vector different size than lookup_vector

Solution: Both vectors must contain same number of cells

Notes

Data MUST be sorted ascending: ..., -2, -1, 0, 1, 2..., A-Z, FALSE→TRUE
Case-insensitive text matching
If omitted, result_vector defaults to lookup_vector
Microsoft recommends VLOOKUP/XLOOKUP for most modern use cases
Array form searches first row (wide arrays) or first column (tall/square arrays)

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Excel 365, Excel 2003, Excel XP, Excel 2000

Not available in:

Content last reviewed: December 11, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+