Table of Contents

**About**

The Excel **LOOKUP **function is a **LOOKUP & REFERENCE** function that is used to search a value in a single row/column and return the corresponding value from the second row/column.

**Function Type**

Lookup and reference

**Vector Form of Lookup Function**

LOOKUP function has two forms, **Vector** and **Array**. We will see the Vector form first.

Use the vector form of LOOKUP function when you want to specify the range that contains the values that you want to match.

**Purpose**

Look for a value in a row/column and return the corresponding value from the second row/column.

**Return value**

A value in the result vector.

**Syntax**

=LOOKUP(lookup_value, lookup_vector, [result_vector])

**Arguments**

**lookup_value** – The value LOOKUP searches for in the first vector. This can be a number, text, a logical value, or a name or reference that refers to a value

**lookup_vector** – that one-row, or one-column range to search

**result_vector** – that one-row, or one-column range containing results. this should be of the same size that of lookup_vector

**Examples**

*Grade from the Score of a Candidate*

In this example, we will see how to use the LOOKUP function to return the Grade after analyzing the score of a candidate.

The following formula will return the Grade of the score **80** in the cell **F4** after comparing it with the Grade and Score Matrix in the range **B3:C7**.

=LOOKUP(F4,B3:B7,C3:C7)

If the LOOKUP function can’t find an exact match, the function will return the value corresponding to the next smallest value.

In this case, if we search for the Grade of the score **65**, LOOKUP function will return the value **D**

=LOOKUP(F5,B3:B7,C3:C7)

LOOKUP function can also perform lookups in the horizontal direction.

In the following example, I have aligned the same data (Grade/Score Matrix) in columns (C3:G4) and used **LOOKUP **function to find the Grade corresponding to the candidate score in the cells **C7** and **C8**

=LOOKUP(C8,C3:G3,C4:G4)

### Array Form of Lookup Function

The array form of LOOKUP function looks in the **first row or column** of an array for the specified value and returns a value from the **same position in the last row or column** of the array. This form of **LOOKUP** function is used when the value that needs to be matched is in the first row or column of the array.

**Purpose**

Look for a value in a row/column and return the corresponding value from the second row/column.

**Return value**

A value from the last column or row of the array.

**Syntax**

=LOOKUP(lookup_value, array)

**Arguments**

**lookup_value** – The value LOOKUP searches for in the first vector. This can be a number, text, a logical value, or a name or reference that refers to a value

**array **– A range of cells that contains text, numbers, or logical values that you want to compare with lookup_value

### Example

#### Perform a lookup for the Country name using Country code

In this example, LOOKUP function is used to return the country Name from a table containing Country Code (**B3:B7**), ISO Code (**C3:C7**) and Country Name (**D3:D7**)

The following formula will look for the Country Code **81 **(lookup value** **in the cell **G7**) in the range of cells from **B3:D7 **(array) and will return the corresponding country name, **Japan**.

=LOOKUP(G5,B3:D7)

### Notes

LOOKUP function assumes that lookup_vector is sorted in ascending order

result_vector must be the** same size** as lookup_vector

When lookup-value can’t be found, the LOOKUP function will match the next smallest value

When lookup_value is greater than the greatest value in lookup_vector, LOOKUP matches the last value

When lookup_value is less than the smallest value in lookup_vector, LOOKUP returns **#N/A error**

LOOKUP function is **not case-sensitive**

Excel Functions in Alphabetical Order (Complete list)

Complete List of Excel Functions (Category wise)