Excel LOOKUP Function

What is the LOOKUP function?

LOOKUP is one of Excel's oldest and most classical lookup functions. Its Turkish name is ARA, and it's used to search for a value in a vector (single row/column) or array. It's the ancestor of VLOOKUP and HLOOKUP, and although XLOOKUP exists today, LOOKUP still appears in many places.

LOOKUP has two distinct syntaxes: the vector form (search in a single row or column) and the array form (search inside a matrix). The vector form is more flexible and more common today.

LOOKUP Syntax

Vector form:

=LOOKUP(lookup_value, lookup_vector, [result_vector])

• lookup_value: (Required) The value being searched.
• lookup_vector: (Required) The single row or column being searched. Must be sorted ASCENDING!
• result_vector: (Optional) Another vector returning the matched value. If omitted, the value from lookup_vector is returned.

Array form:

=LOOKUP(lookup_value, array) — used less often.

What LOOKUP Does — Practical Use Cases

1. Classic Table Lookup

To find a student's letter grade based on a numeric score: =LOOKUP(score,A2:A6,B2:B6). If A has 0, 50, 60, 70, 85 (sorted) and B has F, D, C, B, A, LOOKUP matches the score to the appropriate range and returns the letter grade.

2. Commission Calculation

Ideal for tiered commissions based on sales volume: =LOOKUP(salesAmount,commissionMin,commissionRate). Tiers like 1000-5000 = 3%, 5000-10000 = 5%, 10000+ = 7% work flawlessly.

3. Finding the Closest Value

LOOKUP doesn't require an exact match; it finds the largest value less than or equal to the search value. Useful in budgeting and scale conversions. Example: matching a 17.5°C temperature to a category — automatically located in a sorted range.

4. Finding the Last Filled Value

A classic technique for the last non-empty cell in a column: =LOOKUP(2,1/(A:A<>""),A:A). The expression 1/(A:A<>"") produces 1s and errors, and LOOKUP(2,...) finds "the position of the last 1 passed."

5. Legacy Compatibility

If you work with very old Excel versions (2000, 2003) used by clients, you may have to use LOOKUP. Millions of firms still rely on legacy versions.

Advanced Usage Techniques

The Danger of Unsorted Data

The most critical rule of LOOKUP: the lookup_vector must be sorted ascending. If it isn't, LOOKUP returns the WRONG result silently — it doesn't even raise an error! That's why modern apps prefer VLOOKUP(...,FALSE) or XLOOKUP.

Error Handling

LOOKUP returns #N/A if no match is found, or returns the closest smaller value. Wrap it: IFERROR(LOOKUP(...),"Not found") for safer behavior.

Risk of the Array Form

=LOOKUP(value,A1:E10) is the array form. Excel decides whether to search columns or rows based on dimensions — this behavior is ambiguous and confuses users. AVOID the array form; use the vector form instead.

Common Mistakes and How to Fix Them

The sneakiest LOOKUP error is silently wrong results in unsorted vectors. You're searching for row 100 in a vector ordered 10, 50, 5, 90 — LOOKUP returns row 5's match without any error! To avoid this trap, use modern alternatives: VLOOKUP with FALSE for exact match, or the more modern XLOOKUP. Another common error: result_vector having a different length than lookup_vector — they must match exactly.

Performance and Version Compatibility

LOOKUP works in every Excel version — from the very first Excel in 1985 to today. Performance is very fast because it uses binary search. But this speed advantage only holds for sorted data; unsorted data sacrifices both speed and correctness.

Related Functions

• VLOOKUP: Vertical table lookup (most common classical function).
• HLOOKUP: Horizontal table lookup.
• XLOOKUP: Modern flexible lookup (Excel 365). The advanced successor to LOOKUP.
• INDEX / MATCH: Classic combination for complex lookups.
• MATCH: Returns the position of a value.

Conclusion

LOOKUP is one of Excel's most fundamental functions. In today's modern world, VLOOKUP and XLOOKUP are usually preferred — but when you work with legacy spreadsheets, or use special tricks like "find last filled cell," LOOKUP is still indispensable. However, the requirement that the lookup vector be sorted ascending is critical — break this rule and you get silent wrong results. For modern projects, prefer VLOOKUP(FALSE) or XLOOKUP, and use LOOKUP only for legacy compatibility.