How to use Excel LOOKUP function

Introduction

LOOKUP function in excel is a lookup and reference function. This function is used to lookup or approximately match data in single column or single row and retrieve value from corresponding column or row. This function has two forms, one is called vector and the other one is called array form.

Vector form Syntax

 =LOOKUP(Lookup_Value, Lookup_Vector, [Result_Vector])

Vector form Argument

  • Lookup_Value: Value that will be searched in the first vector. Can be text, number, reference etc.
  • Lookup_Vector: It is the range which has one row or column containing the value.
  • Result_Vector: It is the range which has one row or column containing the result.

Array form Syntax

=LOOKUP(Lookup_Value, Array)

Array form Argument

  • Lookup_Value: Value that will be searched in the array. Can be text, number, reference etc.
  • Array:  It is range of cells which has the value that will be compared to the lookup value.

Keynotes

  • Lookup_vector values should be in ascending order else there is a chance of getting an error.
  •  If in case that LOOKUP function is unable to find the lookup_value, then it looks for the biggest value in lookup_vector/array which is smaller than or equal to lookup_value.
  • If even the smallest value in lookup_vector/array is bigger than lookup_value, #N/A error is displayed.
  • Result_vector should have the same size as lookup_vector.
  • LOOKUP does not distinguish between uppercase and lowercase letters.

Examples

In this example, three rows having Order ID, unit price, Quantity respectively are shown. The value corresponding to Order ID ‘4’ is looked up using the formula =LOOKUP(4,A3:G3,A4:G4) and gives value 1680.

Leave a Reply

Close Menu