LOOKUP function

The LOOKUP function in Excel is used to search for a value in a range or array and return a corresponding value from another range or array. It is one of the older lookup functions in Excel, but it is still useful in certain cases. The LOOKUP function can be used in two forms: Vector Form and Array Form.

Syntax:

LOOKUP(lookup_value, lookup_vector, [result_vector])

• lookup_value: The value you want to search for. It can be a number, text, or logical value.
• lookup_vector: A range or array containing the data you want to search for the lookup_value in. It must be a one-dimensional range or array (either a row or column).
• [result_vector] (optional): A range or array that contains the data you want to return. The result vector must be the same size as the lookup vector. If omitted, the function returns the value from the lookup vector.

1. Vector Form:

In vector form, the LOOKUP function searches for a value in a single row or column and returns a corresponding value from another row or column. Here’s the basic form:

LOOKUP(lookup_value, lookup_vector, result_vector)

Example 1: Vector Form Suppose you have a list of product names in A1:A5 and their prices in B1:B5, and you want to find the price of a product named “Apple.”

Use the formula:

=LOOKUP("Apple", A1:A5, B1:B5)

This formula will return 1.5, the price of “Apple,” because it finds the position of “Apple” in A1:A5 and returns the corresponding value from B1:B5.

2. Array Form:

In array form, LOOKUP searches for a value in a range and returns a value from another column or row within the same range. This form can search in both horizontal and vertical arrays. The lookup vector must be in ascending order for the function to work properly in this mode.

LOOKUP(lookup_value, array)

Example 2: Array Form If you have an array like the following:

You can use this formula:

=LOOKUP(3, A1:B5)

This formula will return 30, which is the value corresponding to 3 in the array A1:B5.

Important Notes:

• The LOOKUP function requires the lookup_vector to be sorted in ascending order for it to work correctly.
• If the lookup_value is smaller than the smallest value in the lookup_vector, the function will return the first value in the result_vector.
• If the lookup_value is larger than the largest value in the lookup_vector, the function will return the last value in the result_vector.
• If an exact match for the lookup_value is not found, the LOOKUP function will approximate the result by returning the largest value that is less than or equal to the lookup_value.

Example 3: Approximate Match

In the following data, you can find the closest match for a value:

If you want to find the value in column B corresponding to 4 (a value that doesn’t exist), the formula:

=LOOKUP(4, A1:A5, B1:B5)

will return 30, the largest value less than or equal to 4 in the lookup_vector (A1:A5).

Benefits of the LOOKUP function:

• Simple Usage: It’s easy to use when you need to find a value from a range and return a corresponding value from another range.
• Works with Sorted Data: It works well when data is sorted in ascending order, especially for finding approximate matches.
• Compact Formula: It’s concise compared to other lookup functions like VLOOKUP or HLOOKUP.

Use Cases:

• Price Lookup: If you have a price list in a table and want to look up prices based on a product name or code.
• Rankings: You can use LOOKUP to find the rank of a specific score from a list of scores.
• Grade Lookup: In academic settings, you can use LOOKUP to assign a grade based on a numerical score.

Limitations:

• No Exact Match: Unlike VLOOKUP, LOOKUP cannot return an exact match unless the data is sorted. If the value isn’t found, it returns an approximate match based on the largest value less than or equal to the lookup value.
• Does Not Handle Errors Well: If the data isn’t sorted properly or the value is out of range, it can lead to incorrect results or errors.

Alternative Functions:

• VLOOKUP and HLOOKUP: These functions are more powerful than LOOKUP when working with data that isn’t sorted or when you need to handle exact matches more flexibly.
• INDEX and MATCH: A more advanced combination of functions, INDEX and MATCH allow for more control over data lookup and do not require sorted data.

In summary, the LOOKUP function in Excel is a simple and effective way to find values in a range or array and return corresponding values from another range. However, it is best suited for situations where data is sorted in ascending order and approximate matches are acceptable.