LOOKUP Function

Finds a value in one row or column and returns the matching item from another.

Category

Lookup & Reference

Available in

All versions, Microsoft 365

Returns

A value from the result range

Want to practice LOOKUP in a real spreadsheet? Open a blank sheet in our Practice Sandbox and try it with live data, with the AI Excel tutor beside you. No Excel needed — just a free account.Try it free in the Sandbox

Overview

LOOKUP is Excel's oldest lookup function. It always does an approximate match, so the lookup range must be sorted in ascending order.

Today, XLOOKUP is a better choice, but LOOKUP still has a famous trick: finding the last value in a column.

Syntax

=LOOKUP(lookup_value, lookup_vector, [result_vector])

Arguments

ArgumentRequired?What it does
lookup_valueYesThe value to search for.
lookup_vectorYesOne row or column to search, sorted ascending.
result_vectorOptionalOne row or column of the same size to return from.

Examples

Example 1 — Grade bands

Score thresholds are sorted in column A; grades in column B.

D2fx=LOOKUP(C2, A2:A5, B2:B5)
ABCD
1MinGradeScoreGrade
20F78C
360D
470C
580B
=LOOKUP(C2, A2:A5, B2:B5) Explain

78 is not found, so LOOKUP falls back to the largest value below it — 70 — and returns C.

Example 2 — The last value in a column

This classic trick returns the last non-empty value in column B.

=LOOKUP(2, 1/(B:B<>""), B:B) Explain

1/(B:B<>"") gives 1 for filled cells and errors for blanks. LOOKUP can't find 2, so it settles on the last 1.

Common mistakes

  • Unsorted data. LOOKUP silently returns wrong answers if lookup_vector isn't sorted.
  • No exact match option. If you need an exact match, use XLOOKUP or INDEX/MATCH.

Notes

Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.