Lookup & Reference

LOOKUP Function in Excel

Looks up a value in a one-row or one-column range and returns a value from the same position in another range.

Syntax

  • =LOOKUP(lookup_value, lookup_vector, [result_vector])

Arguments

  • lookup_value (required): Value to search for
  • lookup_vector (required): One-row or one-column range to search
  • result_vector (optional): Range to return value from

Examples

  • =LOOKUP(5, A1:A10, B1:B10) - Find 5 in A, return from B - Result: Matching value

Excel LOOKUP function documentation

  • LOOKUP is a legacy Lookup & Reference function that searches a one-row or one-column vector and returns a corresponding value.
  • Syntax: =LOOKUP(lookup_value, lookup_vector, [result_vector]).
  • Requires lookup_vector to be sorted ascending for correct approximate-match behavior.
  • Safer alternatives: [XLOOKUP](/functions/xlookup/), [VLOOKUP](/functions/vlookup/) with FALSE, or [INDEX](/functions/index/) + [MATCH](/functions/match/).

LOOKUP syntax and sorted-data rule

  • lookup_value: the value to find (or the nearest match below it in sorted data).
  • lookup_vector: single row or column range — must be sorted ascending.
  • result_vector (optional): same-size range holding return values; if omitted, lookup_vector is used.
  • If data is unsorted, LOOKUP returns incorrect results without warning — use exact-match functions instead.

Step-by-step: verify before using LOOKUP

  • Step 1 — Confirm lookup_vector is sorted ascending (Data → Sort, or helper =A2>=A1).
  • Step 2 — Use =LOOKUP(lookup_value, keys, results) with equal-length vectors.
  • Step 3 — Compare output against manual INDEX MATCH on three test values.
  • Step 4 — If exact match is required, migrate to =MATCH(value, vector, 0) + INDEX.
  • Step 5 — Document in the model that LOOKUP assumes sorted keys.

LOOKUP vs VLOOKUP vs XLOOKUP

  • LOOKUP — vector only; approximate on sorted data; easy to misuse on unsorted lists.
  • VLOOKUP — table-based; exact match with FALSE; key must be leftmost column.
  • XLOOKUP — exact match default; left/right lookup; if_not_found in M365.
  • Legacy files may still contain LOOKUP — audit during migration to XLOOKUP.

Worked examples to copy

  • Commission tier on sorted thresholds: =LOOKUP(sales, tier_limits, commission_rates).
  • Grade from sorted cutoffs: =LOOKUP(score, {0,60,70,80,90}, {"F","D","C","B","A"}).
  • Replace with exact match: =INDEX(results, MATCH(value, keys, 0)) when sort order is uncertain.
  • Horizontal vector: =LOOKUP(A2, $B$1:$M$1, $B$2:$M$2) when both vectors are one row.

People also ask

  • Why does LOOKUP return the wrong value? — Unsorted lookup_vector is the most common cause.
  • LOOKUP vs VLOOKUP? — LOOKUP uses vectors; VLOOKUP uses a table with a fixed column index.
  • Is LOOKUP still in Excel? — Yes, but rarely recommended for new models.
  • Can LOOKUP do exact match? — Not reliably; use MATCH with 0 or XLOOKUP instead.

Common errors

  • Data must be sorted ascending
  • Returns closest match less than or equal

Use cases

  • Simple lookups
  • Grade calculations
  • Tax brackets

Frequently asked questions

  • Why should I use XLOOKUP instead of LOOKUP? XLOOKUP is more powerful: it doesn't require sorted data, can search from bottom, handles errors natively, and can return arrays. LOOKUP is simpler but limited. Use XLOOKUP for new work; LOOKUP for backward compatibility.
  • How does LOOKUP handle approximate matches? LOOKUP always does approximate match on sorted data. It finds the largest value less than or equal to your lookup value. This makes it perfect for tax brackets or grade scales where you need range-based lookups.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use LOOKUP

  • Simple lookups — common Lookup & Reference scenario for LOOKUP.
  • Grade calculations — common Lookup & Reference scenario for LOOKUP.
  • Tax brackets — common Lookup & Reference scenario for LOOKUP.

LOOKUP in the Lookup & Reference category

  • Browse all Lookup & Reference functions at /categories/lookup-reference/ for related formulas.
  • LOOKUP syntax: =LOOKUP(lookup_value, lookup_vector, [result_vector])
  • lookup_value (required): Value to search for
  • lookup_vector (required): One-row or one-column range to search
  • result_vector (optional): Range to return value from
  • Confirm LOOKUP arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm LOOKUP arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • VLOOKUP (/functions/vlookup/): Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify.
  • HLOOKUP (/functions/hlookup/): Searches for a value in the top row of a table and returns a value in the same column from a row you specify.
  • XLOOKUP (/functions/xlookup/): Searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, XLOOKUP can return the closest (approximate) match.
  • INDEX (/functions/index/): Returns the value of an element in a table or array, selected by the row and column number indexes.
  • MATCH (/functions/match/): Returns the relative position of an item in an array that matches a specified value.

Errors to watch for

  • Data must be sorted ascending — review causes on linked error pages in the directory.
  • Returns closest match less than or equal — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter LOOKUP on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

LOOKUP worked examples to copy

  • =LOOKUP(5, A1:A10, B1:B10) — Find 5 in A, return from B. Expected result: Matching value.

LOOKUP reference summary for crawlers and offline review

  • LOOKUP belongs to the Lookup & Reference category in Excel. Looks up a value in a one-row or one-column range and returns a value from the same position in another range.
  • Full syntax: =LOOKUP(lookup_value, lookup_vector, [result_vector]). Open /functions/lookup/ for parameters, FAQs, and related pages.
  • Common mistakes: Data must be sorted ascending; Returns closest match less than or equal
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.