Part of the free Module 5: Excel Formulas and Functions · Function 54 of 105 · Full Excel course
Updated on 5 September 2026 · Works in Excel 365, 2021, 2019 and 2016 (and earlier).
The Excel LOOKUP function searches one row or one column for a value and returns the item in the same position from another row or column. LOOKUP in Excel always uses approximate matching on sorted data, which makes it ideal for grade bands and price tiers, and for the classic trick of returning the last value in a range.
LOOKUP syntax
LOOKUP has two forms. The vector form is the one to learn; the array form exists for compatibility with old spreadsheet programs and VLOOKUP or HLOOKUP do that job more clearly.
=LOOKUP(lookup_value, lookup_vector, [result_vector])
=LOOKUP(lookup_value, array)| Argument | Required or optional | What it does | Notes and defaults |
|---|---|---|---|
lookup_value |
Required | The value to search for: a number, text, logical value, name or reference. | If no exact match exists, LOOKUP uses the largest value that is less than or equal to lookup_value. |
lookup_vector |
Required (vector form) | A range of one row or one column to search. | Must be sorted in ascending order (numbers, then text, then logicals) or the result is unreliable. |
result_vector |
Optional | A one-row or one-column range of the same size as lookup_vector; the result comes from the matching position here. | If omitted, LOOKUP returns the matched value from lookup_vector itself. |
array |
Required (array form) | A block of cells. LOOKUP searches the first row or first column and returns from the last row or last column. | If the block is wider than it is tall, it searches the first row; otherwise the first column. Square blocks search the first column. |
How LOOKUP works
LOOKUP performs a binary search. It assumes lookup_vector is sorted ascending, jumps to the middle, decides which half to keep and repeats until it finds the last position whose value is less than or equal to lookup_value. It then returns the item in the same position of result_vector. Because of this method LOOKUP is fast on very long lists, but it has no exact-match option and gives unpredictable results on unsorted data. LOOKUP is not case-sensitive and, unlike most functions, it silently ignores error values in the lookup array, which is what makes the last-value trick below possible.
Version notes. LOOKUP works the same way in every version of Excel and in Google Sheets. In Excel 365 and Excel 2021, XLOOKUP replaces most LOOKUP uses with an exact match by default and a search-from-last option; in older versions use VLOOKUP with FALSE or INDEX with MATCH for exact matches.
Worked examples
Example 1: Find a price from a sorted product list
Products are sorted A to Z in A2:A5 with prices in B2:B5.
| A (Product) | B (Price) | Formula | Result |
|---|---|---|---|
| Apple | 40 | =LOOKUP("Orange", A2:A5, B2:B5) |
75 |
| Banana | 25 | ||
| Mango | 90 | ||
| Orange | 75 |
LOOKUP finds Orange in the third row of the vector and returns the third price, 75. If you searched for “Melon”, which is not in the list, LOOKUP would return 90 because Mango is the largest name that sorts before Melon; this is why exact matches need VLOOKUP or XLOOKUP.
Example 2: Grade bands with thresholds
Approximate matching is the right tool when values fall into bands. Thresholds are in D2:D5 and grades in E2:E5.
| D (Minimum score) | E (Grade) | Score in B2 | Formula | Result |
|---|---|---|---|---|
| 0 | D | 45 | =LOOKUP(B2, $D$2:$D$5, $E$2:$E$5) |
D |
| 50 | C | 68 | =LOOKUP(B3, $D$2:$D$5, $E$2:$E$5) |
C |
| 70 | B | 83 | =LOOKUP(B4, $D$2:$D$5, $E$2:$E$5) |
B |
| 90 | A | 95 | =LOOKUP(B5, $D$2:$D$5, $E$2:$E$5) |
A |
A score of 83 returns B because 70 is the largest threshold that does not exceed 83. The same formula replaces a chain of nested IFs.
Example 3: Last non-empty value in a column
This is the most searched LOOKUP formula. Column A has entries with blanks scattered among them.
| A | Formula | Result |
|---|---|---|
| North | =LOOKUP(2, 1/(A2:A6<>""), A2:A6) |
West |
| South | ||
| (blank) | ||
| West | ||
| (blank) |
The expression 1/(A2:A6<>"") gives 1 for filled cells and #DIV/0! for blanks. LOOKUP ignores the errors, and because 2 is larger than every 1 the binary search lands on the last 1, which is West. No Ctrl+Shift+Enter is needed in any version.
Example 4: Last date a task was marked complete
Combine the same pattern with a condition. Status is in A2:A200 and dates in B2:B200.
=LOOKUP(2, 1/(A2:A200="Complete"), B2:B200)Only rows where the status is Complete produce a 1, so the formula returns the date from the last such row. Wrap it in IFERROR to show a message when no row qualifies.
Example 5: Last number in a row
=LOOKUP(9.99E+307, A1:Z1)9.99E+307 is the largest number Excel accepts, so the search always overshoots and stops at the final numeric cell, skipping text and blanks. With result_vector omitted, LOOKUP returns the matched number itself.
Tips and common mistakes
- Sort the lookup vector. LOOKUP has no exact-match mode. If the list is not ascending you will get plausible but wrong answers with no error.
- Use it for bands, not IDs. Grade bands, tax brackets and shipping tiers suit approximate matching; looking up an invoice number does not.
- Prefer XLOOKUP or INDEX and MATCH for exact matches. They fail loudly with #N/A when the value is missing instead of returning the nearest neighbour.
- Keep both vectors the same size. Mismatched ranges return #VALUE! or silently misalign results.
- Remember the last-value trick needs the errors. Do not wrap the 1/(condition) part in IFERROR; the errors are what LOOKUP skips over.
- Anchor the vectors. Use $D$2:$D$5 style references before copying the formula down.
- Avoid the array form. Its choice of row or column search depends on the block shape, which surprises people when the data grows.
Errors and how to fix them
| Error | Cause | Fix |
|---|---|---|
| #N/A | lookup_value is smaller than the first item in lookup_vector, or the value is text while the vector contains only numbers (or the reverse). | Add a lower threshold (for example 0) at the top of the vector, or convert the data types so they match. |
| #VALUE! | lookup_vector and result_vector have different sizes, or a vector spans more than one row and one column. | Make both ranges the same length and one-dimensional. |
| #REF! | A vector points at cells that were deleted. | Re-select the ranges. |
| #NAME? | The function name or a named range is misspelt. | Check the spelling of LOOKUP and any names used. |
| Wrong value, no error | lookup_vector is not sorted ascending, or an exact match was expected. | Sort the data or switch to VLOOKUP with FALSE, INDEX and MATCH with 0, or XLOOKUP. |
Practice exercise
- Type the four products and prices from Example 1 and look up the price of Banana. Expected: 25.
- Build the grade table from Example 2 and grade the scores 49, 50, 89 and 90. Expected: D, C, B, A.
- Enter ten values in column A with two blank cells in between and return the last filled value with the LOOKUP(2, 1/…) formula. Expected: the bottom-most entry.
- Add a Status column and return the date of the last row marked Complete. Expected: one date; change a later row to Complete and watch it update.
- Shuffle the products in Example 1 so they are no longer sorted and repeat the lookup. Expected: a wrong or #N/A result, which shows why sorting matters.
Key takeaways
- LOOKUP searches one row or column and returns from a parallel row or column.
- It always uses approximate matching, so the lookup vector must be sorted ascending.
- It is the cleanest choice for banded lookups such as grades and price tiers.
- LOOKUP(2, 1/(condition), range) returns the last value that meets a condition without an array entry.
- For exact matches use VLOOKUP, INDEX and MATCH, or XLOOKUP in Excel 365 and 2021.
Related functions and lessons
- Excel Formulas and Functions library: every function page in this module.
- VLOOKUP: exact or approximate match down the first column of a table.
- HLOOKUP: the same across the first row.
- INDEX and MATCH: flexible exact lookups in any direction.
- CHOOSE: picks a value by position from a short list.
- XLOOKUP and XMATCH step-by-step tutorial for Excel 365 and 2021.
- Four types of lookup functions in Excel with examples.
- Excel Dashboard course: lookups feed almost every dashboard KPI card.
- Watch the video lessons on YouTube.com/@PKAnExcelExpert.
- Microsoft Support: LOOKUP function.
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, to the left or right. VLOOKUP searches the first column of a table, supports exact matching with FALSE as the last argument, and returns from a column to the right. Use LOOKUP for bands and last-value tricks; use VLOOKUP or XLOOKUP for exact matches.
Can LOOKUP do an exact match?
Not directly. When the data is sorted and the value exists, LOOKUP behaves like an exact match, but if the value is missing it returns the nearest smaller item instead of an error. For a guaranteed exact match use VLOOKUP with FALSE, INDEX and MATCH with match_type 0, or XLOOKUP, which matches exactly by default.
Why does LOOKUP with 2 and 1 divided by a condition return the last value?
The division creates an array of 1s where the condition is TRUE and #DIV/0! errors elsewhere. LOOKUP ignores errors, and because 2 is greater than any 1, its binary search settles on the final 1, which corresponds to the last row that meets the condition. It works in every Excel version without Ctrl+Shift+Enter.
Does LOOKUP need sorted data?
Yes. LOOKUP uses a binary search that assumes lookup_vector is sorted in ascending order. On unsorted data it can return a wrong value without any error, which is the most common cause of LOOKUP complaints. Sort the list first, or use a function with an exact-match option.
Is LOOKUP case-sensitive?
No. LOOKUP treats “apple” and “Apple” as the same value, as do VLOOKUP, HLOOKUP, MATCH and XLOOKUP. For a case-sensitive lookup combine EXACT with INDEX and MATCH, for example MATCH(TRUE, EXACT(range, value), 0) inside INDEX.
Want the finished version? Ready-made Excel dashboards, trackers and VBA systems built with these functions are available at NextGenTemplates.com.