What is the Lookup Function?
The LOOKUP function is similar to other Excel lookup functions such as VLOOKUP and HLOOKUP in that it can be used to return a value in excel from a data table.
How it differs is that while VLOOKUP only returns data form columns of data and HLOOKUP only from rows, the lookup function can return data from a vector — a single row or column of data or from an array a block of data containing multiple rows or columns.
Lookup function has two forms: the vector form and the Array form.
The vector form searches for a specified value in a single row or column and then returns a value from the same position in a second row or column. The array looks for the specified value in the first row or column of a data block and then returns a value from the same position in the last row or column of the block.
Types of LOOKUP Function.
- Lookup value (required): a value the function searches for in the first vector. Lookup value can be a text, a logical value, or a name or cell reference that refers to a value.
- Lookup vector (required): a range containing only one row or column that the function searches to find the Lookup value. The data may be text. Numbers, or values of logic.
- Result_vector (optional): a range that contains only one row or column. Ille result_vector argument must be the same size as lookup_vector.
In the table below, we have a database with product name and price in two different locations[ A:1B7and D1: J2 range]. Now we need to find the price according to the product name by using a lookup function. You need to sort the database according to the ascending order to get the exact result before performing the lookup function.
For the LOOKUP function to work correctly, the Lookup_vector must be sorted in ascending order (A to Z or smallest to largest for numbers).
- If the Result vector argument is omitted, the function returns the Lookup value argument if it is present in the Lookup vector.
- If the function can not find an exact match for the Lookup value, it chooses the largest value in the Lookup vector that is lower than or equal to the Lookup value.
- If the Lookup value is lower than all values in the Lookup vector, the LOOKUP function returns the Lookup vector value.
LOOKUP Function and Array Shape.
The array shape – whether it is square (equal number of columns and rows) or a rectangle (UN equal number of columns and rows) – affects where the LOOKUP function searches for data:
- LOOKUP assumes that the data is arranged in columns if an array is square in shape or if it is a tall rectangle (larger than it is wide), and therefore searches for a match to the Lookup value in the first column of the array.
- If an array is a wide rectangle (larger than it is tall), LOOKUP assumes that the data is arranged in rows and therefore searches in the first row of the array for a match to the Lookup value.
The syntax for the Array Form of the LOOKUP function is:
We have two databases in the table below. First, one with student name, score & grade[ we need to lookup] and the second one with score range and grade. Now we need to use the lookup function to LOOKUP the grade according to the score range.
Here we also need to sort the scoring column from where we generate the result.