Why we use the LOOKUP function in Ms Excel ?

VLOOKUP allows you to search a data range that is setup vertically.

Use LOOKUP, one of the lookup and reference functions, when you need to look in a single row or column and find a value from the same position in a second row or column.

For example, let's say you know the part number for an auto part, but you don't know the price. You can use the LOOKUP function to return the price in cell H2 when you enter the auto part number in cell H1.

Use the LOOKUP function to search one row or one column. In the above example, we're searching prices in column D.

Use VLOOKUP to search one row or column, or to search multiple rows and columns (like a table). It's a much improved version of LOOKUP. Watch this video about how to use VLOOKUP.

There are two ways to use LOOKUP: Vector form and Array form

Vector form: Use this form of LOOKUP to search one row or one column for a value. Use the vector form when you want to specify the range that contains the values that you want to match. For example, if you want to search for a value in column A, down to row 6.

Array form: We strongly recommend using VLOOKUP or HLOOKUP instead of the array form. Watch this video about using VLOOKUP. The array form is provided for compatibility with other spreadsheet programs, but it's functionality is limited.

An array is a collection of values in rows and columns (like a table) that you want to search. For example, if you want to search columns A and B, down to row 6. LOOKUP will return the nearest match. To use the array form, your data must be sorted.

Vector form

The vector form of LOOKUP looks in a one-row or one-column range (known as a vector) for a value and returns a value from the same position in a second one-row or one-column range.

Syntax

LOOKUP(lookup_value, lookup_vector, [result_vector])

The LOOKUP function vector form syntax has the following arguments:

lookup_value    Required. A value that LOOKUP searches for in the first vector. Lookup_value can be a number, text, a logical value, or a name or reference that refers to a value.

lookup_vector    Required. A range that contains only one row or one column. The values in lookup_vector can be text, numbers, or logical values.

Vector examples

You can try out these examples in your own Excel worksheet to learn how the LOOKUP function works. In the first example, you're going to end up with a spreadsheet that looks similar to this one:

Lookup() function is the first version of Vlookup() and Hlookup() function in Excel.

it helps return a value based on search for specific value in a column.

But you should sort your data first, otherwise you will get a wrong result.

I recommend to use Vlookup() or HLookup() funcations.

or you can use a combination of Index() Match() functions, which is my choise always.

thank you for the question

It help us in retrieving the number / data from multi column excel sheet.. Its helps us to get exact data from  various different excel sheets