LOOKUP Function in Excel and how to use?

Author: Sarthak Bhalerao

Table of Contents

What is the LOOKUP function?
The formula of the function
How to use the LOOKUP function in Excel?

What is the LOOKUP function?

The lookup function is categorised under Microsoft Excel’s lookup and reference functions. This function returns a value from a range (one row or one column) or from an array. It can be used as a worksheet function (WS) in Microsoft Excel. The LOOKUP function can be entered as a part of a formula in a cell of a worksheet. Comparing two columns or rows in financial analysis can be done very easily with the use of the LOOKUP function. This function is designed to handle the simplest cases of vertical and horizontal lookup. The more advanced versions of the LOOKUP function are HLOOKUP and VLOOKUP.

Formula

There are two forms of the LOOKUP function: vector and array.

Vector Method:

The vector form of the LOOKUP function will search one row or one column of data for a specified value and then get the data from the same position in another row or column.

Formula =LOOKUP(lookup_value, lookup_vector, [result_vector])

Parameters

1. Lookup_value – This is the value that we will be searching for. It can be a logical value of TRUE or FALSE, a reference to a cell, number, or text.
2. Lookup_range – A single row or single column of data that is sorted in ascending order. The LOOKUP function searches for a value in this range.
3. Result_range – It is a single row or single column of data that is the same size as the lookup_range. The LOOKUP function searches for the value in the lookup_rangeand returns the value from the same position in the result_range. If this parameter is omitted, it will return the first column of data. This is an optional parameter

Returns

The LOOKUP function returns any data type such as a string, numeric, date, etc. If the LOOKUP function cannot find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value. If the value is smaller than all of the values in the lookup_range, then the LOOKUP function will return #N/A. If the values in the lookup_range are not sorted in ascending order, the LOOKUP function will return the incorrect value.

Applies to

Excel for Office 365, Excel 2019, Excel 2016, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2007, Excel 2003, Excel XP, Excel 2000

2. Array Method:

Formula =LOOKUP(lookup_value, array)

Parameters

1. Lookup_value – This is the value that we will be searching for. It can be a logical value of TRUE or FALSE, a reference to a cell, number, or text.
2. Array – A range of cells that contains text, numbers, or logical values that we want to compare with the lookup_value.

How to use the LOOKUP function in Excel?

Example 1:

Assume we are given a list of products, colour, order_id, and quantity. We want a dashboard where we put the product and then we instantly get the quantity.