How to use LOOKUP in Excel

LOOKUP finds a value in one list and returns the item in the same position from another list. It is useful for finding a range or category, such as a discount level for an order total. The numbers you search must be sorted from smallest to largest.

Vector syntax

=LOOKUP(lookup_value, lookup_vector, [result_vector])

lookup_value: the number or text you want to find.
lookup_vector: the sorted row or column Excel searches.
result_vector: the row or column with the answer you want Excel to return. It must have the same number of items as the lookup vector.

Approximate-match example

This table lists the minimum order amount for each delivery option. Put the order amount 150 in cell D2.

Minimum order amounts and delivery options
Cell	A: Minimum order	B: Delivery option
1	Minimum	Option
2	0	Basic
3	100	Plus
4	200	Premium

Enter this formula to find the option for an order of 150:

=LOOKUP(D2, A2:A4, B2:B4)

The answer is Plus. Excel finds the largest minimum order that is not greater than 150, which is 100, then returns the option from the same row.

Important behavior

Sort the lookup numbers from smallest to largest. LOOKUP can give the wrong answer if they are not sorted.
The answer is the closest value that is less than or equal to the lookup value. It is not always an exact match.
If the lookup value is below the smallest number in the list, Excel returns #N/A.
For an exact match, use XLOOKUP in newer Excel versions or combine INDEX and MATCH.

Related guides: MATCH in Excel · INDEX in Excel · All tutorials.