Excel LOOKUP function: A complete guide

The LOOKUP function is one of the classic tools available in spreadsheets. Although newer functions such as XLOOKUP have taken the spotlight in recent years, LOOKUP remains extremely useful. This guide brings together everything you need to know about LOOKUP in one place, explained clearly and supported by practical examples.

What the LOOKUP function does

LOOKUP is a handy tool that helps you quickly find a value in one list and return a matching value from another. Instead of requiring an exact match, it works with approximate values, meaning it will return the largest number that is still less than or equal to the one you’re searching for.

To get reliable results, your data should be sorted in ascending order, so the function can “climb” the list correctly. If the data isn’t sorted, LOOKUP might stop too soon and give the wrong result, so it’s always a good idea to double-check that your numbers increase steadily from top to bottom.

The two forms of LOOKUP

LOOKUP exists in two versions. The first is the vector form, which is the version that people actually use today. The second is the array form, which remains available for compatibility with very old spreadsheets but is rarely needed in modern work.

Vector LOOKUP explained

The syntax of vector LOOKUP is:

LOOKUP(lookup_value, lookup_vector, result_vector)

With the vector version, LOOKUP starts at the top of your list and checks each value one by one. When it finds a number bigger than the one you’re looking for, it goes back to the previous value and returns the matching result from the other list. If your number is bigger than everything in the list, LOOKUP simply returns the last value.

Array LOOKUP and why it is rarely used

The array version of LOOKUP has the following syntax:

LOOKUP(lookup_value, array)

With this form, the function looks only at the first row or first column of the array.

• If the array is wider than it is tall, LOOKUP searches across the first row and returns a value from the last row.
• If the array is taller than it is wide, it searches the first column and returns a value from the last column.

The user cannot control which part of the array is used for the search or for the output. Because of this limited flexibility, most people use the vector form instead.

Example: Price based on quantity

Let’s see a practical example in ONLYOFFICE Spreadsheet Editor to understand how the formula works.

We have the following table:

Quantity	Price
10	5
20	8
30	10
40	13

If we write:

=LOOKUP(25, A2:A5, B2:B5)

the function scans the list 10 → 20 → 30. When it reaches 30, it sees that 30 is greater than 25. So it steps back to 20 and returns the price in the same row, which is 8.

This makes LOOKUP ideal for ranges, thresholds, and anything that depends on finding the correct bracket rather than an exact number.

More practical examples you can use immediately

A classic example is grade conversion. You might want a formula that converts a numeric score into a letter grade without writing long chains of nested IF functions. LOOKUP handles this perfectly.

Example 1: Score to letter grade

Score	Grade
0	F
60	D
70	C
80	B
90	A

A formula such as:

=LOOKUP(78, A2:A6, B2:B6)

makes LOOKUP climb the list until it finds the closest score that is still not higher than the student’s value. A score of 78 produces C, because the last threshold below 78 is 70. This logic is extremely clear and avoids complicated IF statements.

Example 2: Shipping fees

Another example is shipping fees based on weight. Suppose your fees increase at 2-kg, 5-kg, and 10-kg intervals. LOOKUP will automatically return the right fee for any weight that falls between two thresholds, as long as your weight column is sorted from smallest to largest.

LOOKUP compared to other LOOKUP functions

Understanding how LOOKUP fits among other lookup functions helps you decide when it is the best tool and when another function is more appropriate.

LOOKUP and VLOOKUP

VLOOKUP looks for a value in the first column of a table and returns a value from a column to the right.

Syntax:
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

What it’s used for:
Retrieving data from vertical tables where the lookup value is always in the first column.

How it differs from LOOKUP:

• Searches only in the first column; LOOKUP works with any two separate lists.
• Can return values only from columns to the right.
• Supports exact matches; LOOKUP does not.
• Shows clear errors when a value isn’t found.
• Does not require sorted data for exact matches.
• LOOKUP requires sorted data and always performs approximate matching.

LOOKUP and XLOOKUP

XLOOKUP is the modern replacement for most lookup functions. It searches in any direction and returns a matching value with more control and reliability.

Syntax:
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

What it’s used for:
Flexible lookups in both vertical and horizontal layouts, with exact or approximate matching and custom error messages.

How it differs from LOOKUP:

• Searches up, down, left, right — no layout restrictions.
• Works with unsorted data.
• Handles exact and approximate matches.
• Offers custom messages when nothing is found.
• More precise and predictable.
• LOOKUP is simpler and shorter, but only reliable with sorted lists.

LOOKUP and HLOOKUP

HLOOKUP works like VLOOKUP but horizontally. It searches the first row of a table and returns a value from a row below.

Syntax:
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

What it’s used for:
Retrieving data from horizontal tables where the lookup value is in the first row.

How it differs from LOOKUP:

• Works only with horizontal tables; LOOKUP has no directional limitations.
• Cannot search above the first row or return results from rows above.
• Requires a specific layout; LOOKUP works with any separate lists.
• Being limited in flexibility, it is now mostly replaced by XLOOKUP.

LOOKUP and INDEX + MATCH

INDEX + MATCH is a powerful combination that gives full control over how a lookup is performed.

Syntax:
INDEX(return_array, MATCH(lookup_value, lookup_array, [match_type]))

What it’s used for:
Precise lookups where direction, match mode, and error behaviour matter. Ideal for professional models.

How it differs from LOOKUP:

• Works with unsorted data.
• Supports exact, approximate, and wildcard matching.
• Searches in any direction.
• Shows clear errors when no match is found.
• More accurate and flexible, but longer to write.
• LOOKUP is shorter, but limited and approximate-only.

LOOKUP and CHOOSE + MATCH

CHOOSE + MATCH lets you build custom lookup structures by rearranging data on the fly.

Syntax:
INDEX(CHOOSE({1,2}, ...), MATCH(...)) (structure may vary depending on setup)

What it’s used for:
Advanced lookups where you need to reorder columns virtually without editing the actual table.

How it differs from LOOKUP:

• Offers full control over the lookup structure.
• Works with unsorted data.
• Very flexible but more complex to maintain.
• LOOKUP avoids this complexity and works well when data is clean and sorted.

When LOOKUP is the right choice

LOOKUP is most helpful in situations where your data is sorted, where an approximate match is the intended behaviour, and where you prefer a simple, compact formula instead of multiple conditions. Threshold tables, commission levels, tax brackets, price ranges, shipping rate tables, and grade books are all good examples of situations where LOOKUP can save you time and produce very readable spreadsheets.

If you need exact matches, advanced error handling, or the ability to work with unsorted data, then LOOKUP is no longer the best tool. In those cases, more modern functions offer better control.

When LOOKUP should be avoided

LOOKUP is not suitable when your data is not sorted, when you require exact matching, when you need to control what happens if a value is not found, or when your spreadsheet is too complex to rely on approximate matching. In such cases, XLOOKUP or INDEX + MATCH will give you better reliability and more predictable behaviour.

Get ONLYOFFICE Spreadsheet Editor and simplify data management

Want to boost your spreadsheet efficiency? Open ONLYOFFICE Spreadsheet Editor and try these formulas right now.

If you don’t have a ONLYOFFICE DocSpace account yet, you can create one for free. Try it online or with our desktop applications.

START ONLINE GET DESKTOP APP