Excel Functions Tutorials
- Excel Formulas Cheatsheet
- Financial Functions in Excel
- Logical Functions in Excel
- TEXT Functions in Excel
- Lookup Reference in Excel
- Address Function in Excel
- Choose Function in Excel
- Column Function in Excel
- Columns Function in Excel
- REPLACE Function in Excel
- GetPivotData in Excel
- HLOOKUP in Excel
- Hyperlink Excel Function
- INDIRECT Function in Excel
- LOOKUP Excel Function
- Match Excel Function
- VLOOKUP Excel Function
- INDEX Excel Function
- VLOOKUP vs HLOOKUP
- TRANSPOSE Excel Function
- Row Function in Excel
- OFFSET Excel Function
- Maths Functions in Excel
- POWER Function in Excel
- EVEN Function in Excel
- ODD Function in Excel
- ABS Function in Excel
- SUM Function in Excel
- SUMPRODUCT Function in Excel
- SUBTOTAL Excel Function
- ROUND in Excel
- AGGREGATE Excel Function
- PRODUCT Excel Function
- RAND Excel Function
- LOG Excel Function
- EXPONENTIAL Excel Function
- SUMIF in Excel
- TAN Excel Function
- CEILING Excel Function
- LN Excel Function
- SIGN Excel Function
- COS Excel Function
- FLOOR Function in Excel
- SIN Excel Function
- ROUNDDOWN Excel Function
- ROUNDUP Function in Excel
- COMBIN Excel Function
- INT Excel Function (Integer)
- Date and Time Function in Excel
- Statistical Function in Excel
- AVERAGE Excel Function
- CORREL Excel Function
- COUNT Excel Function
- COUNTIF Excel Function
- FREQUENCY Excel Function
- MAX Excel Function
- MEDIAN Excel Function
- GROWTH Excel Function
- SLOPE Function in Excel
- TREND Function in Excel
- SMALL Function in Excel
- MODE Excel Function
- LARGE Excel Function
- PERCENTILE Excel Function
- LINEST Excel Function
- T-TEST in Excel
- QUARTILE Excel Function
- Information Functions in Excel
- Excel Charts
- Excel Tools
- Excel Tips
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).
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.
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:
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:
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:
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 1st 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:
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
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