Part of the free Module 5: Excel Formulas and Functions · Function 53 of 102 · Full Excel course
The Excel LOOKUP function searches a single row or column for a value and returns the corresponding item from another row or column. LOOKUP in Excel always performs an approximate match on sorted data, which makes it less strict than VLOOKUP but ideal for banded lookups and for a classic trick: returning the last value in a range.
LOOKUP syntax
=LOOKUP(lookup_value, lookup_vector, [result_vector])
=LOOKUP(lookup_value, array)The vector form is the one to learn. The array form searches the first row or column of a block and returns from the last row or column; VLOOKUP and HLOOKUP do that job more clearly.
Arguments
| Argument | Required | Meaning |
|---|---|---|
| lookup_value | Required | The value to find: number, text, logical value or reference. |
| lookup_vector | Required | A one-row or one-column range, sorted in ascending order. |
| result_vector | Optional | A range of the same size as lookup_vector from which the result is taken. If omitted, LOOKUP returns the matched value from lookup_vector itself. |
If no exact match exists, LOOKUP returns the largest value that is less than or equal to lookup_value. If lookup_value is smaller than every value in the vector, the result is #N/A.
Step-by-step example
Product names sorted A to Z are in A2:A5 and prices in B2:B5. To find the price of Orange:
=LOOKUP("Orange", A2:A5, B2:B5)- Excel scans A2:A5 for “Orange” using an approximate (binary) search.
- It stops at the last cell that is less than or equal to “Orange” in alphabetical order; when the name exists, that is the exact cell.
- It returns the value in the same position of B2:B5.
Practical use cases
1. Grade bands
Thresholds 0, 50, 70, 90 in D2:D5 and grades D, C, B, A in E2:E5:
=LOOKUP(B2, D2:D5, E2:E5)A score of 83 returns B, because 70 is the largest threshold not exceeding 83.
2. Last non-empty value in a column
=LOOKUP(2, 1/(A2:A100<>""), A2:A100)The expression 1/(condition) produces 1 for filled cells and #DIV/0! for blanks. LOOKUP ignores the errors and, because 2 is larger than every 1, it lands on the last 1 in the array. No Ctrl+Shift+Enter is required.
3. Last date a task was completed
=LOOKUP(2, 1/(A2:A200="Task Complete"), B2:B200)4. Last number in a row
=LOOKUP(9.99E+307, A1:Z1)9.99E+307 is the largest number Excel accepts, so the formula returns the final numeric entry.
Common mistakes and errors
- Wrong result on unsorted data – LOOKUP has no exact-match option. If lookup_vector is not ascending, results are unpredictable. Use VLOOKUP with FALSE, INDEX-MATCH with 0, or XLOOKUP.
- #N/A – lookup_value is smaller than the first item in the vector, or the value is text while the vector is numbers.
- #VALUE! – lookup_vector and result_vector are different sizes.
- Using the array form – it silently decides whether to search a row or a column based on the block’s shape; a wide block switches to horizontal search.
- Case – LOOKUP is not case-sensitive.
Related functions
- VLOOKUP and HLOOKUP – exact-match alternatives for tables.
- INDEX and MATCH – flexible lookup in any direction.
- CHOOSE – picks a value by position.
- XLOOKUP and XMATCH tutorial for Excel 365.
- Browse the Excel Formulas hub or watch videos on YouTube.com/@PKAnExcelExpert.
Frequently asked questions
What is the difference between LOOKUP and VLOOKUP?
LOOKUP works on a single row or column, always uses approximate matching and can return from any parallel range. VLOOKUP searches the first column of a table, supports exact matching and returns from a column to the right.
Can LOOKUP do an exact match?
Not directly. Sorted data with the value present behaves like an exact match, but for guaranteed exact matching use VLOOKUP with FALSE, INDEX-MATCH with match_type 0, or XLOOKUP.
Why does LOOKUP(2, 1/(range<>””), range) return the last value?
The division creates an array of 1s and errors. LOOKUP skips the errors and, since 2 is greater than any 1, its approximate search settles on the final 1, which corresponds to the last filled cell.
Need a ready-made template? Browse 7,000+ Excel, Power BI and Google Sheets templates at NextGenTemplates.com.