What is the Lookup Function?
The LOOKUP function is similar to other Excel lookup function such as VLOOKUP and HLOOKUP in that it can be used to return a value from a table of data in excel.
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 will search a single row or column for a specified value and then return a value from the same position in a second row or column.
The Array from looks in the first row or column of a block of data for the specified value, 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 that the function searches for in the first vector. The 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 can be text. numbers, or logical values.
- 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 spreadsheet below, we have a database having product name and price in two different locations [A:1B7
and D1: J2 range]. Now we need to find the price according to the product name by using lookup function.
Before performing lookup function you have to sort the database as per ascending order to get the accurate result.
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 cannot find an exact match for the Lookup_value, it chooses the largest value in the Lookup _ vector that is less than or equal in value to the Lookup_value.
- If the Lookup_value is smaller than all values in the Lookup_vector, the LOOKUP function will return an
#N/ A error.
LOOKUP Function and Array Shape.
The shape of the array – whether it is square (equal number of columns and rows) or a rectangle (unequal
a number of columns and rows) – affects where the LOOKUP function searches for data:
- If an array is square in shape or if it is a tall rectangle (taller than it is wide), LOOKUP assumes that the
data is arranged in columns and therefore searches for a match to the Lookup_value in the first column Of
- If an array is a wide rectangle (wider than it is tall), LOOKUP assumes that the data is arranged in rows
and therefore searches for a match to the Lookup_value in the first row Of the array.
The syntax for the Array Form of the LOOKUP function is:
In the spreadsheet below, we have two databases. First, one having student name, score & grade [we need to lookup] and the second one having score range and grade. Now we need to LOOKUP the grade according to the
score range using lookup function.
Here also we need to sort the scoring column from where we generate the result.