LOOKUP Excel Function

LOOKUP Function in Excel (Table of Contents)

• LOOKUP Formula for Excel
• Explanation of LOOKUP Function in Excel
• Applications of LOOKUP Function in Excel
• How to Use LOOKUP Function?

LOOKUP Excel Function

The LOOKUP Excel 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 for Excel

LOOKUP Excel Formula has 2 different syntaxes:

LOOKUP Formula for Excel Syntax: 1 (vector)

  

Arguments Used in This LOOKUP Formula for Excel

• 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 for Excel 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 for Excel

• 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).

Recommended Courses

Explanation of LOOKUP Excel Function

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 excel 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 in Excel returns any datatype such as a string, numeric, date, etc. This datatype 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 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 Excel 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 VLOOKUP function in such cases.

Applications of LOOKUP Function in Excel

The LOOKUP Excel 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

How to Use LOOKUP Function in Excel?

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

LOOKUP Excel Function 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.

LOOKUP Function in Excel Example #2

Suppose you have a 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

LOOKUP Function in Excel 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.

LOOKUP Excel Function 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 About LOOKUP Function in Excel

• The lookup_vector must be sorted in an 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.

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

Recommended Articles

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

• How to Sort in Excel?
• LARGE Formula in Excel
• SMALL Formula in Excel
• How to use MONTH on Excel
• EXACT Excel Function
• AVERAGE Excel Function
• Match Excel Function
• INDIRECT Excel Function
• HLOOKUP Excel Function
• GetPivotData Excel Function