LOOKUP Function in Excel (Formula, Examples)

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.

You can download this LOOKUP Function Excel Template here – LOOKUP Function Excel Template

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:

Popular Course in this category

All in One Excel VBA Bundle (35 Courses with Projects)4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion

View Course

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