LOOKUP

The LOOKUP function is used to search for a value in a range or array and return a value that corresponds to the first match found. It is handy for quickly looking up values in a list.

Syntax

=LOOKUP(lookup_value, lookup_vector, result_vector)

lookup_value The value you want to find in the lookup_vector.
lookup_vector The range or array in which to search for the lookup_value.
result_vector The range or array from which the corresponding value will be returned.

About LOOKUP

When you're faced with a list of values and you need to quickly find a specific value in that list, LOOKUP is your go-to function in Excel. It simplifies the search process by scanning through a range or array to match a specified value, subsequently retrieving the corresponding value linked to the first match detected. This functionality proves particularly useful in scenarios where you seek to extract relevant data from large lists or tables swiftly and with minimal effort. It streamlines the lookup process, benefiting tasks such as data analysis, comparisons, and data manipulation effortlessly.

Examples

Suppose you have a list of student names in column A and their corresponding grades in column B. To find the grade of a specific student (e.g., 'John') using the LOOKUP function, you can use the following formula: =LOOKUP('John', A:A, B:B). This will return John's grade based on the first match found in column A, and retrieve the corresponding grade from column B.

Imagine you have a table with product names in column A and their prices in column B. To quickly look up the price of a specific product (e.g., 'Chair') using LOOKUP, you could enter the formula: =LOOKUP('Chair', A2:A10, B2:B10). This formula searches for 'Chair' in the product names and returns the price corresponding to the first match detected.

Questions

What happens if LOOKUP does not find an exact match to the lookup_value?

If LOOKUP cannot find an exact match to the lookup_value, it will return the closest match in the lookup_vector that is less than or equal to the lookup_value. It is crucial to organize your data appropriately to ensure the accuracy of the returned results.

Can LOOKUP search for values in both rows and columns?

Yes, LOOKUP can search for values in both rows and columns. You specify the lookup_vector and result_vector to indicate the ranges or arrays to search and return values from, making it versatile in handling various data structures.

How does LOOKUP handle duplicate values in the lookup_vector?

LOOKUP will return the first match it finds in the lookup_vector. If there are duplicate values, it will retrieve the corresponding value for the first occurrence of the value.

Related functions

VLOOKUP
HLOOKUP
INDEX
MATCH
XLOOKUP