## LOOKUP Excel Function

Lookup can also be known as the older version of vlookup function, where in vlookup we search the reference value and match it in the entire table or data, in lookup value we do the same in a single column or row, there are two functions for lookup as both the functions as different arguments, when we use lookup formula in excel we get a prompt asking which function we wish to use.

The LOOKUP function returns a value from a range of values (a row or a column) or from an array. It is a built-in function in Excel.

### LOOKUP Formula

#### LOOKUP Formula Syntax: 1 (vector)

** **** **

**Arguments Used in this LOOKUP Formula**

**value****–**The value to search for**.****lookup_vector****–**A row, or a column range in which a value is to be searched**.**The vector should be sorted in ascending order**result_vector****–**[optional] A row or a column range, which corresponds to the*lookup_vector*and contains the desired output**.**

#### LOOKUP Formula **Syntax II: (array)**

The LOOKUP excel function searches for *a value* in the first row or column of the array and returns the corresponding value in the last row or column of the array.

**Arguments Used in this LOOKUP Formula**

*value***–**The value to search for.**array –**An array of values. The first row/column of the array is similar to the*lookup_vector*above (Syntax I) and the last row/column of the array is similar to the*result_vector*above (Syntax I).

### Explanation

Depending on the size of the row and column in the given *array*, the function decides whether to consider the row or the column for the search. If the size of the row is greater than the size of the column, it looks for *value* in the first row. If the row size is less than or equal to column size, it searches value in the first column and returns the corresponding value in the last column.

The *result_vector *and *lookup_vector *should be of the same size. The LOOKUP function excels searches *value* in *lookup*_*vector* and returns the value having the same position in the *result_vector*. If this parameter is omitted, it will return the first column of data. The *value*, *lookup_vector*, and *result_vector* can be any datatype – a numerical value, a string, dates, currency, etc.

#### Output

The LOOKUP function returns any data type such as a string, numeric, date, etc. This data type is similar to the datatype of *result_vector*. However, it only returns a single datatype. If there is a repetition of *value* in *lookup*_*vector*, it will consider the last occurrence of a *value* in *lookup*_*vector* and return its corresponding value from the *result*_*vector*.

#### When the value is not present in lookup_vector

If the LOOKUP function cannot find an exact match in *lookup*_*vector*, it considers the largest value in *lookup_vector* that is less than or equal to *value*. If *the value* is smaller than all of the values in the *lookup_vector*, then the LOOKUP function in excel gives an error. If *lookup_vector* is not sorted in ascending order, the LOOKUP function in excel will return an incorrect value. You can consider using a VLOOKUP function in such cases.

**How to use the LOOKUP Function in Excel?**

LOOKUP function is very simple and easy to use. Let’s understand the working of LOOKUP Function with examples.

#### Example #1

Suppose you have a list of items say flowers, its Identifier and it’s the latest price as shown below.

Now, using the ID, you can extract the price of the flower. The syntax for the same would be:

LOOKUP(ID_to_search, A5:A10,C5:C10)

The value that you want to search can also be a cell reference. Suppose the ID that you want to search is in E5, then the syntax would be:

LOOKUP(E5, A5:A10, C5:C10)

The above syntax will return 50.

Similarly, you can use the flower name to search for its price. If you want to look at the price of orchid, you will give the syntax as:

LOOKUP(“orchid”, B5:B10, C5:C10)

which will return 90.

#### Example #2

Suppose you have data of several transactions made since 2009 as shown below.

Now, given any year in cell D4, you can extract the information of the last transaction done in that year using the following syntax:

=LOOKUP(D4, YEAR(A4:A18), B4:B18)

where YEAR(A4:A18) will retrieve the year from the dates in A4:A18.

Since, D4 = 2012, it will return 40000.

Similarly, you can extract the last transaction done in the month of March as:

=LOOKUP(3, MONTH(A4:A18),B4:B18)

which returns 110000

#### Example #3

You can also retrieve the last entry of a column using the LOOKUP Excel function. Suppose you have data (list of IDs) in column B,

you can identify the last entry in column B using the LOOKUP Excel formula:

=LOOKUP(1,1/(B:B<>””),B:B)

Here, the value is 1; *lookup_vector* is 1/(B:B<>””); result_vector is B:B.

B:B<>”” will form an array of true and false. True means some value is present, and false means absent. 1 is then divided by this array to form another array of 1 and 0, corresponding to true and false.

The value is 1, so it will look for 1 in the array of 1 and 0, matches with the last 1, and returns the corresponding value of the match. The corresponding value here is the actual value at that position, which is 10 in the above lookup function example.

If the last value is 20 on cell B23, it will return 20 as shown below.

Let us take a lookup function example of an array where you would use the syntax II.

#### Example #4

Suppose you have an array B3:I24 having the student’s roll number (ID) in the 1^{st} column, followed by their names, marks in five different subject, and average marks secured in the last column as shown below.

You can retrieve the average marks of any student using his/her ID. If the ID to look for is in cell K4, then the syntax would be given as:

LOOKUP(K4, B4:I24)

It will return the corresponding average marks of the student.

### Things to Remember

- The lookup_vector must be sorted in ascending order.
- The result_vector and lookup_vector should be of the same size.
- When the value is not found in lookup_vector, the function matches the largest value in lookup_vector that is less than or equal to value.
- If the lookup_value is greater than all values in lookup_vector, the function matches the last value.
- When the value is less than the smallest value in lookup_vector, the function returns an error (#N/A).
- It is not case-sensitive.

### Applications

The LOOKUP Function is used to find value when one of its pairs is known. The following are some of the applications of the function:

- Extract the price of an item using its identifier
- Find the location of the book in the library
- Get the last transaction by month or year
- Check the latest price of an item
- Find the last row in numeric/text data
- Get the date of the last transaction

### LOOKUP Excel Function Video

### Recommended Articles

This has been a guide to LOOKUP Excel Function. Here we discuss the LOOKUP Formula and how to use it in Excel along with practical examples and downloadable excel template. You may also look at these useful functions in excel

- 35+ Courses
- 120+ Hours
- Full Lifetime Access
- Certificate of Completion