LOOKUP Function

LOOKUP Function in Excel

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.

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

Vector Form of Lookup Function

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_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_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

Read more on similar functions

VLOOKUP Function

UNIQUE function

SORT function

SEQUENCE function

SORTBY function

RANDARRAY function

Leave a Reply

Your email address will not be published.Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.